By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Print all files in folder Macro

P: 63
I am trying to write a macro that will print all files in a particular folder. The folder will always have the same name - but the number of files in that folder may differ. I simply want a macro that will open and print all the files in the specified folder (D:\TEST) and then close each file down. I've got so far but I get a subscript out of range on the line with a * next to it. I am no expert on this so it may be easier to rewrite than fix this!

Any help would be greatly appreciated!

Sub BatchMacro()
Dim oDoc As Workbook
Dim sPath As String
Dim sFileSpec As String
Dim sFile As String
Dim sFileList() As String
Dim i As Integer

' Get Folder
'sPath = InputBox("Enter Word Doc Folder to Process", "Batch", "")
sPath = "D:/TEST"
' Clean up

' Add Word extention
sFileSpec = sPath & "*.xls"

' Get All filenames in the Folder
sFile = Dir$(sFileSpec)

i = -1
Do Until sFile = ""
i = i + 1
ReDim Preserve sFileList(i) As String
sFileList(i) = sFile
sFile = Dir$

' Process each file
For i = 0 To UBound(sFileList)* problem line
Set oDoc = Workbooks.Open(sPath & sFileList(i))

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Call oDoc.Close(True)
Next i

' Release dynamic array
Erase sFileList

End Sub
Jul 3 '09 #1
Share this Question
Share on Google+
1 Reply

P: 63
Sorted it! I had forgotten to put the / at the end of the folder path!!!!!!!!!
Jul 3 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.