PDA

View Full Version : [SOLVED] Loop Data validation list item and generate PDF for each



a_chaitanya
08-31-2013, 01:43 AM
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
08-31-2013, 02:55 AM
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-programming-vba-macros/951645-loop-data-validation-list-item-and-generate-pdf-for-each.html
http://www.ozgrid.com/forum/showthread.php?t=182271&p=682113#post682113

a_chaitanya
08-31-2013, 03:27 AM
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
08-31-2013, 03:47 AM
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
08-31-2013, 04:24 AM
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
08-31-2013, 04:43 AM
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
08-31-2013, 06:21 AM
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 Longis in the Declarations area of the code-module

a_chaitanya
09-02-2013, 06:16 AM
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
09-02-2013, 12:49 PM
I suspect you want to change this bit to:
Filename:=.SelectedItems(1) & "\" & cll.Value & ".pdf"
in both places.

a_chaitanya
09-02-2013, 10:43 PM
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
09-03-2013, 07:06 AM
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
09-03-2013, 10:27 PM
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
09-04-2013, 06:04 AM
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
09-04-2013, 09:58 PM
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
09-04-2013, 10:10 PM
It worked!

Time saved with your help. Thanks a lot.