Can one access independent Application objects by macro?

[Deo.Winter]

Can one access a spreadsheet created by an internet export, from within an existing Excel spreadsheet, without first "touching" the newly created spreadsheet?

By way of explanation:
I am running Excel 2007 under Windows 7. I have an Excel spreadsheet in which I collect weather data as a continuum, "WindData.xlsm". Every morning I access an Internet site using Internet Explorer, and from the Right-Mouse click I select "Export to Microsoft Excel ". This creates a "Book1" Workbook with the data (in a range named "ExternalData_1") in Sheet1!$A$1:$Y$18 . I later discard this file.
I can click on Book1, select the data, click on WindData.xlsm and implement macros to process the data. I have been unable to get a macro (from WindData.xlsm) to access Book1 without me first activating Book1 by clicking on it (and the selecting the data). It seems as if each Internet created Workbook runs as it's own (and only) copy of the Excel application. For each Internet created Workbook, a macro run from within the Workbook yields an Application.Workbooks.Count of 1 (irrespective of the number of open Internet creations). If I open n (say 5) files "normally" (in addition to any number of open Internet creations), and interrogate, Application.Workbooks.Count will equal n (say 5) (irrespective of the number of open Internet creations). The regular spreadsheets all know about each other, but the internet creations seem to only know about themselves, and seem to be knowable only to themselves. It thus seems as if by normally opening WindData.xlsm and any other Excel files I choose, the same instance of Excel is being used and the opened workbooks added to the Workbooks collection, but that a different, unique, instance of Excel is used for each and every Internet download.

Thus: Can one access an "Internet" creation by macro from within a "standard" open spreadsheet, without first physically accessing the Internet creation by selecting it and selecting the data or saving the file?

Any ideas?

 

[Ruddles]

Every morning I access an Internet site using Internet Explorer, and from the Right-Mouse click I select "Export to Microsoft Excel ". This creates a "Book1" Workbook...

Instead of opening the workbook, can you save it to your hard disk? If so, you can then open it programatically and that gives you immediate access to it.

 

[Deo.Winter]

Thanks, Ruddles! Yes, I can access the file fully, if I save it. But that means "touching" it, which means I may as well do what I am doing at present (Click on file then Ctrl+Shift+End then Ctrl+Ins and go to my primary file and dump the info, process it via macro from there). I was hoping to be able to access it via macro without first touching it. I fear that the independent instances of Excel do not know of each other. I know Windows knows of them (Task Manager knows, anyway!), but I do not know how to access that information.
I appreciate your input. Thanks.
Deo

 

[Jaafar Tribak]

You could use the GetObject Method to get a pointer to the internet opened workbook and automate it from there.

Sub Test()

    Dim oWb As Workbook
    
    Set oWb = GetObject("book1")
    
    MsgBox oWb.Names("ExternalData_1").RefersTo

End Sub

If you have various excel instances each with a loaded workbook with the same name ie: Book1 then it is more difficult as the GetObject will get confused as to which Book1 it will return.

 

[Ruddles]

Yes, I can access the file fully, if I save it. But that means "touching" it... I was hoping to be able to access it via macro without first touching it.

If you can open IE, navigate to the download link and save the data to disk programatically, you can open it from there using a Workbook.Open command without touching anything and within the same piece of VBA code..

Perhaps if you post the URL where you're getting the data from, we can show you how to do this.

 

[Deo.Winter]

Thanks Jaafar and Ruddles (again!)
Jaafar, your very elegant solution does return the correct range - I shall fiddle and try and convert this information (I'll see if I can remotely suck in (copy) the correct range (including file)). I'll let you know the result.
Ruddles, your solution is the MotherLode! If I can do the whole lot in auto-mode, wow, that would be great! I access 5 pages (spread over 3 sites) each morning, and "Export to Excel" the data. The sites are:

http://windreport.co.za/wg_portelizabeth.php
http://weather.uk.msn.com/hourly.aspx?wealocations=wc:SFXX0041&q=Port+Elizabeth,+ZAF+forecast:hourly
http://weather.uk.msn.com/tenday.aspx?wealocations=wc:SFXX0041&q=Port+Elizabeth,+ZAF+forecast:tenday
http://www.yr.no/place/South_Africa/Eastern_Cape/Port_Elizabeth/hour_by_hour_detailed.html
http://www.yr.no/place/South_Africa/Eastern_Cape/Port_Elizabeth/long.html

I suspect that if your plan can work on "SiteName", the minor variations are just different implementations of your basic plan. If you can assist, it would be great. If you can't, thanks a lot for your input! Does anybody know why Google Chrome does not have (visible to me, anyway) an "Export to Excel" feature? I know MS may be the competition, but Excel remains the most widespread sheet (if not wide spreadsheet).

Thank you very much, both.