macro to print files from a list of links

I am trying to develop a macro to read a list of links from an excel
spreadsheet and print each of the files. The number of files in the
list may change.

ANy ideas?

Are these links to files on your PC on on the internet?

"jim9912" wrote:

> I am trying to develop a macro to read a list of links from an excel
> spreadsheet and print each of the files. The number of files in the
> list may change.
>
> ANy ideas?
>
>

On my PC

On my PC

I have included a macro that runs through the hyperlinks on the spreadsheet
and prints their addresses in the immediate window, to get you started

I haven't had occasion to do what you want to do, so the rest of this is
what I would try if I were doing it.

How to proceed from there depends on the type of files you're printing. If
they are Excel files, it should be easy. You have the Open, PrintOut, and
Close methods.

If you have a hyperlink base that you need to use, you might be able to get
it with BuiltInDocumentProperties. If worse comes to worse, you could
specify it in an input box (which allows for a default if appropriate), or
put it in a particular cell or named range in the worksheet itself.

1) You can use FileCopy or Copyfile to copy the files to a folder, then
print the files outside of Excel, from Window Explorer. You might already
know you can print a group of files from Windows Explorer.

2)You could modify this macro to do a "Follow" or "FollowHyperlink", then a
"SendKeys", sending the appriate keys to print and close the file. Eg, for a
Word document, Alt+P to print, Alt+F E to exit. See Help for specifics.

3) If that doesn't work, you can use hyperlink address to set up an
interaction with whatever a program (eg., Word) that can process your files
and has VBA capability. This is something I've never done, but it is shown
in "Excel 2003 VBA Programmer's Reference" by Paul T. Kimmel and other, and
I'm sure in other books.


Sub PrintHyperlinks()
' Created by Patricia Shannon April 2006

Dim ThisHyperlink As Hyperlink
Dim ThisHyperlinkAddress As String

For Each ThisHyperlink In ActiveSheet.Hyperlinks
ThisHyperlinkAddress = ThisHyperlink.Address
Debug.Print "hyperlink="; ThisHyperlinkAddress
Next

End Sub



"jim9912" wrote:

> On my PC
>
>

In case you need it, I did find the Hyperlink base in
BuiltInDocumentProperties.
It was Activeworkbook.BuiltInDocumentProperties(29)
I don't know if the item number would vary between Excel versions. I have
Excel 2003.

"Patricia Shannon" wrote:

> I have included a macro that runs through the hyperlinks on the spreadsheet
> and prints their addresses in the immediate window, to get you started
>
> I haven't had occasion to do what you want to do, so the rest of this is
> what I would try if I were doing it.
>
> How to proceed from there depends on the type of files you're printing. If
> they are Excel files, it should be easy. You have the Open, PrintOut, and
> Close methods.
>
> If you have a hyperlink base that you need to use, you might be able to get
> it with BuiltInDocumentProperties. If worse comes to worse, you could
> specify it in an input box (which allows for a default if appropriate), or
> put it in a particular cell or named range in the worksheet itself.
>
> 1) You can use FileCopy or Copyfile to copy the files to a folder, then
> print the files outside of Excel, from Window Explorer. You might already
> know you can print a group of files from Windows Explorer.
>
> 2)You could modify this macro to do a "Follow" or "FollowHyperlink", then a
> "SendKeys", sending the appriate keys to print and close the file. Eg, for a
> Word document, Alt+P to print, Alt+F E to exit. See Help for specifics.
>
> 3) If that doesn't work, you can use hyperlink address to set up an
> interaction with whatever a program (eg., Word) that can process your files
> and has VBA capability. This is something I've never done, but it is shown
> in "Excel 2003 VBA Programmer's Reference" by Paul T. Kimmel and other, and
> I'm sure in other books.
>
>
> Sub PrintHyperlinks()
> ' Created by Patricia Shannon April 2006
>
> Dim ThisHyperlink As Hyperlink
> Dim ThisHyperlinkAddress As String
>
> For Each ThisHyperlink In ActiveSheet.Hyperlinks
> ThisHyperlinkAddress = ThisHyperlink.Address
> Debug.Print "hyperlink="; ThisHyperlinkAddress
> Next
>
> End Sub
>
>
>
> "jim9912" wrote:
>
> > On my PC
> >
> >

Other possibilities:
4) Open both Excel and the program suitable for printing the files, eg.
Word or Notepad. From Excel, use "SendKeys" to switch to the other pgm, open
the file, print it, close it, and transfer control back to your Excel pgm.
Eg. in "SendKeys", Alt+Tab is "%{TAB}" , which switches to the other pgm.

5) Use "Print #" to write the filenames to a file. Then you would have to
have a macro in the other pgm to read the filenames from this file, with
"Line Input #" and print the files. You will also need "Open #" and "Close
#" statements.

"Patricia Shannon" wrote:

> In case you need it, I did find the Hyperlink base in
> BuiltInDocumentProperties.
> It was Activeworkbook.BuiltInDocumentProperties(29)
> I don't know if the item number would vary between Excel versions. I have
> Excel 2003.
>
> "Patricia Shannon" wrote:
>
> > I have included a macro that runs through the hyperlinks on the spreadsheet
> > and prints their addresses in the immediate window, to get you started
> >
> > I haven't had occasion to do what you want to do, so the rest of this is
> > what I would try if I were doing it.
> >
> > How to proceed from there depends on the type of files you're printing. If
> > they are Excel files, it should be easy. You have the Open, PrintOut, and
> > Close methods.
> >
> > If you have a hyperlink base that you need to use, you might be able to get
> > it with BuiltInDocumentProperties. If worse comes to worse, you could
> > specify it in an input box (which allows for a default if appropriate), or
> > put it in a particular cell or named range in the worksheet itself.
> >
> > 1) You can use FileCopy or Copyfile to copy the files to a folder, then
> > print the files outside of Excel, from Window Explorer. You might already
> > know you can print a group of files from Windows Explorer.
> >
> > 2)You could modify this macro to do a "Follow" or "FollowHyperlink", then a
> > "SendKeys", sending the appriate keys to print and close the file. Eg, for a
> > Word document, Alt+P to print, Alt+F E to exit. See Help for specifics.
> >
> > 3) If that doesn't work, you can use hyperlink address to set up an
> > interaction with whatever a program (eg., Word) that can process your files
> > and has VBA capability. This is something I've never done, but it is shown
> > in "Excel 2003 VBA Programmer's Reference" by Paul T. Kimmel and other, and
> > I'm sure in other books.
> >
> >
> > Sub PrintHyperlinks()
> > ' Created by Patricia Shannon April 2006
> >
> > Dim ThisHyperlink As Hyperlink
> > Dim ThisHyperlinkAddress As String
> >
> > For Each ThisHyperlink In ActiveSheet.Hyperlinks
> > ThisHyperlinkAddress = ThisHyperlink.Address
> > Debug.Print "hyperlink="; ThisHyperlinkAddress
> > Next
> >
> > End Sub
> >
> >
> >
> > "jim9912" wrote:
> >
> > > On my PC
> > >
> > >

In looking thru the msgs this morning, I discovered the Shell function, which
can execute another program, eg. Word. You could precede it with Sendkeys.

"Patricia Shannon" wrote:

> Other possibilities:
> 4) Open both Excel and the program suitable for printing the files, eg.
> Word or Notepad. From Excel, use "SendKeys" to switch to the other pgm, open
> the file, print it, close it, and transfer control back to your Excel pgm.
> Eg. in "SendKeys", Alt+Tab is "%{TAB}" , which switches to the other pgm.
>
> 5) Use "Print #" to write the filenames to a file. Then you would have to
> have a macro in the other pgm to read the filenames from this file, with
> "Line Input #" and print the files. You will also need "Open #" and "Close
> #" statements.
>
> "Patricia Shannon" wrote:
>
> > In case you need it, I did find the Hyperlink base in
> > BuiltInDocumentProperties.
> > It was Activeworkbook.BuiltInDocumentProperties(29)
> > I don't know if the item number would vary between Excel versions. I have
> > Excel 2003.
> >
> > "Patricia Shannon" wrote:
> >
> > > I have included a macro that runs through the hyperlinks on the spreadsheet
> > > and prints their addresses in the immediate window, to get you started
> > >
> > > I haven't had occasion to do what you want to do, so the rest of this is
> > > what I would try if I were doing it.
> > >
> > > How to proceed from there depends on the type of files you're printing. If
> > > they are Excel files, it should be easy. You have the Open, PrintOut, and
> > > Close methods.
> > >
> > > If you have a hyperlink base that you need to use, you might be able to get
> > > it with BuiltInDocumentProperties. If worse comes to worse, you could
> > > specify it in an input box (which allows for a default if appropriate), or
> > > put it in a particular cell or named range in the worksheet itself.
> > >
> > > 1) You can use FileCopy or Copyfile to copy the files to a folder, then
> > > print the files outside of Excel, from Window Explorer. You might already
> > > know you can print a group of files from Windows Explorer.
> > >
> > > 2)You could modify this macro to do a "Follow" or "FollowHyperlink", then a
> > > "SendKeys", sending the appriate keys to print and close the file. Eg, for a
> > > Word document, Alt+P to print, Alt+F E to exit. See Help for specifics.
> > >
> > > 3) If that doesn't work, you can use hyperlink address to set up an
> > > interaction with whatever a program (eg., Word) that can process your files
> > > and has VBA capability. This is something I've never done, but it is shown
> > > in "Excel 2003 VBA Programmer's Reference" by Paul T. Kimmel and other, and
> > > I'm sure in other books.
> > >
> > >
> > > Sub PrintHyperlinks()
> > > ' Created by Patricia Shannon April 2006
> > >
> > > Dim ThisHyperlink As Hyperlink
> > > Dim ThisHyperlinkAddress As String
> > >
> > > For Each ThisHyperlink In ActiveSheet.Hyperlinks
> > > ThisHyperlinkAddress = ThisHyperlink.Address
> > > Debug.Print "hyperlink="; ThisHyperlinkAddress
> > > Next
> > >
> > > End Sub
> > >
> > >
> > >
> > > "jim9912" wrote:
> > >
> > > > On my PC
> > > >
> > > >