VBA - open a filename, print, close, open the next

jsolomon

Board Regular
Joined
Mar 25, 2005
Messages
109
Hi, I have about 150 filenames (all of which are in the same directory) in an excel column (a1:a150 we'll say). I would like vba to read the filename from the cell starting with a1, open that filename, print it, close it then open the next one in the list (a2). I will probably have to use VBA. What is the best way to do this?

thanks

joe
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Joe

Check this link.

I know it's quite long but I hope you'll find it of interest.
 

jsolomon

Board Regular
Joined
Mar 25, 2005
Messages
109
Hi Norie,
Thanks for the link. Maybe you can help me understand the logic. The lines I do not understand are:

Code:
For I = 1 To .FoundFiles.Count
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 ws.Printout
            Next ws
            wb.Close
        Next I

thanks again

Joe
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Joe

That piece of code loops through all the files found using the FileSearch which are contained in FoundFiles.

It opens each file and sets wb as a reference to it.

Then it loops through each worksheet (ws) in the newly opened workbook and prints it out.

Finally the workbook is closed here.
Code:
wb.Close
 

jsolomon

Board Regular
Joined
Mar 25, 2005
Messages
109

ADVERTISEMENT

so is foundfiles a variable?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
No it's an object returned by FileSearch which contains the files that were found.
 

jsolomon

Board Regular
Joined
Mar 25, 2005
Messages
109

ADVERTISEMENT

I get a type mismatch at :
For I = 1 To Rng

I am still trying to leasr the syntax, as io think that is where my error is coming into play.

Here is my code:
Code:
Sub PrintAllWS()
Const MyFolder = "i:\sbiron\customers\comcast\"
Dim I As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim Rng As Range

Set Rng = ActiveSheet.Range("a1:a150")
       
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute

     For I = 1 To Rng
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 ws.PrintOut
            Next ws
            wb.Close
        Next I
    End With
     
End Sub

Thanks,

Joe
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Joe

The link I pointed you towards doesn't actually deal with getting the filenames from the range. Sorry about that.:oops:

It does however deal with printing all the worksheets of every workbook in a directory.

I thought that might have suited you rather than using the filenames from the worksheet.

Anyway, if you do want to use the filenames from the worksheet this is how you would do it.
Code:
Sub PrintAllWS() 
Const MyFolder = "i:\sbiron\customers\comcast\" 
Dim wb As Workbook 
Dim ws As Worksheet 
Dim Rng As Range 
dim c As Range

    Set Rng = ActiveSheet.Range("a1:a150") 
        
     For Each c In  Rng 
            Set wb = Workbooks.Open(c.Value) 
            For Each ws In wb.Worksheets 
                 ws.PrintOut 
            Next ws 
            wb.Close 
     Next c
      
End Sub

Note you might have to add the full path if you only have the filenames on the worksheet.
Code:
Set wb = Workbooks.Open(MyFolder & c.Value)
 

Dragon_SDC

Board Regular
Joined
Aug 17, 2002
Messages
56
jsolomon said:
I get a type mismatch at :
For I = 1 To Rng
Perhaps this is because I is declared as a long variable and Rng is an object, where as you are trying to use them together?
 

jsolomon

Board Regular
Joined
Mar 25, 2005
Messages
109
Hey,
The code worked, thank you so much. On the down side however, i am still having a little difficulty understanding the logic:

Code:
     For Each c In  Rng
            Set wb = Workbooks.Open(c.Value)
            For Each ws In wb.Worksheets
                 ws.PrintOut
            Next ws
            wb.Close
     Next c

The "For Each" stuff is what is throwing me off. Perhaps there is a thread with an explanation of such? Can the loop code used in these?

Thank you for your help,

Joe
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,639
Members
412,334
Latest member
ExcelForLifeDontHate
Top