Excel trying to save as PDF using a Cell Value for both the Path and the File Name

Question

I've been using excel to save my Worksheet as both PDF and as a .XLS separately based on a cell value, but it saves in the same folder as the parent file. I have been trying to find a way to use VBA to save these files under various paths, and these paths are references in cell values based on a data validation tool.

For Example: Path:- D:\Collections\March\Region 1\ - Is referenced in cell D3

File Name: LN,FN (mm/dd/yy) - Is referenced in cell D4

The bold values keep changing based on Lookup formulas in Excel. The current code I am using to save to .XLS is:

Sub SaveASXLS()

Dim FName As String
Dim FPath As String

FPath = "D:\Collections\March\Region 5\Cerritos"
FName = Sheets("Sheet1").Range("D4").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=56

End Sub

The FPATH= "File Location" I got it to work with a fixed location, but have to change it everytime I switch my Region or City.

And the code I am using for exporting the file to PDF is:

Sub SavePDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("D4").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub

I am using both separately one as a Module and one a a basic macro, is there also a way to combine both these functions together.

Thanks alot in advance for your help.

Solution

This works fine for me

Option Explicit

Sub SaveASXLS()

Dim FName As String
Dim FPath As String

FPath = Range("E1").Value
FName = Range("I1").Value
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=56

End Sub

Use Value not text

To combine them you need to call them

Sub TwoMacros()
    SaveASXLS
    SavePDF
End Sub

Or call one from the other:

Option Explicit

Sub SaveASXLS()

Dim FName As String
Dim FPath As String

FPath = Range("E1").Value
FName = Range("I1").Value
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=56

SavePDF

End Sub

Comments

[Vishal Morari]

@Rayastafarian: Thanks alot, the combination of calling both Macros worked perfectly. However any thoughts on how to get the PDF to save in the same folder that my .XLS is going to, as of now all the PDFs are being saved on my Desktop because that is where the parent file is.

[Raystafarian]

You can get the path to the current file using dir and set that as the path for the pdf