Help w/simple two step macro - Excel 2013

[bman84]

Hey there, I'm using Excel 2013, and hoping you can help me piece together the code to create a simple macro. I have an Excel spreadsheet that acts as a simple statement generator by populating a number of cells based on a VLOOKUP against a master list of account numbers.

The user enters the 4-digit account number, and the VLOOKUP formulas on a separate tab of the workbook bring back the required values (ie. Customer Name, Address, Balance, etc) from the master list.

Rather than have the user spend time entering an account number, and manually saving a PDF, I'm hoping to create a macro that does those steps for them. Desired process would look like this:

1) User pastes list of account numbers in COLUMN L on 'Entry' sheet
2) User enters a desired file path in cell A5 (location where PDF's will be saved)
3) User clicks a button to start the macro
4) Macro reads first account number in COLUMN L and writes it to cell H7
5) Macro moves to separate sheet called 'Renewal Letter' and saves the sheet as a PDF, with the same filename as the text in cell K6
6) Macro moves back to 'Entry' and writes the next account number from COLUMN L to cell H7
....and the loop of steps 5) and 6) continues until the entire list in COLUMN L has been completed


Thanks in advance for any help!

 

[BiocideJ]

Your request is neither simple nor two steps .

It certainly is doable, though, although I want to clarify a few things.
Can you confirm that
1. When you reference cell H7 you mean H7 on the 'Entry' sheet
2. When you reference cell A5 you also mean A5 on the 'Entry' sheet
3. When you reference column L you also mean on the 'Entry' sheet and also, what cell would the values start at? L2? with L1 being a header?
4. When you reference cell K6 you mean K6 on the 'Renewal Letter' Sheet (and that this is a formula that automatically updates)

Once I have these answers I think I (or someone else) can attempt a 'simple two step macro'.

 

[bman84]

Sorry, seemed a lot simpler in my head haha. The answer to all of your questions is YES. Thanks!

Your request is neither simple nor two steps .

It certainly is doable, though, although I want to clarify a few things.
Can you confirm that
1. When you reference cell H7 you mean H7 on the 'Entry' sheet
2. When you reference cell A5 you also mean A5 on the 'Entry' sheet
3. When you reference column L you also mean on the 'Entry' sheet and also, what cell would the values start at? L2? with L1 being a header?
4. When you reference cell K6 you mean K6 on the 'Renewal Letter' Sheet (and that this is a formula that automatically updates)

Once I have these answers I think I (or someone else) can attempt a 'simple two step macro'.

 

[BiocideJ]

After minimal testing, this appears to work.
I recommend starting with a small list to verify execution first.

Paste the following code into a Standard Module

Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub

In the end, it was simpler than I expected so your title wasn't so incorrect.

 

[bman84]

Thanks! Giving it a try now and getting 'Run-time error 1004' with the following line:
wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen

After minimal testing, this appears to work.
I recommend starting with a small list to verify execution first.

Paste the following code into a Standard Module

Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub

In the end, it was simpler than I expected so your title wasn't so incorrect.

 

[BiocideJ]

I forgot the sheet being exported needs to be active (apparently)

Try this amendment. Also, I cleaned up the ExportAsFixedFormat method's arguments so it is more clear what they are for.

Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.Activate
        wsPrint.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub

 

[bman84]

Thanks, but still getting the same error.

'Run-time error 1004' with the following line:
wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen

 

[BiocideJ]

Is it actually created the file?

If not, try putting this line before the ExportAsFixedFormat line to give you an idea of what/where it is trying to save to...

Msgbox filePath & fileName

Let me know what pops up in the message.

 

[bman84]

Popup box displays 'P:\Desktop\renewal\1110'. It did not actually create any files

Is it actually created the file?

If not, try putting this line before the ExportAsFixedFormat line to give you an idea of what/where it is trying to save to...

Msgbox filePath & fileName

Let me know what pops up in the message.

 

[BiocideJ]

That means that A5 on the Entry sheet (where the path was supposed to be entered) is blank.

I updated my code to be more accomodating and prompt for the path if it is not available in A5.
I also placed a little more error conditioning around the ExportAsFixedFormat line so THAT line won't error out anymore.

Let me know if you are still having problems.

Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    
    If filePath = "" Then
        Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "Select a Folder"
            .AllowMultiSelect = False
            .InitialFileName = ThisWorkbook.Path
            If .Show <> -1 Then
                Exit Sub    'user hit Cancel
            End If
            filePath = .SelectedItems(1)
        End With
    End If
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.Activate
        On Error Resume Next
        wsPrint.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=PDFAutoOpen
        On Error GoTo 0
        If Dir(filePath & fileName & "*") = "" Then
            MsgBox "Could NOT save " & filePath & fileName, vbCritical + vbOKOnly, "Error Exporting PDF"
        End If
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub