File Location

[tim220225]

Hello All,

I am using the code below to save a worksheet as a PDF in a specific file path. Works fine.

What I need to have it do now is to create and save the PDF in the same file path location but add a specific folder for the current year that is located in the file path. The year will obviously change, I have a helper cell that I am trying to reference into the file path but I am failing miserably at it. The referenced cell is AX11.

Thanks in advance.

Tim


Sub SaveAsPDFTractorInternalWorkOrder()
'Saves active worksheet named Tractor Internal Work Order as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet
fName = .Range("B14").Value & "." & .Range("AX2").Value & " " & .Range("AX4").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

 

[braindiesel]

would adding

Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents

and then & this year & "\" &
be an option?

 

[tim220225]

would adding

Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents

and then & this year & "\" &
be an option?

The ranges I have are used to create the file name which is fine for what I do. What I need it to do is find a way to incorporate the value in cell AX11 into the .Export where the File Path "C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fName, Quality:=xlQualityStandard, _. Basically after the slash following the words Work Orders I need to add a reference to cell AX11. AX 11 is the current year and I have a folder named that in the file location.

 

[braindiesel]

With my above code... this is what I was suggesting...

"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & thisYear & "\" & fName, Quality:=xlQualityStandard, _

thisYear stores the target folder...

Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents

What am I missing?

 

[tim220225]

With my above code... this is what I was suggesting...

"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & thisYear & "\" & fName, Quality:=xlQualityStandard, _

thisYear stores the target folder...

Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents

What am I missing?

I think I am missing or not understanding where you are saying to insert your code. Not sure why the reference to A12 or A11 or clear contents comes from. Sorry, I am not good with this stuff for as much as I try.

 

[braindiesel]

Sub SaveAsPDFTractorInternalWorkOrder()
'Saves active worksheet named Tractor Internal Work Order as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet

Range("A12").Formula = "=YEAR(A11)"'I chose A12 as a random cell to hold data to extract the year from the date
thisYear = Range("A12").Value 'If A11 contains JUST they year and your file folder has that exact same name,
'then you do not need the previous line with the formula... you just need thisYear = Range("A11").value
Range("A12").clearcontents

fName = .Range("B14").Value & "." & .Range("AX2").Value & " " & .Range("AX4").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & thisYear & "\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

I made some notes above to add clarity (I hope)
If A11 contains a date like 3/5/2021, then my top three lines should help you...
If A11 contains the year number alone 2021 for example, and your target file folder has teh same name "2021"...
then you could likely just do this...

Sub SaveAsPDFTractorInternalWorkOrder()
'Saves active worksheet named Tractor Internal Work Order as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet
fName = .Range("B14").Value & "." & .Range("AX2").Value & " " & .Range("AX4").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & range("A11").value & "\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

 

[tim220225]

Sub SaveAsPDFTractorInternalWorkOrder()
'Saves active worksheet named Tractor Internal Work Order as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet

Range("A12").Formula = "=YEAR(A11)"'I chose A12 as a random cell to hold data to extract the year from the date
thisYear = Range("A12").Value 'If A11 contains JUST they year and your file folder has that exact same name,
'then you do not need the previous line with the formula... you just need thisYear = Range("A11").value
Range("A12").clearcontents

fName = .Range("B14").Value & "." & .Range("AX2").Value & " " & .Range("AX4").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & thisYear & "\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

I made some notes above to add clarity (I hope)
If A11 contains a date like 3/5/2021, then my top three lines should help you...
If A11 contains the year number alone 2021 for example, and your target file folder has teh same name "2021"...
then you could likely just do this...

Sub SaveAsPDFTractorInternalWorkOrder()
'Saves active worksheet named Tractor Internal Work Order as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet
fName = .Range("B14").Value & "." & .Range("AX2").Value & " " & .Range("AX4").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & range("A11").value & "\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

Thank you!!

The second one you sent did the trick. I do only have the year in the reference cell and it automatically updates to the current year. I was missing the & "\" $ fName, from my code.

Again a huge thank you!

 

[braindiesel]

Thank you!!

The second one you sent did the trick. I do only have the year in the reference cell and it automatically updates to the current year. I was missing the & "\" $ fName, from my code.

Again a huge thank you!

Glad it worked. Thank you for letting me know.