Run-Time error '1004' Method 'SaveAs' of object'_Wokkbook' failed

Bmath81

New Member
Joined
Nov 6, 2019
Messages
6
Hello,
I'm trying to save a newly created file in csv format.
I am copying results from one open file, and pasting in a new workbook, and then trying to save the new workbook as a csv file.

My code is below. See the "Save File as CSV" section below. Thanks!


Code:
[SIZE=2]Sub Monthly_Device_Volume_Map()
'
' Monthly_Device_Volume_Map Macro
'

    Sheets("Device_volume_map").Select
    Range("A2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Device_volume_map").Select
    Columns("A:B").Select
    Selection.Copy
    Sheets("Verix Upload").Select
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Material"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Method"
    Columns("B:B").Select
    Selection.Replace What:="1", Replacement:="QTY", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Sheets("Verix Upload").Select
    Application.CutCopyMode = False
    Sheets("Verix Upload").Copy
    
    
 Application.DisplayAlerts = False
    
    '  Check for year folder and create if needed
    If Len(Dir("Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date), vbDirectory)) = 0 Then
        MkDir "Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date)
    End If
    
    
     ' Check for month folder and create if needed
    If Len(Dir("Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "\" & MonthName(Month(Date), False), vbDirectory)) = 0 Then
        MkDir "Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "\" & MonthName(Month(Date), False)
    End If
      

[FONT=microsoft sans serif][I][U][B]The next part is giving me the run-time error:[/B][/U][/I][/FONT]

   
'Save File as CSV
    ActiveWorkbook.SaveAs Filename:= _
    "Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "\" & MonthName(Month(Date), False) & "\" & Format(Now(), "MM.DD.YYYY") & "\" & "Device_volume_map.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
     
'Allows Popup Message below
    Application.DisplayAlerts = True
     
'Popup Message
    MsgBox "File Saved As:" & vbNewLine & "Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "\" & MonthName(Month(Date), False) & "\" & Format(Now(), "MM.DD.YYYY") & "\" & "Device_volume_map.csv"
       
'Closes the standalone CSV file created above
    Windows("Device_Volume_Map.csv").Activate
    ActiveWindow.Close
    
'Tells Excel to close excel 2010 after file is saved
    Application.Quit
    
'Saves this workbook with changes
    ActiveWindow.Close SaveChanges:=True
    
End Sub[/SIZE]
 
Last edited by a moderator:

william_man

New Member
Joined
Oct 31, 2019
Messages
19
It looks like you might be missing a '\' separator between your folder directory and file name:
Code:
[COLOR=#008000]'Save File as CSV[/COLOR][COLOR=#b22222]
ActiveWorkbook.SaveAs Filename:= _
"Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "" & MonthName(Month(Date), False) & [B]"\"[/B] & Format(Now(), "MM.DD.YYYY") & "Device_volume_map.csv" _
, FileFormat:=xlCSV, CreateBackup:=False[/COLOR]
Please try this and let me know how you get on!
 

Bmath81

New Member
Joined
Nov 6, 2019
Messages
6
Hmmmm...that is strange. When I review what I posted above, the "/" are missing. But my code has the "/"s. Let's try again.....

ActiveWorkbook.SaveAs Filename:= _
"Z:\BM 1 - Verix Data and Upload Management\Device Volume Map" & Year(Date) & "" & MonthName(Month(Date), False) & "" & Format(Now(), "MM.DD.YYYY") & "" & "Device_volume_map.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
That is a known problem with the board software, if you enclose your code within code tags (the # icon in the reply window) the problem will go away.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Are you actually using \ or / in the file path?

Also you are checking for folders for the year & month, but not for the date.
 

Bmath81

New Member
Joined
Nov 6, 2019
Messages
6
Thank you very much for the bit of info!
So, here is the code I am using:

Code:
Save File as CSV
    ActiveWorkbook.SaveAs Filename:= _
    "Z:\BM 1 - Verix Data and Upload Management\Device Volume Map\" & Year(Date) & "\" & MonthName(Month(Date), False) & "\" & Format(Now(), "MM.DD.YYYY") & "\" & "Device_volume_map.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
 

Bmath81

New Member
Joined
Nov 6, 2019
Messages
6
Hi!
The date will be different every time I perform this process. So no, I an not checking for the actual date.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
But do you have a folder for that date?
 

Bmath81

New Member
Joined
Nov 6, 2019
Messages
6
No, I do not have a pre-created folder for this or any date. I thought that this code would create a date folder at the time I run the macro...
Code:
Format(Now(), "MM.DD.YYYY")
.

It's worked for me else where.
 

Forum statistics

Threads
1,077,849
Messages
5,336,736
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top