Problem saving a spreadsheet as *.csv using VBA

Question

The problem is that I am trying to save this spreadsheet (that I already have opened and saved as *.xlsx) as a *.csv file and I am having the error Runtime error 1004 Method 'SaveAs' of object '_Workbook' failed.

This is for Excel 2016.

Dim FilePath as string

FilePath = "U:\CAP Data - Every Month\ISEAdHoc\" & FileMonth & FileYear & "\"

Dim ISEAdHoc_FileName_CSV As String

ISEAdHoc_FileName_CSV = "ISEAdHoc_" & FileMonth & "CAP_" & Format(Now(), "YYYYMMDD") & "_" & Format(Now(), "HHMMSS")

Dim FileNameforCSVandPath As String

FileNameforCSVandPath = FilePath & ISEAdHoc_FileName_CSV & ".csv"

Sheets("ISEAdHoc").Activate

'ActiveWorkbook.SaveAs Filename:=FilePath & ISEAdHoc_FileName_CSV, FileFormat:=xlCSV, CreateBackup:=False, Local:=True

Workbooks(FileNameforCSVandPath).Close SaveChanges:=True

I expect to save the file as a *.csv. But for some reason I keep getting the error "Runtime error 1004 Method 'SaveAs' of object '_Workbook' failed." Please help!

If I can suggest taking the approach laid out in this answer from today, I think you'll find your problem. — BigBen
– BigBen, Commented Jun 10, 2019 at 15:21

Mathieu Guindon · Accepted Answer · 2019-06-10 16:16:55Z

1

Your code seems to work fine as-is. Verify that your U: drive mapped correctly.

I was able to copy your code and it worked on my side.

edited Jun 10, 2019 at 16:16

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Jun 10, 2019 at 15:31

JCalvano

531 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Damian Over a year ago

Do not post questions as answers please.

Mathieu Guindon Over a year ago

@Damian eh, it's a legit reason for the code to be failing, and at 40 rep one cannot post comments - edited into a statement

Damian Over a year ago

@MathieuGuindon like it is now, yes. But he was asking to the OP. that wasn't an answer.

Collectives™ on Stack Overflow

Problem saving a spreadsheet as *.csv using VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related