VBA to open file referenced in a cell

[mr_hiboy]

One for you Luke, from old forum that I could find on here.

This worked a treat.
How's this look?

Sub PrintBooks()
 
Dim c As Range
Dim myPath As String
Dim newBook As Workbook
Application.ScreenUpdating = False
For Each c In Range("A1:A23")
    myPath = c.Formula
    'Remove first bracket, = sign, and any single quotes
    myPath = Mid(WorksheetFunction.Substitute( _
            WorksheetFunction.Substitute(myPath, "[", ""), "'", ""), 2)
    myPath = Left(myPath, WorksheetFunction.Find("]", myPath) - 1)
    Set newBook = Workbooks.Open(myPath, False, True)
    newBook.Worksheets("Prices").PrintOut
    newBook.Worksheets("Forecast").PrintOut
    newBook.Close False
    Workbooks.Open (myPath)
Next c
Application.ScreenUpdating = True
End Sub

response

Also, there are a few empty rows and heading that i need to skip.
Can this line be updated to have multiple ranges?
For Each c In Range("C9:C44")
e.g.
For Each c In Range("C9:C20","C22:C24") or similar?
Cheers

 

[Luke M]

Sort of. We can define a variable like to hold all the ranges if you want

Set myRange = Union(Range("A2:B2"), Range("D4:E5"))
For Each c in myRange
'Etc

 

[mr_hiboy]

Luke, works a treat thanks.

One more thing. It doesn't close the file after printing it.

At the minute I have commented outthe following line as not needed yet, not sure if that make a difference newBook.Worksheets("Prices").PrintOut

If possible, I'd like to close it without saving (and having to press no to save every time

Cheers
Paul

 

[mr_hiboy]

This print all files no problem, but also copies them to
C:\Users\paulmclaughlin\Documents
Meaning all the links have also changed to this folder.

Can this be stopped?

 

[Luke M]

Hi Paul.

I don't have any idea about why the files are getting copied...there's nothing in the code that should be doing that. I admit it's been a little bit since I first wrote the original code, but this bit looks odd:

newBook.Close False
Workbooks.Open (myPath)

It says to close the file w/o saving (which is good), but then we open the file back up?! I can't remember why I would do that, so I recommend deleting the 2nd line.

 

[mr_hiboy]

That resolved both issues - cheers Luke...you never fail me