Download from web site

[johnske]

Hi, this is what I have to sort out...

The following procedure takes me to a web page I've set up for this. On this web-page there are links to two bas modules called "WidgetV1_1" and "WidgetV1001".

I need XL to
1) inform me (message box) of the names of all the links on that web-page
2) allow me to select and download the selected file to the desktop.
(This is to eventually be done with .Visible = False)

Anyone?

TIA
John

Option Explicit

Sub BeamMeUpScotty()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
On Error GoTo 1
With ie
.navigate "http://www.geocities.com/johnske100/TestPage.html"
.Visible = True
End With
Set ie = Nothing
Exit Sub
1: MsgBox Err.Description
ie.Quit
Set ie = Nothing
End Sub

[Ken Puls]

Hi John,

A piece of the puzzle for you. Providing that your page is set up in tables, and you know where they start, try this code posted by TonyJollans here.

Sub retrievefromweb()
Dim objWeb As QueryTable
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.geocities.com/johnske100/TestPage.html", _
Destination:=Range("A1"))
With objWeb
.WebSelectionType = xlSpecifiedTables
    .WebTables = "1" ' Identify your HTML Table here
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
End Sub

You'll need a reference set to Microsoft Internet Controls, but that will pull your data into an Excel sheet. You could then maybe use something like this KB entry to make a list of all hyperlinks...

Hope it helps,

[johnske]

Hi Ken,

Thanx for that (and the link). As you can gather, I know virtually zero about web-related things, and every time I do a search with "downloads" as any part of the search criteria, I get a list of the many thousands of things on the net that are available to download

Anyway, now I got somewhere to start - will have a look at this soon and play around with it to see if I can get it doing what I want

Regards,
John

[johnske]

Hi John,

A piece of the puzzle for you. Providing that your page is set up in tables, and you know where they start, try this code posted by TonyJollans here. ...etc....You'll need a reference set to Microsoft Internet Controls, but that will pull your data into an Excel sheet. You could then maybe use something like this KB entry to make a list of all hyperlinks...

Hope it helps,

Ken, your comment above (in bold) gave me an idea ...I have yet to try it, but it should really be quite straight-forward.

Instead of tables, we can have a workbook posted on the net and this contains the modules (or data) we may need to import.

In our book on the desktop, we use the macro I gave above to access the workbook on the net (but with the path changed to the books URL of course).

In this book posted on the net, a workbook_open macro starts a search for the required module (or data) and if the module exists, it's either exported to the desktop (or where-ever) or copied/imported into the book we're working from...

We're really only moving things from one book to another then...Sounds quite simple (but will it work in practice? - to be tested)

[Ken Puls]

Hi John,

I know a little of web related things, but I'm not sure if your idea would/wouldn't work. I'm curious to know if you could actually fire a workbook open event by opening a file directly from the web.

I assumed that you're probably trying to keep a list of versions and modules to download or something...

My thoughts were to use the code I gave you to pull a table into Excel, which maybe had the list of Version numbers and corresponding links. As I say, as long as it's set up in a table, (which yours is,) that part should be easy.

With that in Excel, you could easily code your stuff to figure what versions and links were required, and I think you could download them from there. I've never used Excel to download a file from the web to a specified location (yet), but can't see why it couldn't be done.

I'm thinking that this could be the beginnings of a great KB entry though!

[johnske]

Hi John,

I know a little of web related things, but I'm not sure if your idea would/wouldn't work. I'm curious to know if you could actually fire a workbook open event by opening a file directly from the web.

I assumed that you're probably trying to keep a list of versions and modules to download or something...

My thoughts were to use the code I gave you to pull a table into Excel, which maybe had the list of Version numbers and corresponding links. As I say, as long as it's set up in a table, (which yours is,) that part should be easy.

With that in Excel, you could easily code your stuff to figure what versions and links were required, and I think you could download them from there. I've never used Excel to download a file from the web to a specified location (yet), but can't see why it couldn't be done.

I'm thinking that this could be the beginnings of a great KB entry though!

Hi Ken,

Well you got what I'm trying to do right (update versions via the net).

I recently got a site started, and originally had some links to books there where viewers could simply "test" the code I'd posted instead of down-loading it.

When I tested this "testing" myself, it opened my Excel "application" and ran the code via it. i.e. it appeared to open the book using Office on my PC, so I think a workbook open event should then run just as easily, if not, the first book could run a macro to trigger the transfer of modules anyway, both books then being open means I should the be able to simply copy/transfer the newer modules from one book to the other.

I've already got the code to compare versions and import a higher version number code module from the desk-top and delete the lower numbered one (a long time back) it's just the matter of getting these modules downloaded off the net that's been holding me up...

Won't have time to try today, but will let you know...And yeh, when finished and working properly, will certainly submit to KB

Regards,
John

[Killian]

Hi John,
I was thinking that it might be easier to manage the contents of your web page if you added a Reference to MS HTML Object Library. Then you just need to set your target page as an object and list all the hyperlinks on it.
Well, I say 'just'. I had a quick look and the object model and it might be less than straight forward.
If I get a chance today I'll have a closer look...

[johnske]

Thanx Killian,


That's maybe something else for me to look at... Haven't had time to do very much at all yet (maybe 2morrow) but for the moment, I've opened a book on the site and when I check, the desktop book I'm using to link from and the book on the web page both appear in the project explorer window. So it seems I can then do just about anything that can be done when two books on (say) the desktop are both open. The only 'difference' being that any changes to the book on the site won't be saved in the book on the site, but a copy of it will be saved to the desktop.

Anyway, many thanks to both you and Ken for your input - but as I wont have time to do this for a few days anyway and i've got enough to go on now, I'll mark it as "solved".

Regards,
John

[johnske]

Hi Ken, Killian,

I finally got time to try all this out, after MUCH "playing around" it turned out to be remarkably simple.

You CAN use a "Workbook_Open event, also, there's no need to set any references and there's not even a need to use Internet Explorer, you just access the remote book with this:

'put your own path here

Application.Workbooks.Open ("http://www.geocities.com/johnske100" & _
"/HookToMySite_MASTER.xls")

The full code has been submitted for KB approval. A short uncommented copy here FYI:[vba]
'In workbook on PC

Option Explicit

Sub BeamMeUpScotty()
Dim Answer As VbMsgBoxResult, N%, MyFile$
Answer = MsgBox("1) You need to be on-line to update" & vbLf & _
"2) The update may take a few minutes" & vbLf & _
"3) Please do not interrupt the process once started" & vbLf & _
"" & vbLf & _
"SEARCH FOR UPDATE?", vbYesNo, "Update?")
If Answer = vbNo Then Exit Sub
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
On Error GoTo ErrorProcedure
Application.Workbooks.Open ("http://www.geocities.com/johnske100" & _
"/HookToMySite_MASTER.xls")
Workbooks("HookToMySite_MASTER.xls").Close savechanges:=False
MyFile = Dir(ActiveWorkbook.Path & "\Version101.bas")
If MyFile = Empty Then
MsgBox "No new file found" '< (download failed)
Else
With ActiveWorkbook.VBProject
For N = 1 To .VBComponents.Count
If .VBComponents(N).Name = "Version101" Then
MsgBox "Sorry, there are no later versions available", _
, "Already Using Current Version..."
Exit Sub
ElseIf .VBComponents(N).Name = "Version100" Then
.VBComponents.Remove .VBComponents(N)
MsgBox "Old file removed"
Exit For
End If
Next N
End With
Application.VBE.ActiveVBProject.VBComponents _
.Import (ActiveWorkbook.Path & "\Version101.bas")
MsgBox "Version upgrade complete..."
End If
Run ("NewModuleV101")
ActiveWorkbook.Save
Exit Sub
ErrorProcedure:
MsgBox Err.Description
End Sub

'this goes into the book on the site

Option Explicit
'This code is triggered by a Workbook_Open event 

Sub ExportModule()
Dim MyFile$, N%
Workbooks("HookToMySite_slave.xls").Activate
MyFile = Dir(ActiveWorkbook.Path & "\Version101.bas")
If MyFile = Empty Then
With ThisWorkbook.VBProject
For N = 1 To .VBComponents.Count
If .VBComponents(N).Name = "Version101" Then
Application.VBE.ActiveVBProject.VBComponents(N) _
.Export (ActiveWorkbook.Path & "\Version101.bas")
Exit For
End If
Next N
End With
Else
MsgBox "No later version is available", , "Already updated..."
End If
End Sub