Creating dummy PDF files in vba-created folder

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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?
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
Does anyone have experience creating dummy files or am I a dummy for trying?
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,052

ADVERTISEMENT

What I'd like to do now is create six dummy PDF files in the newly-created Lot folder.
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.
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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.

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?
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

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
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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
 

jmpatrick

Board Regular
Joined
Aug 17, 2016
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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!
 
Solution

Forum statistics

Threads
1,144,679
Messages
5,725,738
Members
422,636
Latest member
GatorsBucs

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