• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

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,304
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,304
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,304
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,965
Messages
5,337,441
Members
399,147
Latest member
Raviteja KOTHA

Some videos you may like

This Week's Hot Topics

Top