Microsoft Excel - I am trying to set up a macro (vba in excel ) which saves 1 of 100 hyperlinks to pdf

Asked By Stephen P on 19-Oct-12 07:16 PM
I am trying to set up a macro which saves 1 out of 100 maybe more or less as pdf files.
Right now I am tying to get it so that the "save as" dialogue box does not appear.
Any help with this would be appreciated.

here is the code as it stands now

Sub Test()
Dim HL As Excel.Hyperlink
Dim ws As Excel.Worksheet
Dim strURL As String
Dim strText As String
Dim FSO As Object
Dim ts As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

Set ws = ActiveSheet
For Each HL In ws.Hyperlinks
    strURL = HL.Address
    ie.navigate strURL
    Do Until ie.readystate <> 4
      DoEvents
      For c = 1 To 5000
      Next c
    Loop
    strText = ie.document.body.innertext
    Set ts = FSO.Createtextfile("C:\" & HL.Name & ".txt")
    ts.write strText
    ts.Close
Next HL
End Sub

Harry Boughen replied to Stephen P on 20-Oct-12 02:12 AM
Hi Stephen,

I think

Application.DisplayAlerts = False

Is the answer,  But be sure to set it back to True at the end of your macro.

Regards

Harry
Stephen P replied to Harry Boughen on 22-Oct-12 02:08 PM
it still comes up with the save as dialogue box, and it seems after the first it gets an error, which seems to be for application.internetexporer2, so it seems to be trying to open another instance of ie, then giving me an error
Harry Boughen replied to Stephen P on 22-Oct-12 03:38 PM
Hello Stephen,

What you are doing is a bit outside my area of knowledge so I have no idea what the error could be.  However, I notice that you just Close the file that you are writing to.  Is it possible that that is where the problem lies?

I assume you have stepped through the macro and have pinpointed on exactly which step and cycle the breakdown occurs.  If you can give that information it is likely to help somebody to solve the problem.

Regards

Harry
Stephen P replied to Harry Boughen on 22-Oct-12 04:00 PM
this is where the error is highlighted.

strText = ie.document.body.innertext
Harry Boughen replied to Stephen P on 22-Oct-12 05:42 PM
Hello Stephen,

Not sure if this will help but have a look at the code towards the bottom of this blog.  Maybe you can hack this to do what you want.

http://www.ozgrid.com/forum/showthread.php?t=42061

Regards

Harry

PS.  One thing I don't understand is your reference to pdf but the file that you seem to be writing to is txt.

H
Stephen P replied to Harry Boughen on 23-Oct-12 06:28 PM
I could only find code to write the file as txt, yet the file should be saved as pdf, I have another where file should be saved as xls or csv, but I think txt will work fine for that.
Harry Boughen replied to Stephen P on 23-Oct-12 06:41 PM
Hello Stephen,

I wonder whether that is where the problem lies.  I doubt that even if the function can select pdf content from a website that it can save it as a text file or even represent it as a text string (which seems to be the step that you are failing on).

Regards

Harry.
Stephen P replied to Harry Boughen on 23-Oct-12 06:43 PM
I'll post my question again without the code and see what I can get as a response.
Harry Boughen replied to Stephen P on 23-Oct-12 07:53 PM
Hi Stephen,

Heres another link via a link that might help you.

http://stackoverflow.com/questions/10400795/vba-internet-explorer-automation-how-to-select-open-when-downloading-a-file

Didn't want to mess up your new post.

I haven't read this so it's up to you.

Regards

Harry