Loop Data validation list item and generate PDF for each

[a_chaitanya]

Hi Team,

I have gone through various sites, but, not found what I am looking for. If the post is not valid, my apologize for this.

Here is what the post is about:
1. I have created a Named range using Macro. The named range used in data validation list, placed in cell H9 with in "Invoice Format" sheet.
2. If I select the item in the list manually, it is fine, values will change accordingly in the format - used look-ups.
3. Here comes what I required is - The list should automatically loop within and the format should capture as "PDF" file for each value in the List item.

In the file attached here is, in the "Invoice Format" sheet, there is two page format. Where in the second page of the format/template the values in it will show based on the column G in the "DSR_Format" sheet. If the values in second page of the Invoice Format sheet is visible, then only the second page also should capture in the PDF. Other wise, one page as PDF.

Hope I am clear with the above. If not, let me know.

I am aware of the forum rules, but, not able to link the urls posted on on Excelforum and ozgrid.com with the same title.

Thanks
Chaitanya

[p45cal]

I am aware of the forum rules, but, not able to link the urls posted on on Excelforum and ozgrid.com with the same title.]

Here they are:
http://www.excelforum.com/excel-prog...-for-each.html
http://www.ozgrid.com/forum/showthre...113#post682113

[a_chaitanya]

Hi P45cal,

I knew that, but, because of less than 5 posts, I couldn't. That is the reason I have mentioned. Thanks for the help.

[p45cal]

I realised that.
Try:

Sub blah()
With Sheets("Invoice Format")
  For Each cll In Range("Filtered").Cells
    .Range("H9") = cll.Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="Loop2 throug the list vbaexpress47376" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
  Next cll
End With
End Sub

The files will be in your default directory. Change the "Loop2 throug the list vbaexpress47376" & cll.Value & ".pdf" to suit (can include the full path).

Edit post posting: I realise I haven't fully answered your one/two page format - am looking at it.

[a_chaitanya]

Hi p45cal,

It's simply superb and cool. Liked it!

one/two page format: In Invoice Format sheet, there are two templates, the second will be the page 2. In DSR_Format sheet column G has two things (One Shot / Installment), based on this, if the cell H9 in Invoice has Installments, then only the values in the second template will be and that is to be included. If not, only first template should be captured as PDF.

Also, is it possible to print the generated PDFs? If so, this should be a separate part / module to use.

Thanks for the quick help.

[p45cal]

try:

Sub blah2()
With Sheets("Invoice Format")
  For Each cll In Range("Filtered").Cells
    .Range("H9") = cll.Value
    If InStr(LCase(cll.Offset(, 5)), "instal") = 0 Then
      .Range("B4:H41").ExportAsFixedFormat Type:=xlTypePDF, Filename:="Loop2 throug the list vbaexpress47376" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Else
      .Range("B49:H90").ExportAsFixedFormat Type:=xlTypePDF, Filename:="Loop2 throug the list vbaexpress47376" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If
  Next cll
End With
End Sub

As to the separate print routine, this will involve a bit more coding (finding the files first, selecting which files to print etc.). Perhaps a separate thread?
(It would be easy to ask if a print is required at the time the files are being generated, either as a one by one question or as a batch process (or both, even!).)

[p45cal]

Separate Print routine. This in a new code-module:

Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hwnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) _
  As Long
Public Sub PrintFile(ByVal strPathAndFilename As String)
Call apiShellExecute(Application.hwnd, "print", strPathAndFilename, vbNullString, vbNullString, 0)
End Sub
Sub PrintPDFfiles()
Dim fNameAndPath As Variant, wb As Workbook
fNameAndPath = Application.GetOpenFilename(FileFilter:="Pdf Files (*.pdf), *.pdf", Title:="Select File(s) To Be Printed", MultiSelect:=True)
If IsArray(fNameAndPath) Then
  For Each pdf In fNameAndPath
    PrintFile pdf
  Next pdf
End If
End Sub

and run PrintPDFfiles.
You can select multiple files to be printed using mouse left-clicks and the Shift and/or the Ctrl key.

The reason for saying a new code-module is actually only so that the

Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hwnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) _
  As Long

is in the Declarations area of the code-module

[a_chaitanya]

Hi p45cal,

Thanks for the needful. You are really cool....

I haven't tried the Print module. will let you know once Im done with the try.

Private Sub CommandButton1_Click()Dim FileN As FileDialog


With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show


    If .SelectedItems.Count > 0 Then
    With Sheets("Invoice Format")
        For Each cll In Range("Filtered").Cells
            .Range("H9") = cll.Value
            If InStr(LCase(cll.Offset(, 85)), "instal") = 0 Then
                .Range("B4:H47").ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
            Else
                .Range("B4:H105").ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        Next cll
    
    End With


MsgBox ("Completed publishing PDFs")
Else
MsgBox ("You have cancelled the folder selection")
End If


End With
End Sub

For the above code by you is modified, I am trying to access the file dialog, it is not selecting the actual folder, but, the accessing the excel file's path and throwing the PDFs. Could you please help me in getting it done.

Thanks,
Chaitanya

[p45cal]

I suspect you want to change this bit to:
Filename:=.SelectedItems(1) & "\" & cll.Value & ".pdf"
in both places.

[a_chaitanya]

replaced the statement

Filename:=cll.Value & ".pdf", Quality:=xlQualityStandard

by yours, as..

Filename:=.SelectedItems(1) & "\" & cll.Value & ".pdf", Quality:=xlQualityStandard

but, it is giving run-time error "438".

trying to get it done, not happening.

[p45cal]

I'm not near a machine with 2007 or later on, but you have introduced another With statement, so try the following (I can't test here at the moment):

Private Sub CommandButton1_Click()
Dim FileN As FileDialog
Set FileN = Application.FileDialog(msoFileDialogFolderPicker)
With FileN
  .AllowMultiSelect = False
  .Show
  If .SelectedItems.Count > 0 Then
    With Sheets("Invoice Format")
      For Each cll In Range("Filtered").Cells
        .Range("H9") = cll.Value
        cll.Offset(, 85).Select
        If InStr(LCase(cll.Offset(, 85)), "instal") = 0 Then
          .Range("B4:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileN.SelectedItems(1) & "\" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Else
          .Range("B4:H105").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileN.SelectedItems(1) & "\" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
      Next cll
    End With
    MsgBox ("Completed publishing PDFs")
  Else
    MsgBox ("You have cancelled the folder selection")
  End If
End With
End Sub

[a_chaitanya]

Hi p45cal,

Im really thankful for you providing me with needful. It is working, selecting folder as required. the only thing is [code] cll.offset(, 85).Select[\code] gave error it this statement. Gone through and removed that, then, it worked fine.

Also, Print module is superb!

Once again, thanks for your precious time. You are awesome!

[a_chaitanya]

Hi p45cal,

My apology for not mentioning the requirements at the earliest in the beginning itself.
An addition to the PDFs generation code is, If in the column "F" of DSR_Format sheet the value is "0" (zero), then for that particular entry, PDF should not generate.

Is there any chance of doing this?

Thanks

[p45cal]

try:

Private Sub CommandButton1_Click()
Dim FileN As FileDialog
Set FileN = Application.FileDialog(msoFileDialogFolderPicker)
With FileN
  .AllowMultiSelect = False
  .Show
  If .SelectedItems.Count > 0 Then
    With Sheets("Invoice Format")
      For Each cll In Range("Filtered").Cells
        If cll.Offset(, 4).Value <> 0 Then
          .Range("H9") = cll.Value
          If InStr(LCase(cll.Offset(, 85)), "instal") = 0 Then
            .Range("B4:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileN.SelectedItems(1) & "\" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
          Else
            .Range("B4:H105").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileN.SelectedItems(1) & "\" & cll.Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
          End If
        End If
      Next cll
    End With
    MsgBox ("Completed publishing PDFs")
  Else
    MsgBox ("You have cancelled the folder selection")
  End If
End With
End Sub

[a_chaitanya]

It worked!

Time saved with your help. Thanks a lot.