Excel COM SaveAs with variable for name - XL.ActiveWorkbook.SaveAs("C:\" variable ".xlsx" not working

[Tigerlily]

Hi Folks,

I'm trying to use COM to have AHK save a backup copy of my excel file every 5 minutes with a timestamp in the filename. I can get everything to work just fine with A_ variables, but when I try to use my own variables to pull the current filename and/or formatted timestamp, I get this error message:

Error: 0x800A03EC -
Source: Microsoft Excel
Description: The file could not be accessed. Try one of the
following:
. Make sure the specified folder exists.
- Make sure the folderthat contains the file is not read-only.
. Make sure the file name does not contain any of the following
characters: < > ? [ ] : | or f
. Make sure the file/path name doesn't contain more than 218
characters.
HelpFile: xlmain11.chm
HelpContext: 0
Specifically: SaveAs
LinerI
011: FormatTlme,filetimed,A_now,ddd MMM-d-yy hh:mm tt
012: MsgBothe date8ttlme is ‘%filetimed%’
014: WinGetTitle,filenamed,ahk_class XLMAIN
015: MsgBothe fliename is ‘%filenamed%’
017: monkey :- filetimed
019: XI :- Com0bJActive(‘Excel.App|lcatlon’]
020: XLVislble :- True


---> 022:
XLActiveWorkbook.SaveAs['C:\Users\Tigerlily\ExcelBackups\‘ filetimed’.xlsx')


023: Sleep,300000
024:}
025: Return
027: Return
028: {
029: MsgBox,ExceiAutoSave is no longer runningl
030: ExitApp
Continuc running the script?

Here is my current code:

Code: Select all

#SingleInstance, force ;	Doesn't allow the script to run multiple instances at once.
#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. 


Loop
{
FormatTime, filetimed, A_now, ddd MMM-d-yy hh:mm tt
MsgBox, The date&time is "%filetimed%"

WinGetTitle, filenamed, ahk_class XLMAIN
MsgBox, The filename is "%filenamed%"

Xl := ComObjActive("Excel.Application") ;handle
Xl.Visible := True

XL.ActiveWorkbook.SaveAs("C:\Users\Tigerlily\ExcelBackups\" filetimed ".xlsx") ;'bookname' is a variable with the path and name of the file you desire
Sleep, 300000
}
return

End::
{
MsgBox, ExcelAutoSave is no longer running!	
ExitApp
}
return

All suggestions/tips are welcome. After I get the timestamp figured out, I would also like to figure out how to grab the current workbooks filename to add the timestamp to when saving these backups. Thank you much in advance!

[Blackholyman]

first thing i see is that you have : in the time stamp so the string in the variable has a char that can't be used in a file name

[Tigerlily]

first thing i see is that you have : in the time stamp so the string in the variable has a char that can't be used in a file name

first thing i see is that you have : in the time stamp so the string in the variable has a char that can't be used in a file name

Thanks Blackholyman (:

That was a stupid mistake, however it isn't the source of my actual problem, although I've now changed it.

I've scoured countless forums & google pages for the answer to this - do you or anyone know where I could find this info or who I should ask?

Seems like a pretty easy thing to do, but I bet I'm doing something wrong syntactically..

[eelrod]

Since you stated that you're using AHK to create a backup of your Excel file every 5 minutes, are you intending to do a saveas? If so, you will always be working in a copy (after the first saveas); the latest copy, but still a copy. Another option may be to push a regular save and then just copy the file with FileCopy.

As for the filename and path:

Code: Select all

oXL := ComObjActive("Excel.Application")
sFullPath := oXL.ActiveWorkbook.FullName
sName := oXL.ActiveWorkbook.Name
sFilePath := SubStr(sFullPath , 1 , InStr(sFullPath , "\" ,, 0))
MsgBox , %sFullPath%`n%sName%`n%sFilePath%