Creating dummy PDF files in vba-created folder

J

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
477
Office Version
  1. 365
Platform
  1. Windows
Good afternoon!

I have this code that I use to automatically create folders on our server if they don't already exist. strDir1 is the Sub folder and strDir2 is the Lot folder.

VBA Code: 
 Sub CreateSubAndLotFolder()
 
  Const strPARENTDIR As String = "\\SERVER\Homes\"
  Dim strDir1 As String
  Dim strDir2 As String
 
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
 
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
   
  If Len(Dir$(strPARENTDIR & strDir1, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  ElseIf Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  End If
 
End Sub

What I'd like to do now is create six dummy PDF files in the newly-created Lot folder.

If the existing macro creates a subdivision folder called Test_Folder_9999 and within that folder a lot folder called TST_001, I now need to create these dummy PDFs in the TST_001 folder that uses the G1 value as part of the file name:

TST_001_floor_plans.pdf
TST_001_floor_system.pdf
TST_001_plans.pdf
TST_001_boise_cut_sheets.pdf
TST_001_plot_plan.pdf
TST_001_options.pdf

Make sense? There's not a lot of information on creating dummy files when I search.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Would it make more sense to have an already created folder on the server that has the six dummy PDF files in it, then copy them into the newly created Lot folder and amend the filenames based on the G1 value?
 
Upvote 0
Does anyone have experience creating dummy files or am I a dummy for trying?
 
Upvote 0
Tried this. Getting file path error when it tries to copy the file to the newly created Lot folder?:

VBA Code: 
 Sub CreateSubAndLotFolder()
 
  Const strPARENTDIR As String = "\\GLC-SERVER\Pulte\"
  Dim strDir1 As String
  Dim strDir2 As String
  
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
  
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
    
  If Len(Dir$(strPARENTDIR & strDir1, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  ElseIf Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  End If
  
  FileCopy "\\GLC-SERVER\Pulte\Z_Dummy_Files\XXX_000_plans.pdf", "strPARENTDIR & strDir1 & Chr$(92) & strDir2"
  
End Sub
 
Upvote 0
jmpatrick said:
What I'd like to do now is create six dummy PDF files in the newly-created Lot folder.
Click to expand...
Save an empty cell as a PDF:
VBA Code: 
    Range("A1").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\folder\path\file.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
adjust path and file name to suit. Repeat for all 6 files.
 
Upvote 0
John_w said:
Save an empty cell as a PDF:
VBA Code: 
    Range("A1").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\folder\path\file.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
adjust path and file name to suit. Repeat for all 6 files.
Click to expand...

Thanks for the reply! Can I add that code to this?:

VBA Code: 
 Sub CreateSubAndLotFolder()
 
  Const strPARENTDIR As String = "\\GLC-SERVER\Pulte\"
  Dim strDir1 As String
  Dim strDir2 As String
 
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
 
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
   
  If Len(Dir$(strPARENTDIR & strDir1, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  ElseIf Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
  End If
 
End Sub

If so, what sheet is A1 on?
 
Upvote 0
John_w said:
Save an empty cell as a PDF:
VBA Code: 
    Range("A1").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\folder\path\file.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
adjust path and file name to suit. Repeat for all 6 files.
Click to expand...

OK, thanks to your code I'm getting closer.

The only thing that's missing is I that need the path and first part of the file name to be pulled from the PARENTDIR and G1 values.

Using the example in the current code, \\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_plot_plan.pdf is a correct path but it's not dynamic.

\\SERVER\Test\ is defined here:

VBA Code: 
Const strPARENTDIR As String = "\\SERVER\Test\"

Andover_Forest_1119\ should be the Lot folder we just created.

AND_699\AND_699_ should come from here:

VBA Code: 
strDir2 = Sheets("LotGrid").Range("G1").value

...and the rest is the different file variations with file format I need:

plans.pdf
plot_plan.pdf
boise_cut_sheets.pdf
floor_plans.pdf
floor_system.pdf
options.pdf

Here's the current code:

VBA Code: 
 Sub CreateLotFolder()
 
  Const strPARENTDIR As String = "\\SERVER\Test\"
  Dim strDir1 As String
  Dim strDir2 As String
 
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
 
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
 
  If Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
 
  End If
 
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Sheets("LotGrid").Visible = True
      Sheets("LotGrid").Select
 
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_plot_plan.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_plans.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_boise_cut_sheets.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_floor_plans.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_options.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\SERVER\Test\Andover_Forest_1119\AND_699\AND_699_floor_system.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
     
      Sheets("LotGrid").Visible = False
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True

End Sub
 
Upvote 0
Getting closer! This works BUT the resulting pdf is in the Sub folder. It should be in the Lot folder (which is in the Sub folder):

VBA Code: 
 Sub CreateLotFolder()
 
  Const strPARENTDIR As String = "\\SERVER\Test\"
  Dim strDir1 As String
  Dim strDir2 As String
  
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
  
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
    
  If Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
    
  End If
  
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Sheets("LotGrid").Visible = True
      Sheets("LotGrid").Select
  
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2) & "_plot_plan.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Sheets("LotGrid").Visible = False
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True

End Sub
 
Upvote 0
Success!!

Just a little tweak and it works. Here's the final code:

VBA Code: 
 Sub CreateLotFolder()
 
  Const strPARENTDIR As String = "\\SERVER\Test\"
  Dim strDir1 As String
  Dim strDir2 As String
  
  strDir1 = Sheets("LotGrid").Range("H1").value 'Subdivision Name / SubCode
  
  strDir2 = Sheets("LotGrid").Range("G1").value 'Sub Initials / Lot Number
    
  If Len(Dir$(strPARENTDIR & strDir1 & Chr$(92) & strDir2, vbDirectory)) = 0 Then
    MkDir strPARENTDIR & strDir1 & Chr$(92) & strDir2
    
  End If
  
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Sheets("LotGrid").Visible = True
      Sheets("LotGrid").Select
  
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_plot_plan.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_plans.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_options.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_floor_plans.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_floor_system.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Range("A3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=(strPARENTDIR & strDir1 & Chr$(92) & strDir2 & Chr$(92) & strDir2) & "_boise_cut_sheets.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
        
      Sheets("LotGrid").Visible = False
      Sheets("Calendar").Select
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True

End Sub

Thanks, John_w for your help!
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

T
  • Solved
VBA to create folders and sub-folders
Replies
3
Views
432
Georgiboy
Georgiboy
A
  • Solved
create folders and hyperlink to them
Replies
5
Views
268
Domenic
D
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
44
VanGirl
V
S
  • Question
Excel macros extremely slow
Replies
4
Views
176
Shloime
S
T
  • Solved
I can't open PDF file after save
Replies
3
Views
312
tubrak
T

Forum statistics

Threads
1,214,642
Messages
6,120,700
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top