Run-Time error when saving

[voegs50]

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?

[Paul_Hossler]

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





24007

[voegs50]

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("A1:D1").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

24008

this was my message box

[Paul_Hossler]

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

24009





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

[voegs50]

That worked perfectly, thank you for the help!