Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA  Open Hyperlink and save as PDF

Posted on 2013-01-17
18
Medium Priority
?
3,907 Views
Last Modified: 2013-02-01
I have a list of 8,000 different links to products on our website that I need to save off as PDF files.  I am somewhat of a newbie to VBA.   Is it possible to write a VBA program that opens a hyperlink in Excel, then saves it to a specific folder as a PDF, naming it using the data after the last forward slash in the Hyperlink http://www.hellermanntyton.us/media/products/2545_lg.jpg.  
Just wondering if this is possible before I start, any tips would be greatly appreciated.
0
Comment
Question by:Dawn7930
  • 7
  • 4
  • 4
  • +2
17 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 38787783
I think you download product picture and then convert the picture into pdf file. If it is so then you can use some tools like internet download manager and give the hyperlinks as text file and will download all the hyperlinks available in the text file you given as input to Internet Download Manager(IDM). Once you got the product pictures then you can convert each Product pictures into pdf files using tools to convert jpg to pdf files.

I hope you understands this. if you need further assistance pl post your comments here!
0
 

Author Comment

by:Dawn7930
ID: 38801247
I download IDM.  But when I run it on the hyperlink I get a  different result see attached.  I need to save the hyperlink as a webpage and then I have a VBA program that will convert it to a PDF.  Thank you for your help!
Results.docx
0
 

Author Comment

by:Dawn7930
ID: 38801279
Currently I click the hyperlink - File - Print - Select Printer PDF Creator - Remove Hyperlink path - Save   Works great BUT not for 8000 hyperlinks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 2000 total points
ID: 38808195
Open installation folder of PDFCreator, go to subfolder named COM, and find the sample script called MsOffice2PDF. Use it as the base for your project.

I have to note that it's somewhat odd to use excel for opening hyperlinks with jpeg files.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38808245
Hi, Dawn7930.

Does each hyperlink point to a .jpg or a web page?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38808799
You could open the hyperlink in Word and then save as PDF. Try this manually first because Word won't display the page exactly as a browser. But it might be sufficient. If it is, let me know and I'll show you how to automate it from Excel.
0
 

Author Comment

by:Dawn7930
ID: 38836266
Thanks for your help but it did not work.  I need to save off as a PDF just as it appears on the web.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38836385
Hi, Dawn7930.

How about the question I asked?  Does each hyperlink point to a .jpg or a web page?
0
 

Author Comment

by:Dawn7930
ID: 38836420
To a web page,  http://www.hellermanntyton.us/productDetail.aspx?lon=PC6GRN14SC
I then need to save each page off to a PDF.  If there is another way to do this for 8,000 different links I am open to suggetions.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38837012
Did you look at the sample code of pdfcreator that I suggested?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38837085
Dawn7390,

If I understand correctly all you need is a script that will save the HTML pages to disk since you already have a script that will print them.  This script will read the contents of an Excel worksheet, downloading the URL specified on each row to a file.  The script assumes that the worksheet is laid out with one item per row with the URL in column A.

I've attached a sample workbook that includes the code.  All you need to do is add the URLs and modify the path the output files will go to.

Sub SavePageToDisk()
    Dim objHTTP As Object, _
        objFSO As Object, _
        objFil As Object, _
        lngRow As Long, _
        strURL As String, _
        strPath As String, _
        strFilename As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'On the next line change the file path as desired.  Make sure the path ends with \
    strPath = "c:\Users\David\Documents\TestArea\"
    For lngRow = 2 To Application.ActiveSheet.UsedRange.Rows.Count
        strFilename = strPath & lngRow & ".htm"
        strURL = Application.ActiveSheet.Cells(lngRow, 1).Value
        objHTTP.Open "GET", strURL, False
        objHTTP.send ""
        Set objFil = objFSO.CreateTextFile(strFilename)
        objFil.Write objHTTP.responseText
        objFil.Close
    Next
    Set objHTTP = Nothing
    Set objFSO = Nothing
    Set objFil = Nothing
    MsgBox "All done!"
End Sub

Open in new window

Dawn7930.xlsm
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38837136
@BlueDevilFan, he needs to print each of those linked pages to pdf.
0
 

Author Comment

by:Dawn7930
ID: 38837272
@BlueDevil Fan - Thanks for the macro but I have that part of it.  
1) I have the macro reading the list
2) opening the hyperlink
3) MISSING
4) Save it to a specific folder as a PDF, naming it using the data after the last forward slash in the Hyperlink  

3) I need it to do: File - Print - Select Printer PDF Creator - "click" print -
Change path in the File box (I can create the path and name but don't know how to get it in the box for PDF creator)
Then "click" save
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38837590
@Dawn7930,

I guess I'm lost.  You said " I need to save the hyperlink as a webpage and then I have a VBA program that will convert it to a PDF."  That sounds like you have the program for converting the downloaded files to PDF format and just need the part that downloads them.  Are you now saying that you already have the code for downloading the files and just need the part that prints them?
0
 

Author Comment

by:Dawn7930
ID: 38839560
@BlueDevil Fan
Sorry for the confusion.  I don't have to physically "print" them.  The only way I found that works is to use Printer PDF Creator which is accessed through file -print - Select Printer PDF Creator - Print.  You then can Save it as a PDF.  I have attached screen shots
The code I need is:,
in Explorer selects File - Print
then chooses the printer PDF Creator
select PRINT from the Print dialog box
access the Document Title field from the PDF Creator dialog box
(I have code to create the file name and location)
select Save from the PDF Creator dialog box
select Save from the Save As dialog box
Screen-Shots.docx
0
 

Author Comment

by:Dawn7930
ID: 38839599
@vadimrapp1
I finally found the code you suggested.    Looks like it will work once I decipher it.  Thank you!
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 38839657
Glad you found it. Whenever you want to drive some piece of software programmatically, you always look for the API, whether it's Excel, PDFCreator, Internet Explorer, or any other application. If it's possible with the software in question, the set of tools to do that will be always called either "API", or "SDK". If there's no API or SDK, then you either have to do it manually, as you described; or employ an application that can find windows and send keystrokes (Automate, AutoIT, and more); or find another product that can do what you want and has an API.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question