vba does not find new workbook

[Lloyd Blankfein]

Hello,

Please Login or Register  to view this content.

My excel application contains two workbooks, the second one directly created before running the code above.
If I set a breakpoint before the for-loop and then execute the code, everything works fine. By executing the code in an automated manner, vba does not execute the code inside the if-statement.

Why vba does not find the second workbook, it surely exists.

Thank you very much!

Lloyd

[Norie]

Have you saved the new workbook so it has a name?

Why are you looping to find the new workbook?

You could set a reference to it as soon as it's created.

[Lloyd Blankfein]

No I haven't saved the workbook. It is a temporary file received by a filedownload.

[Norie]

Lloyd

I thought you created the workbook in the code?

Even if you didn't can't you set a reference to it when it's loaded/downloaded?

[Norie]

Wrong thread.

[Lloyd Blankfein]

I trigger the filedownload via vba:

Please Login or Register  to view this content.

I know that sendkeys is problematic. But as far as I see there is no other option to download the file.

How to set a reference to filedownload?

[Norie]

Is the file actually opened in Excel?

[Lloyd Blankfein]

Yes it is.

In the same excel instance in another workbook.

application.workbook contains two items. Item 1 is the workbook containing the vba code, item 2 is the downloaded csv-file.

[Norie]

If that's the case you can refer to the new workbook with Workbooks(2).

By the way, there alternatives to SendKeys.

Don't have any links to hand but why not try a search?

[Lloyd Blankfein]

Hi Norie,

I have searched through the code of "http://www.google.com/trends/explore#q=euro%20crisis,%20euro%20vs%20dollar". To download the csv-file in a manual manner you need click the gearwheel looking button in the upper right. This buttons link is concatenated by a java function. As far as I know it is not possible to build and trigger this link via vba!?

Lloyd

[Lloyd Blankfein]

Please Login or Register  to view this content.

That does not help. VBA is still not able to find the new object, if there is no manual interruption (breakpoint).

[Norie]

Did that code cause an error or create a reference to another sheet?

[Lloyd Blankfein]

It causes an error: Subscript out of range

By setting a breakpoint in line Set aWBook = Workbooks(2) as expected.

[Lloyd Blankfein]

It causes an error: Subscript out of range
By setting a breakpoint in line Set aWBook = Workbooks(2) vba behaves as expected.

[Norie]

Perhaps it's a timing issue.

Why not try adding a sleep/wait?

[Lloyd Blankfein]

sleep/wait also delays the download itself!

[Lloyd Blankfein]

I still hang on this problem.

Does anybody has an idea what is wrong?

[Lloyd Blankfein]

http://www.excelforum.com/excel-prog...vba-macro.html