Solved

VBA  Open Hyperlink and save as PDF

Posted on 2013-01-17
18
3,176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +2
18 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 500 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 500 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

696 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