Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4551
  • Last Modified:

VBA Open Hyperlink and save as PDF

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
Dawn7930
Asked:
Dawn7930
  • 7
  • 4
  • 4
  • +2
2 Solutions
 
karunamoorthyCommented:
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
 
Dawn7930Author Commented:
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
 
Dawn7930Author Commented:
Currently I click the hyperlink - File - Print - Select Printer PDF Creator - Remove Hyperlink path - Save   Works great BUT not for 8000 hyperlinks.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vadim RappCommented:
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
 
David LeeCommented:
Hi, Dawn7930.

Does each hyperlink point to a .jpg or a web page?
0
 
Nico BontenbalCommented:
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
 
Dawn7930Author Commented:
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
 
David LeeCommented:
Hi, Dawn7930.

How about the question I asked?  Does each hyperlink point to a .jpg or a web page?
0
 
Dawn7930Author Commented:
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
 
Vadim RappCommented:
Did you look at the sample code of pdfcreator that I suggested?
0
 
David LeeCommented:
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
 
Vadim RappCommented:
@BlueDevilFan, he needs to print each of those linked pages to pdf.
0
 
Dawn7930Author Commented:
@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
 
David LeeCommented:
@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
 
Dawn7930Author Commented:
@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
 
Dawn7930Author Commented:
@vadimrapp1
I finally found the code you suggested.    Looks like it will work once I decipher it.  Thank you!
0
 
Vadim RappCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now