VBA Hyperlinks Excel 2010

Question

We have used the following code in a macro in Excel 2003 and it works great.  The hyperlink runs a query in PeopleSoft.  I'm trying to run this on a Windows 7 / Office 2010 machine and am having trouble.  It looks like it runs the PeopleSoft query but doesn't open in a new workbook like it did before. 

'  vHypLnk is built before the code below.

' WBC = count of open workbooks

 With Worksheets("Macro")

        .Hyperlinks.Add .Cells(30, 2), vHypLnk

        .Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

    End With

    Do Until Workbooks.Count = WBC + 1 

        DoEvents

    Loop

    DoEvents

    Workbooks(ThisBook).Worksheets("Macro").Cells(30, 2).ClearContents

Once the workbook (with the results) is opened, I copy the data and paste in the workbook with the macros and do some other stuff with it.  This works GREAT in 2003 but not so good in 2010.

Any help would be greatly appreciated.  Thanks....

Answer 1

I've tweaked it, but am not sure it will work

Dim aWB As Excel.Workbook

Dim MacroWS As Excel.Worksheet

Dim myCell As Excel.Range

Set aWB = ThisWorkbook

Set MacroWS = aWB.Worksheets("Macro")

Set myCell = MacroWS.Cells(30, 2)

myCell.Hyperlinks.Add anchor:=myCell, Address:=vhyplink

myCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Do Until Workbooks.Count = WBC + 1

    DoEvents

Loop

DoEvents

myCell.ClearContents

I'm not sure what you're doing with the Do until Workbooks.count = ... part.

Answer 2

Thanks for the reply.  In 2003, the Do....Until is used while I'm waiting for the query results to appear in an Excel Workbook.  Once a "new" workbook appears, I copy the results from it and continue on.

For whatever reason, I couldn't get this code to work reliably in 2010.  I finally punted and have the user run the query outside of the macro and save the results in Excel.

Now when they use the macro, it asks them to open a file and I proceed from there.  That was a lot easier that pulling out my hair trying to get the hyperlink code to work. 

Thanks again for your help