Excel Macro to Open and Print Other Spreadsheets

[[email protected]]

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

--
Don Guillett
SalesAid Software
[email protected]

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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


--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

Don Guillett wrote:
>
> 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
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

--

Dave Peterson

[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




Dave Peterson wrote:
>
> 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
>
> Don Guillett wrote:
> >
> > 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
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

[Don Guillett]

You're right. You're right

--
Don Guillett
SalesAid Software
[email protected]
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
> Dave Peterson wrote:
>>
>> 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
>>
>> Don Guillett wrote:
>> >
>> > 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
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > [email protected]
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>> > >
>>
>> --
>>
>> Dave Peterson
>
> --
>
> Dave Peterson

[[email protected]]

That works great! I really appreciate the help.

Thanks,

-Chad