VBA for printing files listed in excel

[leemcder]

Hi, I'm hoping someone could kindly help me with this, I want to be able to create a list of file names in excel and then be able to print the files listed without opening each file. All of the files (.XLS) format will be saved in one location, so I want a VBA that will look in a specific location and print the file names I have listed in an excel spreadsheet. If possible I would also like the files that are not in the location to be highlighted or marked so I know which files are missing. Can this be done? Anyones help would be much appreciated

[gmayor]

An Excel file is a workbook which may comprise multiple sheets. It is not clear what you want to print, but the following should point the way
It prints the first sheet if the file exists or colours the cell yellow if it doesn't. The code assumes that the full path and filename is in column A of the active sheet. If only the name is there add the path into the code as shown in the commented lines. Similarly add the extension if that is not present as in the following commented lines

Sub PrintFiles()
Dim oFSO As Object
Dim lngLastRow As Long
Dim lngIndex As Long
Dim strFname As String
Dim xlSheet As Worksheet
Dim xlWB As Workbook
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set xlSheet = ActiveSheet
    With xlSheet
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For lngIndex = 1 To lngLastRow
            strFname = .Range("A" & lngIndex)
            If oFSO.FileExists(strFname) Then
            'If oFSO.FileExists("C:\Path\" & strFname) Then
            'If oFSO.FileExists("C:\Path\" & strFname & ".xls") Then
                Set xlWB = Workbooks.Open(strFname)
                'Set xlWB = Workbooks.Open("C:\Path\" & strFname) 
                'Set xlWB = Workbooks.Open("C:\Path\" & strFname& ".xls") 
                xlWB.Sheets(1).UsedRange.PrintOut
                xlWB.Close savechanges:=False
            Else
                .Range("A" & lngIndex).Interior.Color = &H80FFFF
            End If
        Next lngIndex
    End With
lbl_Exit:
    Set oFSO = Nothing
    Set xlSheet = Nothing
    Set xlWB = Nothing
    Exit Sub
End Sub

[leemcder]

Thank you so much, this is exactly what I needed. I did attempt my own but it would not work. Thank you for your help, I appreciate it.

[leemcder]

Hi, gmayor, thanks again for your help with this, I have an issue when the vba runs and prints, the document doesn't print correctly, it prints smaller than it does if I print it manually. It should fit an A4 sheet but for some reason it doesn't do this when the VBA runs. Do you have any idea why?