blank excel data

[stolie]

Hi

I have a script where i copy data from an excel cell and paste it into another program for a search.

I would like to be able to tell the script to stop when the excel cell is blank.

Where do i start? Make the copied text a variable and then what?

Cheers

[hgeras]

If a cell is empty then the variable that has read its contents will be $cell_contents="".So you have to put an if statement something like that:

If $cell_contents<>"" Then 

Your code here

EndIf

Be sure to have already registered your variable at the start of the program or your func like this :

Dim $Cell_contents=""

And then read from the cell....I believe this will do the trick . The var $Cell_contents is an example ....

C ya

[stolie]

Thankyou, ill give this a go

[Q_Engineer]

Wondering if you got this to work? I'm looking for something along this line but to end of column A Row ?? is blank. In other words, the data will have blanks which I will want to convert to tabs but I want the script to end at the first blank cell in colunm A. I will give my script if needed.

Edited May 24, 2006 by Q_Engineer

[exodius]

Is the file an actual .xls where you need to keep the formatting? or can you convert it to a .csv which you could read into an array and parse out?

If it's something where you have to keep the formatting I had some decent success with the code below before I got into the situation where I could do the above(which is preferable). Obviously at the end of the loop you can do your blank cell check.

WinActivate ("Microsoft Excel")

WinWaitActive ("Microsoft Excel")

For $CellNumber = 1 To 6000

ControlSend ( "Microsoft Excel", "", "", "^g" )

WinWait ("Go To")

WinActivate ("Go To")

WinWaitActive ("Go To")

ControlSetText ("Go To", "", "EDTBX1", "A" & $CellNumber )

ControlSend ("Go To", "", "", "{Enter}" )

WinWaitClose ("Go To")

WinWaitActive ("Microsoft Excel")

Next

[Q_Engineer]

Ill give it a run later this evening. I am, in a nut shell, cutting and pasting the info from each cell by row into another app. There will be a set number of colunms that will contain some that are blanks that I will have to create a send tab function, but once it reachs a blank cell A16, if there is 15 rows of data, then I would like for the script to end. Another method I was think about was to have a popup to ask for the amount of rows to append the data to. At that time I would define the maximum of rows to read...just another idea that I am sure I have seen around here in the past. Thanks for the assistance.

[exodius]

You could have it do it my way with the Go To first just down that the "A" row to see how many have actual data in them, then start at the top again and do what you've been doing since you will then know how far down to go... You didn't mention, is this text with formatting so you need it to be an .xls or can it be a .csv file?

[Q_Engineer]

Case is important only. No other formatting is needed.

[Q_Engineer]

Threw this together and it works so far. One other thing I had found is that my Excel.exe process needs to be killed also. So If tests this, kill it at the end of the script.

#include<ExcelCom.au3>

;--------------- Run the App

Run(@ComSpec & " /C START dffill.exe C:\Oldform_~1\oldform.frl", "", @SW_MAXIMIZE)

;Run(@ComSpec & " /C START winword.exe ", "", @SW_MAXIMIZE)

;-------------- End

;--------------- Prompt for Excel file

$message = "Select the Excel file to open"

Sleep(500)

WinActivate ("Select the Excel file to open")

Sleep(500)

$var = FileOpenDialog($message, "C:\Windows\", "Excel files (*.xls)", 1 + 2 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

$FilePath = StringReplace($var, "|", @CRLF)

; MsgBox(4096,"","You chose " & $FilePath)

$var=_XLread($FilePath,"Formula Sheet","A",1)

$var1=_XLread($FilePath,"Formula Sheet","B",1)

$var2=_XLread($FilePath,"Formula Sheet","C",1)

$var3=_XLread($FilePath,"Formula Sheet","D",1)

$var4=_XLread($FilePath,"Formula Sheet","E",1)

$var5=_XLread($FilePath,"Formula Sheet","F",1)

;MsgBox(4096,"","" & $var)

EndIf

Sleep(500)

WinActivate ("FormFlow Filler - [Old Form]")

Sleep(500)

WinWaitActive ("FormFlow Filler - [Old Form]")

Sleep(500)

Send("^n")

Sleep(500)

Send ($var)

Sleep(500)

Send ("{TAB}")

Sleep(500)

Send ($var1)

Sleep(500)

Send ("{TAB}")

Sleep(500)

Send ($var2)

;Sleep(500)

;Send ("{TAB}")

Sleep(500)

Send ($var3)

Sleep(500)

Send ($var4)

Sleep(500)

;Send ("{TAB}")

;Sleep(500)

Send ($var5)

Sleep(500)

Send ("{TAB}")

[Q_Engineer]

I tried and it errors on me. Any kind of help would be greatly apprecialted.

]#include<ExcelCom.au3>

Global $CellNumber

;--------------- Run the App

Run(@ComSpec & " /C START dffill.exe C:\Oldform_~1\oldform.frl", "", @SW_MAXIMIZE)

;Run(@ComSpec & " /C START winword.exe ", "", @SW_MAXIMIZE)

;-------------- End

;--------------- Prompt for Excel file

$message = "Select the Excel file to open"

Sleep(500)

WinActivate ("Select the Excel file to open")

Sleep(500)

$var = FileOpenDialog($message, "C:\Windows\", "Excel files (*.xls)", 1 + 2 )

For $CellNumber = 1 To 3

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

$FilePath = StringReplace($var, "|", @CRLF)

; MsgBox(4096,"","You chose " & $FilePath)

$var=_XLread($FilePath,"Formula Sheet","A", $CellNumber)

$var1=_XLread($FilePath,"Formula Sheet","B", $CellNumber)

$var2=_XLread($FilePath,"Formula Sheet","C", $CellNumber)

$var3=_XLread($FilePath,"Formula Sheet","D", $CellNumber)

$var4=_XLread($FilePath,"Formula Sheet","E", $CellNumber)

$var5=_XLread($FilePath,"Formula Sheet","F", $CellNumber)

;MsgBox(4096,"","" & $var)

EndIf

Sleep(500)

WinActivate ("FormFlow Filler - [Old Form]")

Sleep(500)

WinWaitActive ("FormFlow Filler - [Old Form]")

Sleep(500)

Send("^n")

Sleep(500)

Send ($var)

Sleep(500)

Send ("{TAB}")

Sleep(500)

Send ($var1)

Sleep(500)

Send ("{TAB}")

Sleep(500)

Send ($var2)

;Sleep(500)

;Send ("{TAB}")

Sleep(500)

Send ($var3)

Sleep(500)

Send ($var4)

Sleep(500)

;Send ("{TAB}")

;Sleep(500)

Send ($var5)

Sleep(500)

Send ("{TAB}")

Next