Not saving correctly

CodingNewb · Dec 14th 2021

Sometimes my macro saves correctly, and other times, they save to a different file. Any idea why these are sending documents to the incorrect folder sometimes and the correct one other times? I'm just trying to save the workbook in the same folder that it is already in and add the date at the end - one in PDF format and the other in excel format.

Thank y ou!

Code

Sub SaveAsPDF()
'THIS ONE WORKS
' SaveAsPDF Macro


WBPath = ActiveWorkbook.path
WBName = Replace(ActiveWorkbook.Name, ".xlsm", "")
CustomerName = ActiveSheet.Range("C1").Text
PricingAnalysis = ActiveSheet.Range("C2").Text
currentDate = Format(Date, "MM.DD.YYYY")


ThisWorkbook.Sheets(Array("E_Cover Page", "E_Pricing Analysis (FP)")).Select
newFullPath = WBPath & "\" & WBName & " " & currentDate & ".PDF"


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=CustomerName & "_" & PricingAnalysis & "_" & currentDate & ".PDF"
'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


End Sub


Sub SaveAsFile()
'THIS ONE WORKS
' SaveAsFile Macro

    WBPath = ActiveWorkbook.path
    WBName = Replace(ActiveWorkbook.Name, ".xlsm", "")
    CustomerName = ActiveSheet.Range("B3").Text
    PricingAnalysis = ActiveSheet.Range("A1").Text
    currentDate = Format(Date, "MM.DD.YYYY")
    
    newFullPath = WBPath & "\" & WBName & " " & currentDate & ".xlsm"
    
    ActiveWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & currentDate & ".xlsm"
        'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Display More

**rory** · Dec 14th 2021

You're not specifying the path when you save, so they will end up in whatever the current directory is - there is no guarantee that that will be the same as the folder the workbook is currently in. You should always include the path when doing a SaveAs or export.

CodingNewb · Dec 14th 2021

Thanks! So what would that look like? I thought the newFullpath was specifying where to save it.

**rory** · Dec 14th 2021

You're not actually using newFullPath for the filename argument though...

**royUK** · Dec 14th 2021

I did tell you how to do this here but you didn't bother responding!!!

CodingNewb · Dec 14th 2021

Sorry. It didn't work when I tried it.

**royUK** · Dec 15th 2021

Well, don't ask another question. Continue with the original.

Quote

Sorry. It didn't work when I tried it.

That comment does nothing to help solve your problem.

What error message did you get?

What didn't work?

Respond to this in the original question and attach your workbook with the code in that doesn't work.

CodingNewb · Dec 15th 2021

Quote from royUK

Well, don't ask another question. Continue with the original.

That comment does nothing to help solve your problem.

What error message did you get?
What didn't work?

Respond to this in the original question and attach your workbook with the code in that doesn't work.

Display More

I don't get an error message. It just still saves to random folders. Just used the code below and it saved to the Documents folder on my computer, not the folder the file is in.

Code

Sub SaveAsFile_2()


Dim WBPath As String, WBName As String, CustomerName As String, PricingAnalysis As String, newFullPath As String
' SaveAsFile Macro
WBPath = ThisWorkbook.Path
WBName = Replace(ThisWorkbook.Name, ".xlsm", "")
CustomerName = ActiveSheet.Range("B3").Text
PricingAnalysis = ActiveSheet.Range("A1").Text
'File Formats:
'51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)


newFullPath = WBPath & "\" & WBName
ThisWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"


Range("B27:G40").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=17
Range("B49:G62").Select
Selection.ClearContents
Range("A1").Select


End Sub

Display More

**rory** · Dec 15th 2021

You still haven't put the path into the SaveAs line.

CodingNewb · Dec 15th 2021

Thanks, Rory! Where do I put it in the SaveAs line? I put it after SaveAs, but it errored out.

**KjBox** · Dec 15th 2021

Try changing

Code

newFullPath = WBPath & "\" & WBName
ThisWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

to

Code

newFullPath = WBPath & "\" & WBName & "\"
ThisWorkbook.SaveAs fileName:=newFullPath & CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

CodingNewb · Dec 15th 2021

Unfortunately, it still bugged out on the saveAs line.

**KjBox** · Dec 15th 2021

Does the folder specified in "newFullPath" always exist, and is it in the same folder as "ThisWorkbook"?

**royUK** · Dec 16th 2021

I asked you to continue in the previous Thread

Not saving correctly

Participate now!

Share