save sheet as pdf to subfolders based on cell value

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
985
Office Version
  1. 2010
hi all
i search code save sheet as pdf to subfolder the directory is "C:\Users\OSE\Downloads\client \" i have main folder calls client and contains many subfolders customer1,customer2,customer3,customer4 when i fill in cell h2 = customer1 then save as pdf to subfolder customer1 and if i fill customer2 in h2 then save as pdf to subfolder customer2 and so on
i appreciate if anybody help
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
It looks like the folder name and the file name are made up of the contents of C2 and C3. If that is true, post an example of the contents of C2 and C3 and what the full path would look like using those values.

Full path: S:\Fire Dispatchers\Circuits Program\Reading_Data

Value of C2 and C3:
1601662953558.png


Format for C2: mm-dd-yyyy
Format for C3: hhmm

Thanks!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
So the full path including the folder name and the file name would look like this where the folder name is in red and the filename is in blue:
Rich (BB code):
S:\Fire Dispatchers\Circuits Program\Reading_Data\10-02-20201200\10-02-20201200.pdf
Is this correct? If not, please post your expected result.
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
So the full path including the folder name and the file name would look like this where the folder name is in red and the filename is in blue:
Rich (BB code):
S:\Fire Dispatchers\Circuits Program\Reading_Data \10-02-20201200\10-02-20201200.pdf
Is this correct? If not, please post your expected result.

I would just want to had a space or - in between the date and time.

Example: 10-02-2020-1200

Thanks!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
Private Sub CommandButton1_Click()
    ChDir "S:\Fire Dispatchers\Circuits Program\Reading_Data\" & ActiveSheet.Range("C2").Value & "-" & ActiveSheet.Range("C3").Value & "\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Range("C2").Value & "-" & ActiveSheet.Range("C3").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
End Sub
Make sure you have an existing folder named "10-02-2020-1200" otherwise you will get the same error.
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14

ADVERTISEMENT

Try:
VBA Code:
Private Sub CommandButton1_Click()
    ChDir "S:\Fire Dispatchers\Circuits Program\Reading_Data\" & ActiveSheet.Range("C2").Value & "-" & ActiveSheet.Range("C3").Value & "\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Range("C2").Value & "-" & ActiveSheet.Range("C3").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
End Sub
Make sure you have an existing folder named "10-02-2020-1200" otherwise you will get the same error.


I apologize the folder is actually Reading_Data

The name of the PDF document should just be the example 10-02-2020-1200

I apologize. Jeff
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
Private Sub CommandButton1_Click()
    ChDir "S:\Fire Dispatchers\Circuits Program\Reading_Data\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Range("C2").Value & "-" & ActiveSheet.Range("C3").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
End Sub
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14

ADVERTISEMENT

1601664420232.png


The folder isn't open, the program was saved as a "Macro Enabled Worksheet".

Jeff
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. (de-sensitized if necessary).
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Click here to download your file. There was something about your sheet that kept generating an error when the macro tried to save the file as a PDF. I created a new blank sheet and copied/pasted the cells from the READING sheet to the new blank sheet. I then deleted the original READING sheet and renamed the new one as "READING". The macro now seems to work. Click the button.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,615
Messages
5,625,871
Members
416,141
Latest member
Bartek9q

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