Excel fails catching file download window

[Lloyd Blankfein]

Hello,

with the attached files macros I try to download three excel files from an url to a local path. By opening the vba editor and running the macro WorkBook_Open this works perfect. But if I open the workbook (here WorkBook_Open is triggered by default), there is a problem with third download.

I have marked the line in HeritageOrg where the problem occurs (! here the macro stops !!!). It seems Excel is not able to catch the file download window. I have no idea that's wrong.

Can anybody help me up?

Thank you very much!

Lloyd

[Kyle123]

Why not just scrape the table data from the page rather than trying to download?

[Lloyd Blankfein]

Hi Kyle,

sorry I don't understand, that exactly do you mean with "scrape ".

In general the download works fine. Just by using the auto run WorkBook_Open there is a problem.

[Kyle123]

Send keys is flakey at best.

You can loop through the table on the page to extract the data, it's all displayed there before downloading. Why not return the data from the page directly into a workbook and save that?

[Lloyd Blankfein]

I'm still not sure if I got you right!

Please have a look on the changes I've done in HeritageOrg

Please Login or Register  to view this content.

I still need SendKeys to trigger the file download box which allows me to open the desired workbook.
With this solution the problem gets even worse:
1) Same problem as described above: Different behavior by just opening the file (auto run) and manual execution via vba editor.
2) Please see this line: !!!!! please set breakpoint one line above !! Again different behavior: By monitoring Application.ActiveWorkbook.Name via the watch window, this value seems to be always vba_download_NODB.xlsm without setting a breakpoint. By using the breakpoints its fist vba_download_NODB.xlsm and data.csv then.

?????

[Kyle123]

No, you have misunderstood.

Don't download anything. The data is there on the page, there's no need to use the download button.

Just loop through the table that has your data in on the page, writing it to Excel. Then save that workbook

[Lloyd Blankfein]

Hi Kyle,

Please Login or Register  to view this content.

After running this code I had a view on the sites html-code. In line 433 the definition of the grid table starts its name is ctl00_cphContent_ExpMulti_pnlGrid. Now I need to know how to access and parse this object. I was not able to localize it via the watch window.

Thank you very much!

Lloyd

[Lloyd Blankfein]

This works perfect:

Please Login or Register  to view this content.

Lloyd

[Kyle123]

Yep, that's what I meant