Sign in to follow this  
Followers 0
stolie

blank excel data

10 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

Thankyou, ill give this a go

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Q_Engineer

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Case is important only. No other formatting is needed.

Share this post


Link to post
Share on other sites

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}")

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0