Error saving XLS file as CSV in VB Script

[douglashindle]

I created a macro by recording keystrokes. There were several prompts during recording. I need to automate the answers. One question required me to agree to only save the active sheet or be prompted for a name for each sheet. Another prompt suggested I will drop formatting. I agreed to drop the formatting.

When running the macro I get:
Run-Time error '1004':
Method'SaveAs' of object'_Workbook' failed

There are no prompts when running the macro.

Here is the beginning of the code:
ChDir "S:\"
Workbooks.Open Filename:="S:\file1.xls"
ActiveWorkbook.SaveAs Filename:="S:\merge\test.csv", _
FileFormat:=xlCSV, CreateBackup:=False

Any ideas?

 

[SkipVought]

Hi,

    application.displayalerts=false
    Workbooks.Open Filename:="S:\file1.xls"
    ActiveWorkbook.SaveAs Filename:="S:\merge\test.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    application.displayalerts=true

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[douglashindle]

I had previously tried it.

There is a DDE connection failure from Word to this file. However I can save it manually without issue. I have rights to the S drive.

I plugged in your code. The same error is displayed.

 

[douglashindle]

I re-ran the code. The error is further down in the code. However, the file created has no size.

 

[SkipVought]

post all your code and indicate where it craters.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[douglashindle]

Got the answer! - Must only have one sheet.
First I make a copy
Next delete all but the first sheet in the copy.
Last save as CSV.

(May include extra steps that are not required.)

ChDir "S:\"
Workbooks.Open Filename:="S:\000 041405. crletterlog5.xls"
ChDir "S:\MERGE"
ActiveWorkbook.SaveAs Filename:="S:\MERGE\temp1.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.ExclusiveAccess 'required to delete sheets
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Save
ActiveWindow.Close
Workbooks.Open Filename:="S:\MERGE\temp1.xls"
ActiveWorkbook.SaveAs Filename:="S:\MERGE\temp1.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close

 

[RobBentley]

I wrote a VBA application for this today with a graphical userform front end. If anybody wants to see how it works, let me know.