Save a excel form as multiple/batch files of PDF's

[dlmoore99]

I have vba code that works on another workbook, but for whatever reason it will not work on a new workbook when I inserted into new workbook. When the button is pressed to create the PDF's, the code will take a record number from a cell on the excel worksheet (I1) (ie. 12) where the form is located and then pull data from a different worksheet, creating 12 pdf forms filled out with individualized info.

here is the original code that works:

Sub PDFCreate()
RowCount = Worksheets("Items").Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("DD1348").Select
    
    For i = 1 To RowCount
        Range("I1").Value = i
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\dana.l.moore\Desktop\DD Form 1348\" & Range("BG3").Value, Quality:=xlQualityStandard, IncludeDocProperties:= _
        True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
Next i
End Sub

here is the code adjusted for the new workbook:

Sub Batch_DA37499()
RowCount = Worksheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("Ind-Batch Weapon Card").Select
    
    For i = 1 To RowCount
        Range("F3").Value = i
       [COLOR=#FF0000] ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\dana.l.moore\Desktop\DA Form 3749\DA 3749  " & Range("G9").Value & "  " & Range("B17").Value, Quality:=xlQualityStandard, IncludeDocProperties:= _
        True, IgnorePrintAreas:=False, OpenAfterPublish:=False[/COLOR]

Next I

End Sub

When the macro is run I get a Run-time error '13': Type mismatch msgbox and the debugger points to the red text in the code

The record number in Range(F3) is an alpha-numeric number (ie... A026) as required. Once I enter the Record number into F3 and click the save button, the number entered changes to 1 and then continues to save countless pdf doc's in the designated folder and the info isn't populated, I have to press the ESC key for it to stop saving pdf doc's. I think that is where the Type mismatch error comes from, but as a newbie I don't know how to fix it.

Thank you for any assistance.

dlmoore99

 

[StephenCrump]

The record number in Range(F3) is an alpha-numeric number (ie... A026) as required. Once I enter the Record number into F3 and click the save button, the number entered changes to 1 and then continues to save countless pdf doc's in the designated folder and the info isn't populated, I have to press the ESC key for it to stop saving pdf doc's. I think that is where the Type mismatch error comes from, but as a newbie I don't know how to fix it.

I am guessing that the Sub Batch_DA37499 is triggered when you "click the save button"?

The loop in your code will populate 'Ind-Batch Weapon Card'!F3 with the numbers 1,2,3 ... up to RowCount. But it sounds like you want alpha numerics instead, perhaps the 1st, 2nd, 3rd ... etc value in some list? Where will your code find this list - perhaps in column A of worksheet "DataEntry"?

My guess is that the type mismatch error is due to an error in cell 'Ind-Batch Weapon Card'G9 or 'Ind-Batch Weapon Card'B17, e.g. #N/A or #VALUE , probably because there are formulae expecting alphanumerics but getting numerics instead?

 

[dlmoore99]

I still need help with this. I was able to get the code to input the correct data for each pdf. What's happening now, the loop to populate/create the pdf's doesn't stop at the row count F3. ie, if I put a 5 into cell F3 (looking to save the first 5 records) and click the save button, it will create 30+ pdfs (up to however many rows have data in them on the DataEntry worksheet).

 

[StephenCrump]

It's a little hard to diagnose without seeing your revised code ...

'Previously, F3 was the value of i within the iteration
For i = 1 To RowCount
    Range("F3").Value = i
    '...

'Now, F3 is the number of iterations?
For i = 1 To Range("F3").Value
    '...

I am guessing that you want something along these lines:

Dim RecordCount As Long, M As Long, N As Long
Const FIRST_ROW = 2

RecordCount = Worksheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row - FIRST_ROW + 1

M = 1   'say
With Worksheets("Ind-Batch Weapon Card")
    N = Application.Min(RecordCount, .Range("F3").Value) 'cell F3 say?
    For i = M To N
        'Get i-th record
        .Range("SomeRange").Value = i
        'produce PDF
        .ExportAsFixedFormat Type:=xlTypePDF, ....
    Next i
End With

 

[dlmoore99]

Yes, i was always suppose to be the number of iterations. New code is:

Sub Batch_DA37499()
RowCount = Worksheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("Ind-Batch Weapon Card").Select
    For i = 1 To RowCount
        Range("F3").Value = i
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\dana.l.moore\Desktop\DA Form 3749\DA 3749  " & Range("G9").Value & "  " & Range("B17").Value, Quality:=xlQualityStandard, IncludeDocProperties:= _
        True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
End Sub

I'm trying your code now and will let you know if it works.

 

[dlmoore99]

Yes, your new code works like a charm. I was pulling my hair out trying to figure out why my code would work on the other workbook, but not this one. Thank you so much.

 

[dlmoore99]

Stephen, is there any way to add saving the file to a folder created when the save button is clicked? I tired adding some code to yours, but it only creates the folder and not pdf doc's. I'm sure I don't have it in the right place or missing something. Being a noob can suck at times.

Sub PDFBatch_DA3749v2()
Dim myFolderName As String
Dim myFileName As String
myFolderName = Environ("userprofile") & "\desktop\DA 3749"
    If (Dir(myFolderName, vbDirectory)) = "" Then MkDir myFolderName
    myFileName = "DA 3749  " & Range("G9").Value & "  " & Range("B17").Value
 
Dim RecordCount As Long, M As Long, N As Long
Const FIRST_ROW = 2
RecordCount = Worksheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row - FIRST_ROW + 1
M = 1   'say
With Worksheets("Ind-Batch Weapon Card")
    N = Application.Min(RecordCount, .Range("F3").Value) 'cell F3 say?
    For i = M To N
        'Get i-th record
        .Range("F3").Value = i
        'produce PDF
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next i
End With
End Sub

 

[dlmoore99]

Stephen, I was able to figure it out with the following code:

Sub PDFBatch_DA3749v2()
Dim myFolderName As String
myFolderName = Environ("userprofile") & "\desktop\DA 3749"
    If (Dir(myFolderName, vbDirectory)) = "" Then MkDir myFolderName
 
Dim RecordCount As Long, M As Long, N As Long
Const FIRST_ROW = 2
RecordCount = Worksheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row - FIRST_ROW + 1
M = 1   'say
With Worksheets("Ind-Batch Weapon Card")
    N = Application.Min(RecordCount, .Range("F3").Value) 'cell F3 say?
    For i = M To N
        'Get i-th record
        .Range("F3").Value = i
        'produce PDF
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFolderName & "\  " & Range("G9").Value & "  " & Range("B17").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next i
End With
End Sub

Thanks again for all of your help.

 

[StephenCrump]

You're welcome. It's great that you were able to work it out.

 

[dlmoore99]

actually, once I opened the workbook today and made some changes to other items, my code at below now gets a error: "Run-time error '-2147024773 (800700b)': Document not saved.

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFolderName & "\  " & Range("G9").Value & "  " & Range("B17").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

And I have found the issue.