software

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?

quot;jim9912quot; wrote:

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

On my PCOn my PCI 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 quot;Followquot; or quot;FollowHyperlinkquot;, then a
quot;SendKeysquot;, 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 quot;Excel 2003 VBA Programmer's Referencequot; 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 quot;hyperlink=quot;; ThisHyperlinkAddress
Next

End Sub
quot;jim9912quot; wrote:

gt; On my PC
gt;
gt;

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.

quot;Patricia Shannonquot; wrote:

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

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

5) Use quot;Print #quot; 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
quot;Line Input #quot; and print the files. You will also need quot;Open #quot; and quot;Close
#quot; statements.

quot;Patricia Shannonquot; wrote:

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

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.

quot;Patricia Shannonquot; wrote:

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