My company is behind the times and I'm forced to code everything so that all workbooks are saved in XLS format (not XLSX or XLSM). To force this I added the following code to the 'main' macro whose primary purpose is to prepare a sheet so that about 25 other macros are able to work on it. The problem is I can't get [initialfilename] to populate. If I put it in quotes it works, but if I put a variable there it's just blank in the dialog box. The variable isn't bad either... I can hover over the variable name in VBE and see that the variable is populated, but that name refuses to appear in the dialog box.
At first I had no [initialfilename] in place, just a comma, since it's SUPPOSED to auto-populate the default filename (like this: x = Application.GetSaveAsFilename(, "Excel 97-2003 Workbook (*.xls), *.xls")) But that didn't work either. I've tried saving the document first so it has a 'fresh' name attached to it, but nope. I've redefined the variables from strings to variants and everything in between. Nothing works.
The only times I can get GetSaveAsFilename to work is when I put [initialfilename] in quotes... but that's no good because the filename changes every time the file is run so I can't hardcode it like that.
Below is the relevant snippet. What am I doing wrong?
dim a1 as workbook
dim a as string
dim x as variant
Set a1 = ActiveWorkbook
a = a1.name
x = Application.GetSaveAsFilename(a, "Excel 97-2003 Workbook (*.xls), *.xls")
If x = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=x, FileFormat:=xlExcel8
At first I had no [initialfilename] in place, just a comma, since it's SUPPOSED to auto-populate the default filename (like this: x = Application.GetSaveAsFilename(, "Excel 97-2003 Workbook (*.xls), *.xls")) But that didn't work either. I've tried saving the document first so it has a 'fresh' name attached to it, but nope. I've redefined the variables from strings to variants and everything in between. Nothing works.
The only times I can get GetSaveAsFilename to work is when I put [initialfilename] in quotes... but that's no good because the filename changes every time the file is run so I can't hardcode it like that.
Below is the relevant snippet. What am I doing wrong?
dim a1 as workbook
dim a as string
dim x as variant
Set a1 = ActiveWorkbook
a = a1.name
x = Application.GetSaveAsFilename(a, "Excel 97-2003 Workbook (*.xls), *.xls")
If x = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=x, FileFormat:=xlExcel8
