• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto save the same worksheet into different file names using macro (Excel 2010)

F

fred

Member
Hi,


I have a worksheet with an input sheet and an output sheet. On the output worksheet there is a list box displaying workers' names (e.g 100 names). By clicking on a different name it will display the info of that worker in a report format.


Currently, when I hi-light all the names in that listbox and hit the print button, it will automatically refresh the report one employee at a time and print out all 100 individual report to the printer.


But due to sensitivity of the individual reports, I now need save it as a PDF file and not send it to a printer.


In C15 on the output worksheet that is where the employee name is, say John Doe. How do I make macro to capture the info on this cell C15 and add a string of text behind it and save as pdf?


That means each pdf file would be saved as "John Doe - Performance Report April 2012.pdf" and the next person "Mary Doe - Performance Report April 2012.pdf", and the next one "David Copperfield - Performance Report April 2012.pdf", etc. There are 100 names and manually save each file and type in their name over and over again with a fixed text string should be


How do I use macro to capture the file names using macro?


SirJB7 was nice enough to help me started this with 3 macros and I have expanded the last one somewhat.


Private Sub lstPrinting_LostFocus()

Dim I As Integer, J As Integer

J = 0

With lstPrinting

For I = 0 To .ListCount - 1

If .Selected(I) = True Then J = J + 1

Next I

End With

Range("H3").Value = J

End Sub


Private Sub cmdPrintSelected_Click()

Dim I As Integer, J As Integer

With lstPrinting

For I = 0 To .ListCount - 1

If .Selected(I) = True Then

Range("C3").Value = I + 1

ActiveSheet.PrintOut

End If

Next I

End With

End Sub


Private Sub cmdUpdateZSelected_Click()

Dim I As Integer, J As Integer

With lstPrinting

' Quarter 2 Goal

For I = 0 To .ListCount - 1

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 13).Value = Range("O3").Value

End If

' Quarter 3 Goal

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 21).Value = Range("P3").Value

End If

' Quarter 4 Goal

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 29).Value = Range("Q3").Value

End If

' Quarter 1 Net Payout

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 7).Value = Range("N4").Value

End If

' Quarter 2 Net Payout

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 15).Value = Range("O4").Value

End If

' Quarter 3 Net Payout

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 23).Value = Range("P4").Value

End If

' Quarter 4 Net Payout

If .Selected(I) = True Then

Range("C3").Value = I + 1

Worksheets("Summary").Cells(I + 6, 31).Value = Range("Q4").Value

End If

Next I

End With

End Sub


My guess is that I need to expand the last macro so that as the "summary" worksheet is being refreshed a pdf file can be saved as well.


Thanks for the help.
 
Hi, fred!


I'd surely made something wrong if I looked like so nice... maybe I had fever or was drunk. Let me read a little what you're trying to achieve and I'm coming back.


Regards!


EDIT:


I remembered an old post from Luke M about saving in PDF format. So, why reinvent the wheel?


This is the main code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"XXX" & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


Borrowed without permission, obviously, from:

http://chandoo.org/forums/topic/how-do-i-use-this-macro-correctly#post-11494


Let me see what can I arrange with that... and I'll be coming back again.
 
Hi, fred!


Yes, yes, it's me again... Don't you remember I was coming back?


I opened your last time version workbook, and I think you should modify the procedure cmdPrintSelected_Click as follows:


-----

[pre]
Code: 
Private Sub cmdPrintSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3").Value = I + 1
'ActiveSheet.PrintOut
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & Application.PathSeparator & _
.List(I) & " - Performance Report " & Range("G8").Value & _
".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next I
End With
End Sub
[/pre]
-----


That's replacing the previous print instruction for the new export statement.


The filename is built with the name taken from the drop down list box for print selection and the date from cell G8 (it was empty, and so close, that I couldn't resist the temptation of using it). You mentioned C15 cell, but there I have "Title:" value, so please update filename as needed. I have the name in C14 cell, but's the same as ".List(I)" (unquoted) control value.


I hope it's what you were looking for.


Two considerations:

a) the .pdf files are written in the same folder as the main file, so if you want to store them in another (which I'd do) you have to modify the "Filename:=" part or the new sentence and if not sure if it might exist or not, then create it firstly

b) the exportation replaces files with same name without asking, so if you're going to have more than one version for same period (text in G8), be careful because the older will be overwritten.


Good luck, just advise if any issue, and till next modification.


Regards!


PS: updated file reuploaded at same previous link:

http://dl.dropbox.com/u/60558749/How%20to%20write%20a%20macro%20to%20print%20data%20pertaining%20to%20a%20list%20box_%20-%20Testing%20%28for%20fred%20at%20chandoo.org%29.xlsm
 
Hi, fred!

Did you happen to download the file or update the print procedure?

Regards!
 
Hello SirJB7,


Sorry I didn't reply soon enough. Super busy at work lately. I'm going to give it a try on Monday. I don't have MS2010 on my personal PC, only at work. rest assure i'll do it on monday or tuesday.


the employee performance report would be conducted quarterly or annually. And I would prefer saving the PDF files in differnt folders so that I have a history to go back and read them. You know, track how an employee's performance is tracking. So all quarter 1 performance reports would go to one folder. When the next quarter is over, I'll create a new folder, run the reports with new data and save them in a new folder.


Thank you very much.
 
Hi, fred!

Give it a try and get back with your comments.

About Excel versions, I don't know if that will work on 2003 (so many years I don't work on it) but I think that it'll do fine for 2007 & 2010. Maybe for 2003, but I can't assure it to you.

Regards!
 
WOW! Works like a charm! Thank you very much, SirJB7. I only made tiny alteration on the coding to fit the real file. My jaw is on the floor when I see how it works. All the names hi-lighted will have a PDF file independent of one another. This will help not just me but every readers here on Chandoo.org so much!


I just have one question remaining. I read the code. How do you capture the names without showing the cell location in VBA? I don't understand that part.


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & Application.PathSeparator & _

.List(I) & " - Q1 2012 Performance Report" & _

".pdf", _
 
Hi, fred!


As I wrote "You mentioned C15 cell, but there I have "Title:" value, so please update filename as needed. I have the name in C14 cell, but's the same as ".List(I)" (unquoted) control value.", I found that you've made slight changes to the worksheet structure (name from C14 to C15, so I decided to take it from the drop down list box control selected items.


How did I do that? Well, this control has the property ListIndex which points to the selected item, to which you may refer as ControlName.List(ListIndex). Note that if you have MultiSelect enabled you won't have neither multiple ListIndex values nor an array of them.


Here's when another property Selected() enters the game. Using "With lstPrinting" in the statement ".Selected(i)" I'm referring to lstPrinting.Selected(I) which points to an array of boolean values that indicate if item I has been selected or not. Testing for true with the If I can use the lstPrinting.List(I) to point to each selected name (each value of I would represent each ListIndex property if VBA would support an array of it, but's clear for explaining).


So after this introduction, the summary is: lstPrinting.List(I) gives the name regardless cell location, with I pointing to lstPrinting.Selected(I) values equal True.


If I didn't explain myself clearly, please come back again.


Regards!
 
You must log in or register to reply here.
Back
Top