Consulting

Results 1 to 5 of 5

Thread: Run-Time error when saving

  1. #1
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    3
    Location

    Run-Time error when saving

    I am trying to do a save into a dynamic folder path (...\YYYY\03 March\), but continue to get an error message "Method 'Save As' of object '_Workbook' failed

    below is my code, i've also included a msg box, to verify my path in the macro, but continue to have issues.


    MsgBox "W:\Finance\Billings" & ActiveSheet.Range("B1") & "" & ActiveSheet.Range("C1") & ActiveSheet.Range("A1") & ""

    Dim Path As String
    Dim filename As String
    Path = "W:\Finance\Billings" & ActiveSheet.Range("B1") & "" & ActiveSheet.Range("C1") & ActiveSheet.Range("A1") & ""
    filename = Range("D1")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Cell B1 is the year, C1 is the month (03), D1 is the File name for the file

    Any suggestions?

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not much to go on, but

    What's in A1, B1, C1, and D1?

    What exactly did the MSgBox show?

    Do you have all the backslashs correct? Don't see any, but I'm guessing that the "" are supposed to be "\" and got deleted in the forum (need to use 2 backslashes to get 1, first is the escape char)

    My best guess is that you want ""W:\Finance\Billings"" to be ""W:\Finance\Billings\"



    Option Explicit
    Sub test()
        Dim Path As String
        Dim filename As String
        Path = "W:\Finance\Billings\"                           '   note the \
        Path = Path & Format(ActiveSheet.Range("B1"), "0000") & "\"   '   year
        Path = Path & Format(ActiveSheet.Range("C1"), "00") & "\"     '   month
        
        filename = Range("D1")
        MsgBox Path & filename
    '   ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End Sub

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    3
    Location
    Sorry, attached is my full code.

    Sub ADB_NegativeBal_2()
    '
    ' ADB_NegativeBal_2 Macro
    '


    '
    Sheets("Negative Balance").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=TEXT(TODAY()-10, ""MMMM"")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=YEAR(TODAY()-10)"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(LEN(MONTH(TODAY()-10))=1, ""0""&MONTH(TODAY()-10)&"" "",MONTH(TODAY()-10))&"" """
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=""ADB - ""&RC[-3]"
    Range("A11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    MsgBox "W:\Finance\Billings" & ActiveSheet.Range("B1") & "" & ActiveSheet.Range("C1") & ActiveSheet.Range("A1") & ""

    Dim Path As String
    Dim filename As String
    Path = "W:\Finance\Billings" & ActiveSheet.Range("B1") & "" & ActiveSheet.Range("C1") & ActiveSheet.Range("A1") & ""
    filename = Range("D1")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


    End Sub

    Capture.PNG

    this was my message box

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    If you don't need the worksheet updated, you can use something like this. I think this gets you the format

    This assumes that the folder exists

    I did the sFullPath piece meal so that I could single step through the macro (F8) and hover the mouse over variables

    Capture.JPG


    Option Explicit
    
    Sub ADB_NegativeBal_2()
    
        Dim sFullPath As String
    
        sFullPath = "W:\Finance\Billings\"
        sFullPath = sFullPath & Format(Now - 10, "yyyy") & "\"                  '   2019
        sFullPath = sFullPath & Format(Month(Now - 10), "00") & " "             '   03
        sFullPath = sFullPath & Format(Now - 10, "mmmm") & "\"                  '   March
        sFullPath = sFullPath & "ADB - " & Format(Now - 10, "yyyy") & ".xlsx"   '   ADB - March
        
        MsgBox sFullPath
        
    '    ActiveWorkbook.SaveAs filename:=sFullPath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    3
    Location
    That worked perfectly, thank you for the help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •