vba code to print off the files stored in listbox

[aman]

Hi Guys

I have a listbox which stores the file path in column (0), I want to write down the vba code so that when a button is clicked then it prints off all the files stored in the listbox.

As the file path gets stored in a table , so for one recordID there might be multiple files in that table. so what should be the code to print off the files against that particular record?

Any help will be much appreciated.

Thanks

 

[arnelgp]

am i correct, Print the content
of all the files in the listbox?

 

[aman]

yes, it could be any file format e.g .pdf, .doc , .xlsx ?

 

[arnelgp]

okey, copy this in Standard Module in VBE:

Public Sub PrintAnyFile(ByVal strPathFile As String)
    Dim TargetFolder
    Dim FileName
    Dim ObjShell As Object
    Dim ObjFolder As Object
    Dim ObjItem As Object
    Dim ColItems As Object
    
    If InStrRev(strPathFile, "\") <> 0 Then
        TargetFolder = Left(strPathFile, InStrRev(strPathFile, "\"))
        FileName = Right(strPathFile, Len(strPathFile) - Len(TargetFolder))
    End If
    Set ObjShell = CreateObject("Shell.Application")
    Set ObjFolder = ObjShell.NameSpace(TargetFolder)
    Set ColItems = ObjFolder.Items
    For Each ObjItem In ColItems
        If ObjItem.Name = FileName Then
            ObjItem.InvokeVerbEx ("Print")
            Exit For
        End If
    Next
    Set ObjItem = Nothing
    Set ColItems = Nothing
    Set ObjFolder = Nothing
    Set ObjShell = Nothing
End Sub

to print all items in the listbox, regardless if
selected or not:

dim i as integer
for i = 0 to me.list0.listcount-1
PrintAnyFile me.list.ItemData(i)
next



if you only want to print the item that is
selected in the listbox:

dim var as variant
for each var in me.list0.itemsselected
PrintAnyFile me.list0.itemdata(var)
next

 

[aman]

Arnelgp, it doesn't print off anything. The file stored in column(0) is in the following format and we need to print off "Appointment Clarity.xls" file.

C:\Access Databases\Project_1\MDB\Appointment clarity.xls

 

[arnelgp]

am i correct, Print the content
of all the files in the listbox?

 

[arnelgp]

are all your files in "c:\access databases\project_1\mdb" ?

you should pass the filename together with correct path and
extension of the file. access will not guess which folder
or file extension it will print.

for i = 1 to me.lst0.ListCount-1
PrintAnyFile "C:\Access Databases\Project_1\MDB" & Me.lst.ItemData(i)
next


***
if there are varied paths you must include them in the
list as second column

 

[aman]

Column(0) in the listbox stores filepath and file name as well. so if I write following line of code then it gives me "c:\access databases\project_1\mdb\abc.xls". It doesn't print anything .

MsgBox Me.lstDocs.ItemData(i)
PrintAnyFile Me.lstDocs.ItemData(i)

 

[arnelgp]

If you can uplaod a sample db. With your form id like to take a look

 

[aman]

sorry I can't upload database because of security restrictions in my company.

Me.lstDocs.ItemData(I) gives me both filepath and filename but why its not printing anything . Does this code work for you?

 

[arnelgp]

It does work, just tested it before posting.

 

[aman]

Really weird , why its not working for me then?

when I try to display the "Target Folder name" then it says C:\Access Databases\Project_1\MDB (which is right)

And when I try to display the "File name" then it says Appointment clarity.xls (which is right as well)

Not sure whats going wrong

 

[isladogs]

Do you have a trailing backslash after the folder name?

This will work

C:\Access Databases\Project_1\MDB\Appointment clarity.xls

but this won't ...

C:\Access Databases\Project_1\MDBAppointment clarity.xls

 

[aman]

yes there is trailing backslash after the folder name

 

[aman]

I tried to print a document by manually selecting print button from the menu bar and it works fine. Not sure why the code doesn't print it off.

 

[aman]

I amended the code as below and it displays "Hello" message .

For Each ObjItem In ColItems
        If ObjItem.Name = FileName Then
            ObjItem.InvokeVerbEx ("Print")
            Exit For
            Else
            MsgBox "Hello"
            End If
      
    Next

 

[aman]

Also I just found out ObjItem.Name contains the file name without extention

anf Filename stores the name of the file with extenstion . I amended the code below to display what values do they display and that's why its not printing

For Each ObjItem In ColItems
    MsgBox "Object name" & ObjItem.Name
      MsgBox "FileName" & FileName
        If ObjItem.Name = FileName Then
            ObjItem.InvokeVerbEx ("Print")
            Exit For
            Else
            MsgBox "Hello"
            End If
      
    Next

 

[arnelgp]

Alsi tested it. If it is a folder it will have no extension. If you are debugging it keep on pressing f8 until it finds the match. Chk also if you have same filename without extension.

 

[aman]

I have changed the code as below and it started printing

Public Sub PrintAnyFile(ByVal strPathFile As String)
    Dim TargetFolder
    Dim FileName
    Dim ObjShell As Object
    Dim ObjFolder As Object
    Dim ObjItem As Object
    Dim ColItems As Object
    
    If InStrRev(strPathFile, "\") <> 0 Then
        TargetFolder = Left(strPathFile, InStrRev(strPathFile, "\"))
        FileName = Right(strPathFile, Len(strPathFile) - Len(TargetFolder))
    End If
    Set ObjShell = CreateObject("Shell.Application")
    Set ObjFolder = ObjShell.NameSpace(TargetFolder)
    Set ColItems = ObjFolder.Items
    For Each ObjItem In ColItems
       
            ObjItem.InvokeVerbEx ("Print")
            Exit For
       
    Next
    Set ObjItem = Nothing
    Set ColItems = Nothing
    Set ObjFolder = Nothing
    Set ObjShell = Nothing
End Sub

 

[aman]

Another functionality I want to add with this is print off a word document that will display "Form Name", "Unique Reference " , "File names attached". This word document needs to be printed before the files stored in the listbox get printed .

Any suggestions are welcome .

Thanks