Help with closing/save as excel workbook as a different filepath

[JasonFruh]

Hi everyone,

I just recently learned of autoit as of a week or so ago and have started to try to use to it help automate some functions at my work. One of the things I'm trying to automate is opening a excel workbook of one file type, doing some various actions in the workbook, closing it and then saving as a new file type. I have followed the help guide and other forum posts extensively and I can't seem to figure out what I'm doing wrong. The sample for _ExcelBookSaveAs uses html so I can't tell if the extension for my .xlsx workbook is set correctly. My script is below;

#include <excel.au3>

; Specify file name and file paths for specific use case ;
Local $FilePath = 'C:\Totals\Totals.csv'
Local $FileName = 'Totals'
Local $WindowInfo = 'Totals.csv - Excel'
Local $SourceFolder = 'C:\Totals'
Local $oExcel = _Excel_Open()
Local $Extension = '.xlsx'
Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath)
Local $ArchiveFolder = 'C:\Totals\Totals Archive Test'
Local $sWorkbook = $ArchiveFolder & '\' & $FileName & $Extension

; Opening Test File and making it fullscreen ;
_Excel_BookOpen($oExcel, $FilePath, False)
WinWaitActive($WindowInfo)
WinSetState($WindowInfo, '', @SW_MAXIMIZE)

; Sending some text to test ;
_Excel_RangeWrite($oWorkbook, Default, "Test", "A5")

Sleep(3000)

; Closing workbook and saving changes ;
WinActivate($WindowInfo)
_Excel_BookClose($oWorkbook, True)

; Saving as new file type ;
WinWaitClose($WindowInfo)
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $Extension, True)

Sleep(2000)
FileMove($sWorkbook, $ArchiveFolder & '\' & $FileName & " " & @MON & "-" & @MDAY & "-" & @YEAR & $Extension)

 

I also am having trouble closing almost any file or program I use including in this sample script with WinClose, usually I just close the file manually and it continues the rest of the script but I can't figure out why the my close command never works or why it's not saving as the new file type. In this example it also doesn't seem to be sending the sample text to the specified cell. This isn't a major issue as all my other scripts I use keyboard hotkeys which seem to have no trouble working but I still am confused as to if I'm generally using the _Excel commands wrong. I know that's a lot so I apologize. If anyone can at least help me figure out why it's not converting the file type and saving that would be great!

Thanks!

test script.au3

[water]

#include <excel.au3>

; Specify file name and file paths for specific use case ;
Local $FilePath = 'C:\Totals\Totals.csv'
Local $FileName = 'Totals'
Local $WindowInfo = 'Totals.csv - Excel'
Local $SourceFolder = 'C:\Totals'
Local $oExcel = _Excel_Open()
Local $Extension = '.xlsx'
Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath)
Local $ArchiveFolder = 'C:\Totals\Totals Archive Test'
Local $sWorkbook = $ArchiveFolder & '\' & $FileName & $Extension

; Opening Test File and making it fullscreen ;
_Excel_BookOpen($oExcel, $FilePath, False)

; Sending some text to test ;
_Excel_RangeWrite($oWorkbook, Default, "Test", "A5")

Sleep(3000)

; Closing workbook and saving changes ;
_Excel_BookClose($oWorkbook, True)

; Saving as new file type ;
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $Extension, True)

FileMove($sWorkbook, $ArchiveFolder & '\' & $FileName & " " & @MON & "-" & @MDAY & "-" & @YEAR & $Extension)

; Close Excel and all workbooks without saving changes ;
_Excel_Clos($oExcel, False)

I have removed all unneeded window automation commands. The Excel UDF automates via COM, hence no GUI automation is needed.

Parameter 3 of _Excel_BookSaveAs needs an numeric constant, not the ".xlsx" extension.
In your script move the cursor to "#include <Excel.au3>" then press Alt+i. ExcelConstants.au3 gets opened. Move the cursor to "#include ExcelConstants.au3" and pres Al$xlWorkbookDefault+i.
Now search for "XlFileFormat" and you will get a list of valid file formats. For ".xlsx" I suggest $xlWorkbookDefault if you run > Excel 2007

[zeenmakr]

i dont have excel or know anything about it but i think save as new file type might have something to do with $iFormat. regardless here is my thought. see *** for comments

expandcollapse popup

#include <excel.au3>

; WIN TITLE
Local $WindowInfo = 'Totals.csv - Excel'

; WIN CLASS
Global $_CLASS_SAVE_AS_DIAGLOG = '[CLASS:#32770; TITLE:Save As]'
Global $_CLASS_EXCEL = '[CLASS:XLMAIN; TITLE:' &$WindowInfo& ']'

; XLSX Dir
Local $ArchiveFolder = 'C:\Totals\Totals Archive Test'
;~ Local $FileName = 'Totals'
Local $FileName = 'Totals'
Local $FileNameExtended = " " & @MON & "-" & @MDAY & "-" & @YEAR
;~ Local $Extension = '.xlsx'
Local $currentExtention = '.csv'      ; working extension

; *** Save As NEW File Type Extention
; *** see this table here https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat
;~ Local $newExtension = $xlOpenXMLWorkbook    ; '.xlsx'   
; or test out which works
Local $newExtension = 51                    ; '.xlsx'


;~ Local $sWorkbook = $ArchiveFolder & '\' & $FileName & $Extension
Local $sWorkbook = $ArchiveFolder & '\' & $FileName

; CSV Dir
Local $FilePath = 'C:\Totals\Totals.csv'
Local $SourceFolder = 'C:\Totals'


; initiate  excel
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; it's a good practive to have @error check before procedding
Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)


; ***why are you opning this file $FilePath again bellow which already opened above***
; Opening Test File and making it fullscreen ;
;~ _Excel_BookOpen($oExcel, $FilePath, False)


; *** it best to use CLASS app specific with TITLE otherwise autoit would look for any window which match TITLE name***
; get the window handle of this specific app and specific window (you can have multiple windows of the same app)
Local $hWnd = WinWaitActive($_CLASS_EXCEL)
WinSetState($hWnd, '', @SW_MAXIMIZE)

; Sending some text to test ;
_Excel_RangeWrite($oWorkbook, Default, "Test", "A5")


; ***because excel $oWorkbook is an object i dont' know if it is neccesary to use WinActive($hWnd) here but it won't hurt
; Closing workbook and saving changes ;
;~ Sleep(3000)
;~ WinActivate($WindowInfo)
WinActivate($hWnd)
;~ Sleep(3000)
If WinActive($hWnd) Then 
    _Excel_BookClose($oWorkbook, True)
EndIf



; ***WARNING WARNING WARNING
; ***if you Closed excel book with _Excel_BookClose($oWorkbook, True) above does the windows still exist to Create a New File Type?
; Saving as new file type ;
;~ WinWaitClose($WindowInfo)
WinWaitClose($hWnd)
;~ _Excel_BookSaveAs($oWorkbook, $sWorkbook, $Extension, True)
_Excel_BookSaveAs($oWorkbook, $sWorkbook & $currentExtention, $newExtension, True)

; *** OPTIONAL
; ***i think if you save as a new file name here then you don't have to use FileMove() bellow
;~ _Excel_BookSaveAs($oWorkbook, $sWorkbook & $FileNameExtended & $currentExtention, $newExtension, True)
;~ MsgBox(0, 'DONE', 'FINISHED')
;~ Exit



; ***check if new file type was successfully created before it could be able to move
Local $iCount =0
Local $iTimeOut = 10 ;in seconds
While 1
  If FileExists($sWorkbook & $newExtension) Then
;~     FileMove($sWorkbook, $ArchiveFolder & '\' & $FileName & " " & @MON & "-" & @MDAY & "-" & @YEAR & $Extension)
    FileMove($sWorkbook & $newExtension, $sWorkbook & $FileNameExtended & $newExtension)
    ExitLoop
  EndIf
  Sleep(1000)
  $iCount +=1
  If $iCount > $iTimeOut Then ExitLoop ;
WEnd

MsgBox(0, 'DONE', 'FINISHED')

[JasonFruh]

Thank you guys for the responses. I definitely had the wrong file extension which was causing issues. I have updated it with the correct version (default for this use case) and removed all the win lines as they don't end up being needed. For whatever reason the changes to the workbook still don't work including the _ExcelRangeWrite and I can't seem to get why. It also still isn't saving the file at all let alone with the new file type. I will include the updated script below:

#include <excel.au3>

; Specify file name and file path for specific use case ;
Local $FilePath = 'C:\Totals\Totals.csv'
Local $FileName = 'Totals'
Local $SourceFolder = 'C:\Totals'
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath)
Local $ArchiveFolder = 'C:\Totals\Totals Archive Test'
Local $sWorkbook = $ArchiveFolder & '\' & $FileName & '.xlsx'

; Opening Test File and making it fullscreen ;
_Excel_BookOpen($oExcel, $FilePath, False)

; Sending some text to test ;
_Excel_RangeWrite($oWorkbook, Default, "Test", "A5")

; Closing workbook and saving changes ;
_Excel_BookClose($oWorkbook, True)

; Saving as new file type ;
_Excel_BookSaveAs($oWorkbook, $sWorkbook, 51, True)

FileMove($sWorkbook, $ArchiveFolder & '\' & $FileName & " " & @MON & "-" & @MDAY & "-" & @YEAR & '.xlsx')

; Close Excel and all workbooks with saving changes ;
_Excel_Close($oExcel, True)

 

Any ideas as to why the excel isn't writing the sample text or saving? I could just automate this with mouse clicks but the general idea of this is to make a script that can be passed off to someone else if need be and I want everything to be set up with as little manual input as possible. Is there maybe another set of commands I should use instead or is this my best option and I'm simply doing something wrong?

Thanks!

[water]

You need to add some error handling to your script.
You opened the workbook two times.
_Excel_BookClose closes the workbook before you can save it with a new filetype (I made a comment out of this statement.

#include <excel.au3>

; Specify file name and file path for specific use case ;
Local $FilePath = 'C:\Totals\Totals.csv'
Local $FileName = 'Totals'
Local $SourceFolder = 'C:\Totals'
; Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath)
Local $ArchiveFolder = 'C:\Totals\Totals Archive Test'
Local $sWorkbook = $ArchiveFolder & '\' & $FileName & '.xlsx'

; Starting Excel 
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(0, "Error", "_Excel_Open error. @error = " & @error & ", @extended = " & @extended)

; Opening Test File and making it fullscreen ;
_Excel_BookOpen($oExcel, $FilePath, False)
If @error Then Exit MsgBox(0, "Error", "_Excel_BookOpen error. @error = " & @error & ", @extended = " & @extended)

; Sending some text to test ;
_Excel_RangeWrite($oWorkbook, Default, "Test", "A5")
If @error Then Exit MsgBox(0, "Error", "_Excel_RangeWrite error. @error = " & @error & ", @extended = " & @extended)

; Closing workbook and saving changes ;
; _Excel_BookClose($oWorkbook, True)

; Saving as new file type ;
_Excel_BookSaveAs($oWorkbook, $sWorkbook, 51, True)
If @error Then Exit MsgBox(0, "Error", "_Excel_BookSaveAs error. @error = " & @error & ", @extended = " & @extended)

FileMove($sWorkbook, $ArchiveFolder & '\' & $FileName & " " & @MON & "-" & @MDAY & "-" & @YEAR & '.xlsx')

; Close Excel and all workbooks with saving changes ;
_Excel_Close($oExcel, True) 
If @error Then Exit MsgBox(0, "Error", "_Excel_Close error. @error = " & @error & ", @extended = " & @extended)

 

[JasonFruh]

Thank you for the response, that worked and seemed to do the trick great! I am curious as when I close the original csv file it doesn't save the changes to the file, ie "Test" in cell A5 isn't there however it is saved in my new file, the .xlsx (good for what I need). Any thoughts as to why that is? I assume it's the order in which I'm doing my operations as in telling it to save the new workbook after making the changes and not closing and saving the .csv before the change.

Also while I'm still on the subject I was wondering if there was an easy way to tell autoit to wait until an excel query is done running in the background to execute the rest of my script? The idea of my script is to be applied to a larger excel file with a built in query I need to refresh everyday but given that the query runs in the background of excel and none of the window properties change, there is no easy way I can find to halt the script until it's done. My thought was to just add a Sleep() command for some larger amount of time then needed to ensure that it always is done executing since it can vary a little. Does this make sense or is there some easier way? I've tried ProcessWait and some others but none work.

Thanks again for all the help! This is enough to get me through my major struggles lmao

[water]

14 minutes ago, JasonFruh said:

I close the original csv file it doesn't save the changes

Simply add a _Excel_BookSave. If this doesn't work then try _Excel_BookSaveAs with filetype CSV.

What kind of query are you talking about? Can you post an example?

[zeenmakr]

for ratability click '<>' above the reply box menu to insert autoit code

 

7 minutes ago, JasonFruh said:

when I close the original csv file it doesn't save the changes to the file, ie "Test" in cell A5

because you need to add _Excel_BookSave() above _Excel_BookSaveAs()

also you want to implement prompting userinput instead of static text. if you have new text from a exist file it could also be imported automatically for you.

; Sending some text to test ;
Local $sUserInput = InputBox('Enter Today' New Text', 'example: [A5] sample text', '[A5] Test')
Local $aArray = StringRegExp($sUserInput, '^\[([A-Z]\d{1,2})\]\s?(.*?)$', 3)
_Excel_RangeWrite($oWorkbook, Default, $aArray[1], aArray[0])

could you walk thru step by step scenario how how to want to automate and have the script wait before you done your task?

[JasonFruh]

Hi guys, essentially the query I'm running is a Microsoft Excel query that was probably developed in SQL and then added as a live connection to the workbook. I will attach an image of what the query looks like when it's running as well as a look at the connection type. Given that I'm running this as I type it's not going to allow me into the actual query editor but it's essentially just running a SQL query in the background of the workbook to refresh certain data tables. The part I'm having difficulties with is there is no pop up box for when the query is running or anything else I can think of to tell the script wait until this process is complete. The only thing that changes is the little globe icon in the bottom left corner that shows "Running background query". Any thoughts on this? As of now I just have a sleep command for way longer than I know the query takes to run in case of variance but that obviously isn't always going to work.

Thanks!

[dmob]

Try this:

While $oExcel.Ready = False 
                Sleep(10)
            WEnd

 

[zeenmakr]

3 hours ago, JasonFruh said:

The only thing that changes is the little globe icon in the bottom left corner that shows "Running background query". Any thoughts on this?

if the above suggestion by @dmob doenst work out, there is imagesearch udf or check the statusbar status if supported. run 'autoit window info' click and drag the cross-hairs 'finder tool' on to the 'globe icon' send in the screenshot of that, and another one on the text 'running background query' get another screenshot of that. also try another one on 'ready' and '(click here to cancel)' if it didn't highlighted previously

Edited September 22, 2020 by zeenmakr

[water]

To get a list of connections you could run the following script (after modifying the path) which will return the name, description and type of all connections.
We then can drill down further.

#include <Excel.au3>

Global $sWorkbook = @ScriptDir & "\Connections.xlsx" ; <=== MODIFY
Global $oExcel = _Excel_Open()
ConsoleWrite(@error & @CRLF)
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
ConsoleWrite(@error & @CRLF)
Global $oConnections = $oWorkbook.Connections
ConsoleWrite(@error & @CRLF)
Global $aConnections[100][3], $i = 0
For $oWorkbookConnection In $oConnections
    $aConnections[$i][0] = $oWorkbookConnection.Name
    $aConnections[$i][1] = $oWorkbookConnection.Description
    $aConnections[$i][2] = $oWorkbookConnection.Type
    $i = $i + 1
Next
ReDim $aConnections[$i][3]
_ArrayDisplay($aConnections)