Excel VBA activecell.offset(x,Y).value does not poplulate normal mode.

I have some Excel VBA code where I run an MS Query into a worksheet. Cell A1 is selected after the query, and I want to populate some variables with values of cells in reference to cell A1.
If I step through the code in debug mode, the variables populate fine. But if I run the macro full speed (F5), these variables are empty.
Note: I have tried adding a WAIT command but that does not seem to work. I have also tried the DOEVENTS function but that does not work.
Here is this part of the code.
                    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                        "ODBC;DSN=Prophet21;UID=bdobo;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=anb002415" _
                        , Destination:=ActiveCell).QueryTable
                         Debug.Print
                        .CommandText = Array("exec [MYDBASENAME].[dbo].STOREDPROCEDURENAME" & SP & " " & intInvoice & "")
                        .RowNumbers = False
                        .FillAdjacentFormulas = False
                        .PreserveFormatting = True
                        .RefreshOnFileOpen = False
                        .BackgroundQuery = True
                        .RefreshStyle = xlInsertDeleteCells
                        .SavePassword = False
                        .SaveData = True
                        .AdjustColumnWidth = True
                        .RefreshPeriod = 0
                        .PreserveColumnInfo = True
                        '.Refresh BackgroundQuery:=False
                        .Refresh
                        'MsgBox .CommandText
                    End With
           
                Select Case SP
   Case "1"
   'if SP = 1
                      Selection.AutoFilter
                      Application.Wait (Now + TimeValue("0:00:01"))
                      DoEvents
                      Application.Wait (Now + TimeValue("0:00:01"))
               
                intOrderNo = ActiveCell.Offset(1, 4).Value
                intInvoiceDate = ActiveCell.Offset(1, 1).Value
                intReceiptdate = ActiveCell.Offset(1, 1).Value
                invInvoiceamt = ActiveCell.Offset(1, 39).Value
                strOrganization = ActiveCell.Offset(1, 70).Value

Does anyone have a better way to do this or a solution for this one?
briandoboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
This looks like the query is still executing when the code continues. There are various threads covering ways to resolve to be sure that the query completes first - here is one that looks promising:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21887558.html
0
briandoboAuthor Commented:
I do not believe that the query is still executing. The reason being that I did have the variable population code in a different area of the macro and it did the same thing. I just moved it right after the query in hopes to resolve the issue.
I will look at your supplied link.
0
NorieData ProcessorCommented:
Have you tried replacing ActiveCell with an explicit range reference, eg Range("A1")?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

briandoboAuthor Commented:
OH, Yes I am sorry, I have tried the explicit range reference and these variables still do not populate.

One thing to note is that I "think" that this used to work on an older computer. This is a very old macro and I used to use it. I think this might be because I have a newer, faster computer. I wonder if it has something to do with the faster processing power. But only suspecting this.

I can't believe that I cannot seem to Google much about anyone else having a similar problem like this.
0
Martin LissOlder than dirtCommented:
The thread that regmigrant pointed to may well have the solution you need, especially the post with the DoEvents loop. I just thought I'd point out however that according to MS Wait,
suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue.
I don't know if your query is considered a background process, but if it isn't then it explains why using wait didn't help.
0
briandoboAuthor Commented:
Actually what the solution that worked was for me to un-comment the   '.Refresh BackgroundQuery:=False
and comment the  .Refresh

I thought that I put the .Refresh in just to get the query to run. But I must have been mistaken. But it is working now.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.