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

[Bmath81]

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!

[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]

 

[william_man]

It looks like you might be missing a '\' separator between your folder directory and file name:

[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]

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

 

[Bmath81]

for sure, when I post the code, the "/" are disappearing. darn!

 

[Fluff]

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]

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]

Thank you very much for the bit of info!
So, here is the code I am using:

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]

Hi!
The date will be different every time I perform this process. So no, I an not checking for the actual date.

 

[Fluff]

But do you have a folder for that date?

 

[Bmath81]

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...

Format(Now(), "MM.DD.YYYY")

.

It's worked for me else where.