Created Macro to save file dynamically failing

[MrPink1986]

Hi there,

I have created a macro which saves a sheet from a workbook in a location that that changes daily. This is captured in the macro by using formulas in excel. The macro seems to create the file as expected however I get to the following line and it fails to save

 .SaveAs Filename:=CheminDest & fNAME & ".xlsx"

- I get a runtime error 1004.

The CheminDest string created seems to be fine and I cant seem to figure it out.


My code is below

Sub Save_Upload()

Dim WS As Worksheet, CheminDest As String, fNAME As String


    On Error Resume Next
    CheminDest = "C:\Data\4XXX\" & Sheets("Manual Price Assets").Range("R11") & "\" & Sheets("Manual Price Assets").Range("R13") & "\" & Sheets("Manual Price Assets").Range("R14") & "\"
    MkDir CheminDest
    On Error GoTo 0
    fNAME = Sheets("Manual Price Assets").Range("R20")
    
    Sheets(Array("Upload File")).Copy
    Sheets("Upload File").Name = "Upload File"


    With ActiveWorkbook
        For Each WS In .Worksheets
        Next WS


        .SaveAs Filename:=CheminDest & fNAME & ".xlsx"
        .Close Fasle
    End With
    
     MsgBox "Text file has been saved ", vbInformation, "Data backup"
    
    End Sub

Any help greatly appreciated.

 

[daverunt]

Remove the on error code to see where it is failing.

You have to make each folder in the path. Unlike Unix.

Untried.
https://superuser.com/questions/100...ies-in-one-command-from-windows-command-promp

 

[MrPink1986]

Its failing here

MkDir CheminDest

As my folders are not been created......
Is there a limit to the number of subfolders I can create using the code as it does create the top folder but fails on the second and third.

Remove the on error code to see where it is failing.

You have to make each folder in the path. Unlike Unix.

Untried.
https://superuser.com/questions/100...ies-in-one-command-from-windows-command-promp

 

[MrPink1986]

thanks got it - I created the folders separately and then combined them in my last statement - code is below.....

Sub Save_Upload()

Dim WS As Worksheet, CheminDest As String, fNAME As String


    On Error Resume Next
    CheminDest = "C:\Data\4XXX\" & Sheets("Manual Price Assets").Range("R11") & "\"
    MkDir CheminDest
    CheminDest2 = CheminDest & Sheets("Manual Price Assets").Range("R13") & "\"
    MkDir CheminDest2
    CheminDest3 = CheminDest2 & Sheets("Manual Price Assets").Range("R14") & "\"
    MkDir CheminDest3
    On Error GoTo 0
    fNAME = Sheets("Manual Price Assets").Range("R20")
    
    Sheets(Array("Upload File")).Copy
    Sheets("Upload File").Name = "Upload File"


    With ActiveWorkbook
        For Each WS In .Worksheets
        Next WS


        .SaveAs Filename:=CheminDest3 & fNAME & ".xlsx"
        .Close Fasle
    End With
    
     MsgBox "Text file has been saved ", vbInformation, "Data backup"
    
    End Sub

 

[daverunt]

Hi Glad you got it sorted.

As windows has a path length limit of 255 characters then MkDir would fail for a folder over that limit.