File Location

tim220225

New Member
Joined
Jun 4, 2012
Messages
25
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
would adding
VBA Code:
Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents
and then & this year & "\" &
be an option?
 

tim220225

New Member
Joined
Jun 4, 2012
Messages
25
would adding
VBA Code:
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

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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...
VBA Code:
Range("A12").Formula = "=YEAR(A11)"
thisYear = Range("A12").Value
Range("A12").clearcontents
What am I missing?
 

tim220225

New Member
Joined
Jun 4, 2012
Messages
25

ADVERTISEMENT

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...
VBA Code:
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

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
VBA Code:
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...
VBA Code:
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
 
Solution

tim220225

New Member
Joined
Jun 4, 2012
Messages
25
VBA Code:
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...
VBA Code:
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

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top