Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Print seelcted files from folder in a list box

  Asked By: Kent    Date: Feb 25    Category: MS Office    Views: 731
  

I am using the following to populate the file names in a multiselect
listbox:

Sub newlistbox()
Dim i As Integer, fNm As String, fLg As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
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
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
'MsgBox "Form Loaded"
End Sub

Now some advancement to the code is required.

The list box is a multi select list box.
I want to attach a button named "Print" to the user form where the
list box is attached. On clicking the "Print" button, it will open
the selected name files in the specified folder and then print each
sheet.
If all the items are selected, then print all the files
If some of the files are selectd then print those selected files
only.
If nothing is selcted then nothing will be printed.

How can this be done?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Jonah Brown     Answered On: Feb 25

I assume that your listbox has a MultiSelect set to 1 or 2 (multi or extended
instead of single).
assign a macro to your print  button.
use something like:
For i = 0 To userform1.listbox1.listcount
If userform1.ListBox1.Selected(i) = True Then
' Record a macro that calls up a excel document, then print the entire
workbook.
' use this macro as a template to insert here and substitute the selected  file
name from the listbox
End If
Next i

do you need more explicit ideas?
hope this helps.

 
Answer #2    Answered By: Boyce Fischer     Answered On: Feb 25

This is the code  that I am using to print  ll files  in a folder:



Sub PrintFilesFromListbox()

Dim path  As String

Dim FileName As String

Dim Wkb As Workbook

Dim WS As Worksheet



Application.EnableEvents = False

Application.ScreenUpdating = False

PATH = "D:\Form16_AY_2008 - 2009\"

FileName = Dir(PATH & "\*.xls", vbNormal)

Do Until FileName = ""

Set Wkb = Workbooks.Open(FileName:=PATH & "\" & FileName)

Application.ActivePrinter = "CutePDF Writer on CPW2:"

Wkb.PrintOut Copies:=1, Collate:=True

Wkb.Close False

FileName = Dir()

Loop

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

This is working well.

This is the code that I am using to add all the files name in the folder
into a listbox:



Sub newlistbox()

Dim i As Integer, fNm As String, fLg As Integer



Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.EnableEvents = False

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

Application.ScreenUpdating = True

Application.DisplayAlerts = True

Application.EnableEvents = True

'MsgBox "Form Loaded"

End Sub



Now I want to Print those files which are selected  in the Listbox.
If all the files are selected print all the files.
If some files are selected, then print those selected files only.
If nothing is selected then nothing to be printed.

 
Answer #3    Answered By: Stacy Cunningham     Answered 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!

 
Answer #4    Answered By: Jimmie Ramirez     Answered On: Feb 25

Thank you for the code  and the suggestion. I would keep the same in mind in
future projects.

 
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: