CODEWITHSUNDEEP
excelcomautohotkey
Aaron Parisi
Aaron Parisi

Reputation: 706

Why can't I save 2 excel files with AHK script?

I want to grab info from my company's database, click the "export to excel" button (all of which works fine), and save the exports into a specific folder.

The first one works fine, saves, and then quits.

The second one does NOT. The second file TEST1 does not get deleted even though I get to the MsgBox following the delete command.

Then a gray Excel window pops up with no worksheets in it, then it disappears a second later, and the second exported sheet is still just sitting there.

I don't know what it thinks X2 is referring to if not the only open excel workbook. Do I need to somehow "really" quit X1 or something? It just seems to fail if I try to do TWO saveAs's.

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

+^g::

;; generate an excel spreadsheet from our DB

Sleep 5000

X1 := ComObjActive("Excel.Application")
X1.Visible := True

If FileExist("M:\Current Users\aparisi\MaryFiles\TEST.xlsx") {

    MsgBox, "We found TEST"
    FileDelete, "M:\Current Users\aparisi\MaryFiles\TEST.xlsx"  

}

X1.ActiveWorkbook.SaveAs("M:\Current Users\aparisi\MaryFiles\TEST.xlsx")
X1.Quit()

;; props

;; generate a 2nd excel spreadsheet from our DB

Sleep 5000

X2 := ComObjActive("Excel.Application")
X2.Visible := True

If FileExist("M:\Current Users\aparisi\MaryFiles\TEST1.xlsx") {

    MsgBox, "We found TEST1"
    FileDelete, "M:\Current Users\aparisi\MaryFiles\TEST1.xlsx"
    MsgBox, "We apparently just deleted TEST1"

}

X2.ActiveWorkbook.SaveAs("M:\Current Users\aparisi\MaryFiles\TEST1.xlsx")
X2.Quit()

WinActivate Act! Premium Plus - TenThirtyOneServices
WinWaitActive Act! Premium Plus - TenThirtyOneServices

CoordMode, Mouse, Screen

MouseMove, 1172, 312
Click
Click
Sleep 1000
MouseMove, 1154, 688
Click
Sleep 1000

Return

Upvotes: 0

Views: 175

Answers (1)

Relax
Relax

Reputation: 10543

Try something like this

+^g::

;; generate an excel spreadsheet from our DB

Sleep 5000

X1 := ComObjCreate("Excel.Application")  ; create a new instance of Excel
X1.Visible := true                       ; make Excel visible
X1 := ComObjActive("Excel.Application")  ; make Excel active
X1.Workbooks.Add()                       ; create a new blank workbook  in the active instance

If FileExist("M:\Current Users\aparisi\MaryFiles\TEST.xlsx") 
{
    MsgBox, "We found TEST"
    FileRecycle, M:\Current Users\aparisi\MaryFiles\TEST.xlsx
    Sleep 500
    If !FileExist("M:\Current Users\aparisi\MaryFiles\TEST.xlsx")
        MsgBox, "We just deleted TEST.xlsx"
    else
        MsgBox, "We could NOT delete TEST"
}

X1.ActiveWorkbook.SaveAs("M:\Current Users\aparisi\MaryFiles\TEST.xlsx")
X1.Quit()
X1 := ""                     ; clear the variable


;; props

;; generate a 2nd excel spreadsheet from our DB

Sleep 5000

X2 := ComObjCreate("Excel.Application")  ; create a new instance of Excel
X2.Visible := true                       ; make Excel visible
X2.Workbooks.Add()                       ; create a new blank workbook in the active instance of Excel

X2 := ComObjActive("Excel.Application")
X2.Visible := True

If FileExist("M:\Current Users\aparisi\MaryFiles\TEST1.xlsx") 
{
    MsgBox, "We found TEST1"
    FileRecycle, M:\Current Users\aparisi\MaryFiles\TEST1.xlsx
    Sleep 500
    If !FileExist("M:\Current Users\aparisi\MaryFiles\TEST1.xlsx")
        MsgBox, "We just deleted TEST1.xlsx"
    else
        MsgBox, "We could NOT delete TEST1.xlsx"
}
X2.ActiveWorkbook.SaveAs("M:\Current Users\aparisi\MaryFiles\TEST1.xlsx")
X2.ActiveWorkbook.Close
X2.Quit()
X2 := ""  

; .....
Return

Upvotes: 1

Related Questions