Excel Macro to Open and Print Other Spreadsheets

[shaqattack1992-newsgroups]

Hello Everyone,

I have a spreadsheet that containts 2 columns-Part Number and Drawing
Location. The sheet looks something like this:

---A------------------------B-------------------------
Part#-----------Drawing Location
Part 1----------c:\drawings\part1.xls
Part 2----------c:\drawings\part2.xls
Part 3----------c:\drawings\part3.xls

Is there a way to make a macro that will look at the cell containing
the file path, open that Excel file, print the workbook, close the
file, move to the next cell down, open/print/close...etc until it
reaches a blank cell?

I'm very new to macros, so any help would be greatly appreciated.

Thank You,

-Chad

 

[Don Guillett]

try

assuming c:\yourfolder\workbookname.xls

Sub Printworkbooks()

for each wb in [yourlist]
Set wb = Workbooks.Open("C:\yourfolder" & wb & ".xls")
wb.PrintOut
wb.Close SaveChanges:=False
End Sub

 

[shaqattack1992-newsgroups]

Thanks for the response. Sorry, I'm really new to this.

I copied

Sub Printworkbooks()

Set wb = Workbooks.Open("C:\drawings\" & wb & ".xls")
wb.PrintOut
wb.Close SaveChanges:=False

End Sub

I want to print the list of files starting in cell b2 on down. I have
the file path stored in b2 down. How do I point to cell B2 and
continue going down if the files are in C:\Drawings\. Do I just put
the filename in the Part Drawing cell B2?

However, I don't want to print all the files in that folder-just the
ones that are in column b2 (which will change). Any help would be
appreciated.

-Chad

 

[Don Guillett]

try this where b2 on down has the name(s) of the file

firstfile
secondfile

Sub Printworkbooks()
lr=cells(rows.count,"b").end(xlup).row
for each wb in range("b2:b" & lr)
Set wb = Workbooks.Open("C:\drawings" & wb & ".xls")
wb.PrintOut
wb.Close SaveChanges:=False
End Sub

 

[Dave Peterson]

I think I'd use separate variables:

Sub Printworkbooks()
dim LR as long
dim WB as workbook
dim WBCell as range

lr=cells(rows.count,"b").end(xlup).row

for each wbCell in range("b2:b" & lr)
Set wb = Workbooks.Open("C:\drawings\" & wbCell.value & ".xls")
wb.PrintOut
wb.Close SaveChanges:=False
End Sub

 

[Dave Peterson]

Oops. There isn't a "next wbcell" line...

Option Explicit

Sub Printworkbooks()
Dim LR As Long
Dim WB As Workbook
Dim WBCell As Range

LR = Cells(Rows.Count, "b").End(xlUp).Row

For Each WBCell In Range("b2:b" & LR)
Set WB = Workbooks.Open("C:\drawings\" & WBCell.Value & ".xls")
WB.PrintOut
WB.Close SaveChanges:=False
Next WBCell
End Sub

 

[Don Guillett]

You're right. You're right

 

[shaqattack1992-newsgroups]

That works great! I really appreciate the help.

Thanks,

-Chad