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

[jsolomon]

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

 

[Norie]

Joe

Check this link.

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

 

[jsolomon]

Hi Norie,
Thanks for the link. Maybe you can help me understand the logic. The lines I do not understand are:

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]

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.

wb.Close

 

[jsolomon]

so is foundfiles a variable?

 

[Norie]

No it's an object returned by FileSearch which contains the files that were found.

 

[jsolomon]

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:

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]

Joe

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

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.

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.

Set wb = Workbooks.Open(MyFolder & c.Value)

 

[Dragon_SDC]

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]

Hey,
The code worked, thank you so much. On the down side however, i am still having a little difficulty understanding the logic:

     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