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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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,344
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,344
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,011
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top