Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Kent Hamilton   on Feb 25 In MS Office Category.

  
Question Answered By: Stacy Cunningham   on Feb 25

OK... you've got most of the framework in place!
First of all,
I'd make your print  sub a FUNCTION, which
accepts the file  file name as input.
'---------------------------------------------------
Public Function PrintFiles(PrintName)
Dim fPATH As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False

fPATH = "D:\Form16_AY_ 2008 - 2009\"
Set Wkb = Workbooks.Open(FileName:=fPATH & PrintName)
Application.ActivePrinter = "CutePDF Writer on CPW2:"
Wkb.PrintOut Copies:=1, Collate:=True
Wkb.Close False

Application.EnableEvents = True
Application.ScreenUpdating = True
End Function

'---------------------------------------------------
Next, I moved your newlistbox sub into the Initialize event for the UserForm.
That way, the listbox is populated when the userform is opened:
'---------------------------------------------------
Private Sub UserForm_Initialize()
Dim i As Integer, fNm As String, fLg As Integer
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path  To suit
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "D:\Form16_AY_ 2008 - 2009\"
If .Execute > 0 Then 'Workbooks In folder
For i = 1 To .FoundFiles.Count ' Loop through all
fLg = InStrRev(.FoundFiles(i), "\")
fNm = Right(.FoundFiles(i), Len(.FoundFiles(i)) - fLg)
UserForm1.ListBox1.AddItem Replace(fNm, ".xls", "")
Next i
End If
End With
On Error GoTo 0
End Sub

'---------------------------------------------------
Next is the Print button. The click event should be:
'---------------------------------------------------
Private Sub CommandButton1_Click()
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) = True Then
stat = PrintFiles(UserForm1.ListBox1.List(i))
End If
Next i
Unload UserForm1
End Sub
'---------------------------------------------------
Now, as long as your Print sub is working the way you want it, then this should
work fine!
(It worked with my sample files)

Just a couple of cautionary notes (or advice)...
The names  PATH and FileName
are dangerous. Because they are "reserved words" used
by VBA in all manner of places. When you declare them
as variables, then have to use them as they were originally
intended, there can be unpredictable results.
I would suggest using something unique, like fPATH, or PrintName.
Also... I would declare fPATH as a global variable, then define it once
in the routine that OPENS the UserForm.
This serves two purposes... if you need to change the location of your files,
you only have to do it in one place, and if later you decide you want to
locate the path using a "browse" panel, you'll be all set for it!
Hope this helps!

Share: 

 

This Question has 3 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Print seelcted files from folder in a list box Or get search suggestion and latest updates.


Tagged: