Macro to Loop Through Hyperlinks and Print the Files

[JodieM]

Hi I have done a search on Groups and can't find a good answer to my
query.
I have a list of hyperlinks in one workbook that contains hyperlinks
to other workbooks.
I want to loop through each of the hyperlinks, follow the hyperlink,
to open the file, then print the file and close the file, then move
onto the next hyperlink.
Can someone help me with the basis for this code?

I think the problem comes from this line of code
ActiveWorkbook.FollowHyperlink ("MyLink.xls")

From that point, I've lost control over the file so I can't do

something like
Workbook.PrintOut
Workbook.Close SaveChanges:=False
ie the code
dim wb as Workbook
set wb = ActiveWorkbook.FollowHyperlink ("MyLink.xls")
wb.PrintOut
Just doesn't work because the Follow Hyperlink can't be a type of
Workbook...

Can anyone help with this please?

 

[NickHK]

If these are Excel files, you can use

Dim WB as workbook
set wb=workbooks.open(PathToMyLink.xls)

with wb
.printout
.close false
end with

Put that in your loop of filenames

NickHK

 

[JodieM]

If these are Excel files, you can use

Dim WB as workbook
set wb=workbooks.open(PathToMyLink.xls)

with wb
.printout
.close false
end with

Put that in your loop of filenames

NickHK








- Show quoted text -

Thanks Nick, I was just about to post that I'd worked that much out...
is there no way to do this with a follow hyperlink then? I'm assuming
not.

The thing is, I've got either Word Documents, PDF files or Excel files
as my hyperlinks, so I will have to work out which type of file it is
and then write a bit of code to handle the printing of each type of
file... unless there is an easier way.

 

[NickHK]

Jodie,
In that case look at using the ShellExceute/ShellExceuteEx API, with the
"print" verb. Then the default app for that file type will do the printing
and you do not have to deal with it yourself.
http://vbnet.mvps.org/code/shell/shellexecute.htm

NickHK