Copy Excel data and paste to DOS application

[blass9]

Hi all,

New to AutoIT and the forums, and loving what I'm seeing so far!

I'm a pretty novice programmer, but am learning more all the time. The following code is my attempt to copy Excel data in specific cells and pass them to a DOS application. I have looked at several scripts and examples in the forums, and have it very close to working, but I don't get the result I want from the clipboard to paste in the DOS app (which is Perl-based).

#include <Excel.au3>

Run(@ComSpec & " /k cd c:\Users\exam\Desktop\appfolder\", "c:\", @SW_SHOW) ;Opens DOS to the folder where the app is
WinActivate("Administrator: C:\Windows\system32\cmd.exe")
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe")
   Send("Perlprogram.pl" & "{ENTER}") ;Opens the perl-based app in the Command line window

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\Users\exam\Desktop\mytestfile.xlsm") ;opens the Excel file I want
$oExcel.Application.ActiveSheet.Range("C13:D13").Select ;selects the first set of data I want
$oExcel.Application.Selection.Copy  ;copies the first set of data to the clipboard
WinActivate("Administrator: C:\Windows\system32\cmd.exe - Perlprogram.pl") ;ponits focus back to DOS
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe - Perlprogram.pl") ;activates DOS window for entry
   Send("{PASTE}") ;(is supposed to) Paste the data from the clipboard

What I'm expecting here is a line of text to be pasted, but all that pastes is the capital letter "P." When I stop the script after the Excel copy code and paste the clipboard contents into another Excel cell or into a Word file, it gives me the line of text I want, so I know the copy to clipboard function is working properly.

I tried turning the part of the code that selects and copies the Excel data into a variable, and then putting the variable into the Send command (i.e. Send($var)), but all I got for a result was the word "True."

The clipboard has the data I want--any ideas why that isn't transferring to the DOS command line?

Thanks in advance!

 

[Shane0000]

Try 

 

Send(ClipGet())

This should type out what is in the clipboard

Edited June 22, 2015 by Shane0000

[blass9]

Shane0000,

Thanks for your response, but what it did was put "^v" into the command line. Maybe DOS needs something different to extract or parse the correct data from the clipboard?

[Shane0000]

sorry edited my OP to a tested method

#include <Excel.au3>

Run(@ComSpec & " /k cd c:\Users\exam\Desktop\appfolder\", "c:\", @SW_SHOW) ;Opens DOS to the folder where the app is
WinActivate("Administrator: C:\Windows\system32\cmd.exe")
ClipPut("Test text")

WinActivate("Administrator: C:\Windows\system32\cmd.exe") ;ponits focus back to DOS
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe") ;activates DOS window for entry
   Send(ClipGet())

 

[Shane0000]

Also I dont think {Paste} is a valid keyword,

 

Here is some information on passing control characters and such:

https://www.autoitscript.com/autoit3/docs/appendix/SendKeys.htm

 

[232showtime]

why don't you use excel func _Excel_RangeRead or _Excel_CopyPaste then pass the value to cmd.

 

[water]

As you start the DOS application from your Autoit script you could set parameter opt_flag to $STDIN_CHILD and then pass the Excel dta using function StdInWrite.

[blass9]

So, I used the ClipGet function, and it worked fine for me. Thanks Shane0000!

Thanks for your input also, 232showtime and water! I started working on the excel functions _Excel_RangeRead and _Excel_CopyPaste, but I couldn't get them to work and time was running out to get this project done, so I needed the simplest solution possible. Same reason why I didn't try $STDIN_CHILD and StdInWrite. I have several projects I can use those functions for in the future, I'm sure.

So now I have a new problem. I need to know how to take the specific Excel file name in the code above and make it a generic variable, because my script has to adapt to any given Excel file. The "any given" Excel file will be the only one on the desktop at the time I use the script. Can anyone help me with that?

Thanks in advance!

[water]

Use _FileListToArray to grab a list of xlsx files in @DesktopDir.

[blass9]

water,

That worked! Took me a few tries to get the parameters right, but I got it.

Thank you!

[water]

[blass9]

Hi all,

So I was getting the code to work, but now I have other issues, and can't figure out how to perform them.

1. I need to adjust the script so that the Excel file is already open. I think that means that I don't need to the _ExcelOpen function or the _Excel_BookOpen function, is that correct?

2. I need the script to focus on the Excel file that's already open to start copying data and moving to it to the DOS application. So if I don't need the other other functions mentioned above, what function would I need? Would I still need to use the _FileListToArray function? If so, how would I use it? If not, what would I need? Maybe _Excel_BookAttach? I'm just stuck on knowing what functions and their parameters to use.

Here is the code I've been trying to get to work. I have some of the things I've tried, or was unsure if they were needed, commented out. I also shifted to using the Excel_RangeRead function, which works better than the other code I was using for what I need.

Again, thanks in advance!

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Run(@ComSpec & " /k cd c:\Users\exam\Desktop\TLC Suite\", "c:\", @SW_SHOW) ;Opens DOS to the folder where the app is
WinActivate("Administrator: C:\Windows\system32\cmd.exe")
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe")
   Send("scale5_print.pl" & "{ENTER}") ;Opens the Scale program in DOS

; Create application object and open an example workbook
Local $aFileList = _FileListToArray(@DesktopDir, "*.xlsm")
;_ArrayDisplay($aFileList, "$aFileList")
;Local $oAppl = _Excel_Open()

Local $oWorkbook = @ScriptDir & $aFileList
Local $oWorkbook = _Excel_BookAttach($oWorkbook)

;Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & $aFileList)

Local $filename = _Excel_RangeRead($oWorkbook, Default, "C13") ;Reads filename

WinActivate("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ;ponits focus back to DOS
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ;activates DOS window for entry
   Send($filename & "{ENTER}") ;Pastes and enters filename from clipboard

 

[water]

1) _Excel_Open is still needed. But replace _Excel_BookOpen with _Excel_BookAttach.

2) If you know the name of the already open Excel workbook then _Excel_BookAttach is just fine. Else we need to look for another solution.

[blass9]

water,

I do know the name of the already open Excel "file". But as I mentioned before, the Excel file name changes with every new project. The "workbook" in each file that I need to extract data from is always going to have the same name (Sheet1), but the names of the files will change.

So I was unsure if you meant file name or workbook name. It appears to me that _Excel_BookAttach is looking for a file name, not a specific workbook name in the file.

Hope this helps clarify.

[water]

_Excel_BookAttach allows to connect to an already open Excel workbook by FileName (Name of the open workbook), FilePath (Full path to the open workbook) or Title (Title of the Excel window).

[water]

BTW: The lines you posted above won't work because _FileListToArray returns an array (as the name implies).

; Create application object and open an example workbook
Local $aFileList = _FileListToArray(@DesktopDir, "*.xlsm")
;_ArrayDisplay($aFileList, "$aFileList")
;Local $oAppl = _Excel_Open()

Local $oWorkbook = @ScriptDir & $aFileList ; This will not return the name of a single workbook
Local $oWorkbook = _Excel_BookAttach($oWorkbook) ; This will fail as you didn't provide a valid filename

You have to loop through the array to open the workbooks.

[blass9]

So, the following code opens the (any) file I want:

Local $aFileList = _FileListToArray(@DesktopDir, "*.xlsm", 1, True)
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ($aFileList) ;opens the Excel file I want

This is using an example from the _FileListToArray section of the help file. All of my Excel project files will have this in common: 1) on the desktop, and 2) only file that ends in .xlsm, and this code can open all of them.

At this point, isn't it just getting the syntax correct to declare the variable $oWorkbook from the code above, so I can start passing data back and forth using this code?:

Local $filename = _Excel_RangeRead($oWorkbook, Default, "C13") ;Reads filename

WinActivate("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ;ponits focus back to DOS
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ;activates DOS window for entry
   Send($filename & "{ENTER}") ;Pastes and enters filename from clipboard

That's what I don't get, is how to declare $oWorkbook. I'm not very good at AutoIt yet, but I don't see how lopping through the array to open the workbook is necessary.

Thanks!

[water]

That's interesting!
MSDN says that you have to pass the FileName as "String. The file name of the workbook to be opened." Looks like it accepts an array as well so you can open multiple workbooks in one go.
_Excel_BookOpen does not accept an array and returns an error.

I checked the returned value of

$oExcel.WorkBooks.Open ($aFileList)

and it seems to be undefined. That is why you are having problems with your script.

[water]

I suggest to try:

#include <Excel.au3>
Local $aFileList = _FileListToArray(@DesktopDir, "*.xlsm", 1, True)
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $aFileList[1])
Local $sFilename = _Excel_RangeRead($oWorkbook, Default, "C13") ; Reads filename
WinActivate("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ; Sets focus back to DOS
WinWaitActive("Administrator: C:\Windows\system32\cmd.exe - scale5_print.pl") ; Activates DOS window for entry
Send($sFilename & "{ENTER}") ; Pastes and enters filename

 

 

Edited June 30, 2015 by water

[blass9]

Didn't you mean:

Local $oWorkbook = _Excel_Open($aFileList[1])

Instead of:

Local $oExcel = _Excel_Open($aFileList[1])

And no, it didn't work.