Why is this file always saved as FALSE.xls ?

Question

Hello!

I wrote a VB script that creates an .xls file, based on .xlt file. Then it calls a macro from the .xls file that populates it with information from a database. In the last step the script saves the .xls file on the disk.

I did this before with VB and Excel 2003. Now I upgraded to Excel 2007 and before it saves the file, a window pops up and tells me that:

"The following features can't be saved in macro free workbooks:

• VB project

...some yada yada about what the Yes and No option do.

And the yes and no buttons in the dialog box. " I want the script to automatically select and execute Yes in the dialog box. But I can't figure how to do this. I've also posted the script I wrote.

If you have a better approach for this please share. Thank you, Steve

Sub Main()
    Dim xl_app

        Set xl_app = CreateObject("Excel.Application")
        xl_app.Workbooks.Open("E:\Work\Send Mail\Clienti.xls")


        'Run the macro

 xl_app.Run( "ImportData(""Data Source=SFA;Initial Catalog=Campofrio; 
            Integrated Security=SSPI;Connect Timeout=3000"", -1, 47)")

xl_app.ActiveWorkbook.SaveAs FileName="E:\Work\Send   
                Mail\Clients.xls",FileFormat=xlNormal

xl_app.Quit
    Set xl_app = Nothing
End Sub

Now the cod works but instead of saving the file at the specified location, it saves it in My Documents folder under FALSE.xls.

Answer 1

Do you really need to save it with the macros included? If not, use: ActiveWorkbook.SaveAs Filename:="E:\Work\Send Mail\Clients.xls", FileFormat:=xlNormal

EDIT: the key is that the extension used should match the FileFormat specified. The above works for me (to exclude macros) and the below works for me (to include macros). Neither has any popup, and both end up in the right directory (as the other poster mentioned, you have to have the := if you specify FileName; otherwise, use the form below.

ActiveWorkbook.SaveAs "c:\temp\wordmacros\mybook.xlsm", xlOpenXMLWorkbookMacroEnabled

Answer 2

Merging responses from the two duplicate questions the poster asked:

1

Preventing False.xls when saving files in Excel

2

You are using named parameters in the .SaveAs wrong. When writing out the named parameter you'll have to do it in the format

FileName:="e:\myfile.xls"

Notice the colon before the equal sign.

If you just write Filename="myfile.xls" then its a boolean comparison that will return false. And thats why it save the file as false.xls.

Really funny error I think. ;)

Answer 3

I'm not sure if this is the problem, but shouldn't you just be saving as a .xlsm instead of .xls, which lets you save a macro-enabled workbook in Office 2007? You can check which version the macro is running on and if it's Excel 2007 then save as .xlsm, .xls otherwise...