SaveAs method and file format

[jkwleisemann]

I'm trying to use Macro templates to create new workbooks, and as a part of that i want to save the template under a new name without the macros enabled. Here's what I'm doing with it:

    strFileName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls), *.xls")
    
    If strFileName <> "" Then ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=51, ConflictResolution:=xlUserResolution

The code works, but the problem I'm running into is that, obviously, it doesn't care for trying to save a non-macro file format that has macro code in it, and it generates "extension doesn't match file format" errors when I re-open it.

Any suggestions on how to clear up those two issues? Preferably without having to use the extensibility add-ins to delete all the VBA code?

 

[GTO]

You are committing a SaveAs format 51/non-macro .xlsx workbook, i.e. 2007 and later format with 1+ million rows and are giving it a filename with a .xls (97-2003) extension...

Also, cancelling GetSaveAs does not result in an empty string. Something like...

<font face=Courier New>**<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> UCase$(strFileName) = "FALSE" <SPAN style="color:#00007F">Then</SPAN><br>****ThisWorkbook.SaveAs Filename:=strFileName, FileFormat:=51, ConflictResolution:=xlUserResolution<br>**<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

Hope thta helps,

Mark

 

[Joe4]

It looks like you may want FileFormat 56, not 51.
See: https://www.rondebruin.nl/win/s5/win001.htm

 

[jkwleisemann]

You are committing a SaveAs format 51/non-macro .xlsx workbook, i.e. 2007 and later format with 1+ million rows and are giving it a filename with a .xls (97-2003) extension...

Also, cancelling GetSaveAs does not result in an empty string. Something like...

**If Not UCase$(strFileName) = "FALSE" Then
****ThisWorkbook.SaveAs Filename:=strFileName, FileFormat:=51, ConflictResolution:=xlUserResolution
**End If

Hope thta helps,

Mark

That was exactly it - missing the X on the end. Fixed that though!

Any tips on how to save a macro-enabled file as a non-macro format without throwing the "we can't use this macro code if you save it like this" warning?

 

[Joe4]

Any tips on how to save a macro-enabled file as a non-macro format without throwing the "we can't use this macro code if you save it like this" warning?

You cannot. That would defeat the whole purpose of why they have the different formats.

You either need to:
- Remove all VBA code if you want to save it as an XLSX file
- Save it as a Macro-enabled option (i.e. XLSM)
- Save it to the old "XLS" format, which allows both (using the File Format number of 56 I mentioned above)

 

[jkwleisemann]

Which I'd consider, but since we have sheets that need more than the 65k rows, I'll stick with .xlsx. Thanks for the advice!

 

[Joe4]

Why exactly are you trying to "fool the system" (save as an "XLSX" but with VBA code)?
It almost seems as if you are trying to bypass security measures, which could raise some red flags.

 

[jkwleisemann]

Because I'm having trouble getting the code to delete all the VBA to work, but I'd like to save it as an XLSX format in order to shut down the macros that are embedded in it after the template is saved as a specific new file.

Basically, I have a Template for formatting purposes, and I use macros to generate the new results, but I want to shut down the macros in the new files without having to keep cursing out my coding for not getting the Extensibility tidbits to work out.

 

[Joe4]

Have a template that has NO VBA code. Then store the VBA code that you are running in a separate workbook.
So then when you save the file, there won't be any VBA code in it.

 

[jkwleisemann]

That would work. I keep forgetting that I can do that kind of thing.