Excel COM - pull variable from other function variable??

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Tigerlily
Posts: 283
Joined: 04 Oct 2018, 22:31

Excel COM - pull variable from other function variable??

09 Nov 2018, 03:30

Hello Folks,

Just trying to figure out how to make it so if someone wants to save their Excel Workbook as Macro-enabled *.xlsm, then they can.. but I don't understand how to pull the chosen variable out of a selection with a function variable...?? I don't think I'm wording this right because I'm too much of a noob at this complex of wording..? Okay let me just post my code and it will make more sense:

Code: Select all

Xl := ComObjCreate("Excel.Application") ; creates Excel handle


		;				//				Prompt User to Open Last Month's SEO Report file				//
		
ReportingMonth := "AnyMonth"	
		
fileToOpen := Xl.GetOpenFilename(FileFilter := "Excel Files *.xls*, *.xls*", FilterIndex := 1, Title := "OPEN SEO MONTHLY REPORT", MultiSelect := False)
	if fileToOpen = 0 
	{
		MsgBox, 0x1010, REPORT NOT OPENED, No file was selected.`n`nSEO Monthly Report Builder will close.
		ExitApp
	}
		else If fileToOpen <> 0
		Xl.Workbooks.Open(FileName := fileToOpen, UpdateLinks := 0)


MsgBox, 0x1030, Please Wait..., You have requested to populate the %ReportingMonth% Monthly Report.`n`nPlease wait a moment for the report to complete.,5

Xl.Visible := False

SoundPlay *-1
MsgBox, 0x1030 ,Saving...,Please choose the destination folder where you would like to save your %ReportingMonth% SEO report.

fileToSave := Xl.GetSaveAsFilename(InitialFilename := "SEO Monthly Report " ReportingMonth " 2018", FileFilter := "Microsoft Excel Worksheet *.xlsx, *.xlsx, Microsoft Excel Macro-Enabled Worksheet *.xlsm, *.xlsm", FilterIndex := 1, Title := "Save " ReportingMonthCaps " REPORT as...")

Xl.ActiveWorkbook.Saveas(FileName := fileToSave, FileFormat := ??????????????????????????????????????????????)

	if fileToSave = 0 
	{
		SoundPlay *-1
		MsgBox, 0x1010, REPORT NOT SAVED, Your report was not saved.`n`nSEO Monthly Report Builder will now close.
		ExitApp
	}
		else If fileToSave <> 0
		
SoundPlay *-1
MsgBox, 0x1024, Open Monthy SEO Report? ,Would you like to open your sparkly new %ReportingMonth% Monthly SEO Report??
IfMsgBox Yes
	{
	Xl.Visible := True, 
	WinMaximize, ahk_exe EXCEL.EXE
	WinActivate, ahk_exe EXCEL.EXE
	}
else
	Xl.Workbooks.Close
So the last part, when saving. I give the user the option to select saving their file as a .xlsx and .xlsm, but my problem is that I don't know what to set the SaveAs FileFormat variable as? I'm not sure if this gets recorded in the fileToSave variable in some way, although I suspect that it does. How would I use that variable to tell the SaveAs FileFormat to equal 51 (for .xlsx) or 52 (for .xlsm) based on user input??

If I set it to the fileToSave variable, it just crashes. If I set it as a static number then the user will not get one of the options even if they choose it.. what to do?

Application.GetSaveAsFilename Method (Excel) MSDN: https://docs.microsoft.com/en-us/office ... asfilename
XlFileFormat Enumeration (Excel) MSDN: https://docs.microsoft.com/en-us/office ... fileformat


Thank you so much in advance :D :shock: I really appreciate your insight, whatever it may be.
-TL
User avatar
TLM
Posts: 1418
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

Re: Excel COM - pull variable from other function variable??

09 Nov 2018, 10:14

Tigerlily wrote:
09 Nov 2018, 03:30
Just trying to figure out how to make it so if someone wants to save their Excel Workbook as Macro-enabled....
You're really close Xl.ActiveWorkbook.Saveas(FileName := fileToSave, FileFormat := 52)
FileFormatConstants wrote:52 is for an Open XML Workbook Macro Enabled ( xlOpenXMLWorkbookMacroEnabled )
53 is for an Open XML Template Macro Enabled


...Also, sweet GetOpenFilename() method :thumbup:
for direct help, tweet at me: https://twitter.com/CW_DTech
FanaticGuru
Posts: 1355
Joined: 30 Sep 2013, 22:25

Re: Excel COM - pull variable from other function variable??

09 Nov 2018, 16:30

Tigerlily wrote:
09 Nov 2018, 03:30
So the last part, when saving. I give the user the option to select saving their file as a .xlsx and .xlsm, but my problem is that I don't know what to set the SaveAs FileFormat variable as? I'm not sure if this gets recorded in the fileToSave variable in some way, although I suspect that it does. How would I use that variable to tell the SaveAs FileFormat to equal 51 (for .xlsx) or 52 (for .xlsm) based on user input??

You need to do a string check to get the format to use.

Code: Select all

xlApp := ComObjActive("Excel.Application")
 
FileToSave := xlApp.GetSaveAsFilename(InitialFilename := "SEO Monthly Report " ReportingMonth " 2018", FileFilter := "Microsoft Excel Worksheet *.xlsx, *.xlsx, Microsoft Excel Macro-Enabled Worksheet *.xlsm, *.xlsm", FilterIndex := 1, Title := "Save " ReportingMonthCaps " REPORT as...")
 
FileFormat:=51 ; default to xlsx
if (SubStr(FileToSave, -3) = "xlsm")
	FileFormat := 52
 
xlApp.ActiveWorkbook.Saveas(FileToSave, FileFormat)

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

Return to “Ask For Help”

Who is online

Users browsing this forum: casperharkin, Google [Bot], Odlanir, SOTE, teadrinker and 155 guests