vba does not find new workbook

[Lloyd Blankfein]

Hello,

     Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
     

     Dim i As Integer
     For i = 1 To Application.Workbooks.Count
        If InStr(Application.Workbooks.Item(i).Name, "report") > 0 Then
            Application.Workbooks.Item(i).Activate
            Dim aSheet As Worksheet
            Set aSheet = Application.Workbooks.Item(i).Sheets(1)
            Exit For
        End If
     Next i
     Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 5)

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]

I still hang on this problem.

Does anybody has an idea what is wrong?

[Lloyd Blankfein]

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

[Lloyd Blankfein]

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

[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:

    Dim browser As InternetExplorer: Set browser = New InternetExplorer    
    browser.Navigate ("http://www.google.com/trends/viz?q=euro%20crisis%2C%20euro%20vs%20dollar&cmpt=q&graph=all_csv")
    browser.Visible = True
        
    'Here is where the file download box opens
    Application.Wait (Now + TimeValue("0:00:05"))
    SendKeys "{TAB}"
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys "{TAB}"
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys "{ENTER}", True
    Application.Wait (Now + TimeValue("0:00:05"))

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]

     Dim aWBook As Workbook
     Set aWBook = Workbooks(2)

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!