VB to open file referenced in a cell

[mr_hiboy]

Hi, I have a cell that links to another file showing a a version number, in cell A1, as below:


='C:Foldersub folder[File1.xlsm]Prices'!$A$1


I also have 23 more of these linking to different files.


I want to write a bit of VB to open the File1.xlsm and print the Prices sheet, plus a Forecast sheet, close the file, then do the same for the next 23 files referenced in A2,A3 etc.


The file can change quite option, therefore need it to take the file reference from cell A1 and hard code it.


Any help would be great.


thanks

 

[Luke M]

How's this look?

[pre]

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

[/pre]