Can ReadExcel command use "active" Excel filename?

Advanced programming using WinTask scripting language

Can ReadExcel command use "active" Excel filename?

Postby blass9 on Wed Jun 03, 2015 9:26 pm

Hi all,
Here is a bit of code that retrieves data from Excel file cells and passes them to a command line of a Perl-based application.

Code: Select all
Dim filename$(100)
Dim format$(100)
Dim nofq$(100)
Dim nofk$(100)
Dim keyvalue$(100)
Dim deptn$(100)
Dim profn$(100)
Dim ctitle$(100)
Dim instname$(100)
Dim testn$(100)
Dim versn$(100)
Dim semester$(100)
Shell("cmd",1)

UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe",1)
   SendKeys("cd c:\users\exam\desktop\tlc suite<Enter>")
   SendKeys("scale5_update.pl")
   SendKeys("<Enter>")

ReadExcel("myfile.xlsm","C13:D13",filename$())
'myfile is a specific Excel file

UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe - scale5_update.pl",1)
   SendKeys(filename$(0))
   SendKeys("<Enter>")


This code passes several more cells of data from Excel to the application, and works perfectly fine.
However, this my test for one specific Excel file. What I need to do is make the code work for not just this specific Excel file, but to the "active" Excel file I have open at any given time. In other words, this same code needs to work for a myriad of different Excel files.
Is there automation code that can grab the name of the "active" Excel file and write it dynamically into the ReadExcel function for the Excel file name in all the places it needs to go?
Or is there a simpler way?
I know I can use the "UseWindow" function to get it to read the Excel cells I need, but was wondering if it could be done with ReadExcel.
Please let me know--thank you in advance!!
Love WinTask!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Thu Jun 04, 2015 8:48 am

Hello,

ReadExcel is a bit in contradiction with an active Excel sheet, ReadExcel is used when the excel file is not opened, so not active. ReadExcel used on an opened worksheet can return incorrect values if the sheet is updated by another process for example.

If you want to work on an opened sheet and active, I would use the excel interface instead of ReadExcel. The focus$ or top$ functions return the window name of the window which has the focus or which is on top. You can parse then this window name to find the excel file name and use that filename in a ReadExcel if you don't want to read directly from the opened excel sheet.

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Thu Jun 04, 2015 4:47 pm

And how would that code look to parse the file name?
Sorry--new to this kind of coding.
I tried this but it didn't work (this is part of the code from above).

Code: Select all
UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe",1)
   SendKeys("cd c:\users\exam\desktop\tlc suite<Enter>")
   SendKeys("scale5_update.pl")
   SendKeys("<Enter>")

window_name$=Top$()
ReadExcel("Top$()","C13:D13",filename$())

UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe - scale5_update.pl",1)
   SendKeys(filename$(0))
   SendKeys("<Enter>")


Thank you so much!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Sun Jun 07, 2015 12:52 pm

Hello,

The first parameter is the excel filename, it's not a window name, and the last parameter is the array receiving the values read by readexcel, so your readexcel line is not correct.

If you do msgbox(top$()) just before the readexcel line (comment it as it is not correct), what is the value displayed by msgbox(top$()) ? I will be then able to extract from it the file name if the window name contains the excel file name that you want.

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Tue Jun 09, 2015 8:09 pm

The name that pops up in the msgbox is:

CMD.EXE|ConsoleWindowClass|Administrator C:\Windows\System32\cmd.exe-scale5_update.pl

Which is the DOS command line Perl-based program.

Aren't we looking for the Excel file? How do we switch it to that file so we can extract the file name? I will ensure this will be the only Excel file open.

Thanks!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Tue Jun 09, 2015 8:49 pm

Hello,

Yes you need to put on focus first the excel workbook, for example with Excel 2013:
UseWindow("EXCEL.EXE|EXCEL7|")
and then
msgbox(focus$())

As now the excel window should be on focus with the UseWindow, focus$() should return the full window name which contains the excel workbook name.

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Wed Jun 10, 2015 3:07 pm

Ok, I get that.
Now what?
Do I use the "focus$()" code every time I need to call the Excel file in focus, and before the ReadExcel function? Or do I just need to use it once at the beginning of the script?
So I don't need the output it in a msgbox, I need to activate the excel file into focus, copy a cell's data (I assume using ReadExcel), and then pass it to the DOS Perl-based application.
How does that code look?
Thanks!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Thu Jun 11, 2015 2:57 pm

Hello,
You need the focus$ each time before the ReadExcel.
the msgbox is just for testing, it will return the full window name, and from it you will have to extract the excel filename. That's why I wanted the result of the msgbox to tell how to parse the excel filename from the full window name returned by focus$()

And then yes you can use this excel file name in ReadExcel using a variable:
fileexcel$="part of the string returned by focus$()" 'pseudo code!
and
Readexcel(fileexcel$, "A10:B10",data$())

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Thu Jun 11, 2015 4:16 pm

Sorry, still not following completely, as I'm still not sure the order of how everything should go, and how to parse.

So, the msgbox code returns:
EXCEL.EXE|EXCEL7|myfile.xlsm

Which is exactly the open Excel file I want it to read.
There are no other path parameters--just the file name.
So now, how does the rest of the code go?
Thank you so much!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Fri Jun 12, 2015 8:14 am

Hello,

You have to extract the last part of the string returned by focus$() :
a$=focus$()
b$=ExtractBetween$(a$,"|",excluded,"",included)
msgbox(b$)
fileexcel$=ExtractBetween$(b$,"|",excluded,"",included)
msgbox(fileexcel$)

The msgbox are there only for testing the result, you can delete those lines when you are happy with the script.
And fileexcel$ containing the excel filename which is in focus is the first parameter of the Readexcel line.

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Fri Jun 12, 2015 7:35 pm

Thank you!
It seems to be working except for one thing.
At the point it focuses on the Excel file and goes to the ReadExcel function, it comes up with the following error:

Error at line 27: The excel workbook C:\Program Files(x86)\WinTask\Scripts\myfile.xlsm doesn't exist

Isn't the whole purpose of this code that the Excel file to be read from could be any Excel file that is open on the desktop and not a specific one in a specific location? So now we have the focus on the generic Excel file, but it's trying to look in a specific path to find it, instead of just the one that's open on the desktop. Why is it trying to look in the folder of the WinTask scripts?
Is there more code that I need here to make it work? Is Read Excel not the right function to use?
Thanks!

Here is the code up to the first copy from the Excel file to the first paste into the DOS program (well, you can take the msgbox line out):

Code: Select all
Dim filename$(100)
Dim format$(100)
Dim nofq$(100)
Dim nofk$(100)
Dim keyvalue$(100)
Dim deptn$(100)
Dim profn$(100)
Dim ctitle$(100)
Dim instname$(100)
Dim testn$(100)
Dim versn$(100)
Dim semester$(100)
Dim paperyn$(100)
Shell("cmd",1)

UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe",1)
   SendKeys("cd c:\users\exam\desktop\tlc suite<Enter>")
   SendKeys("scale5_update.pl")
   SendKeys("<Enter>")

UseWindow("EXCEL15.EXE|EXCEL7|")
a$=focus$()
b$=ExtractBetween$(a$,"|",excluded,"",included)
fileexcel$=ExtractBetween$(b$,"|",excluded,"",included)
msgbox(fileexcel$)

ReadExcel(fileexcel$,"C13:D13",filename$())

UseWindow("CMD.EXE|ConsoleWindowClass|Administrator: C:\Windows\System32\cmd.exe - scale5_update.pl",1)
   SendKeys(filename$(0))
   SendKeys("<Enter>")
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Sat Jun 13, 2015 6:22 am

Hello,

Yes if you don't specify the path, the content of the variable fileexcel$ is just the excel filename, and so as there is no path, the excel file is supposed to be in the same folder as the script. So you need to add the path to your filename.
If the filename is in c:\test for example, the first parameter of ReadExcel will be:
ReadExcel("c:\test\"+fileexcel$,......)
At execution the first parameter will be like that c:\test\myfile.xlsm and so the excel filename will be now found as the filename includes the path.

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Mon Jun 15, 2015 3:38 pm

So, I don't want to have to specify a path.
I want the code to pass data back and forth between Excel file and DOS application, where it reads the Excel file generically (the active Excel file open on the desktop).
If I specify a path, I will need to go in and update the path in the code weekly, as the files I will use are in a different path each week. I'll have something like 15 to 20 scripts to update weekly because of parameters in my project, and I don't necessarily want to use WinTask to automate that procedure.
Is there any way to capture the data from Excel without having to declare the path?
Sorry for all the trouble.
Thank you!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm

Re: Can ReadExcel command use "active" Excel filename?

Postby TechSupport on Tue Jun 16, 2015 6:28 am

Hello,

It's computers, a computer needs to know where to find the file, so sorry, the path has to be specifed. Just a filename is not enough in Windows (you can have the same filename in different folders, that's why Windows needs the folder and the file name to find a specific file).

Regards.
WinTask Tech Support
TechSupport
 
Posts: 805
Joined: Thu Nov 16, 2006 9:58 am

Re: Can ReadExcel command use "active" Excel filename?

Postby blass9 on Tue Jun 16, 2015 5:15 pm

So, I figured out if I take the file I'm working on and move it to the desktop, then I don't need to change the path name week after week.
I'll just set one path, the desktop, for all the scripts to simplify things.
Thanks so much for all your help!
blass9
 
Posts: 8
Joined: Mon Jun 01, 2015 9:43 pm


Return to Scripts programming

Who is online

Users browsing this forum: No registered users and 4 guests

cron