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?