2 Macros run independently but won't run consecutively from one macro

I am pulling financial data via a SQL query into Excel and have problems using V Lookup with one column of data returned so need to re-format the cells in this column.
I have 2 macros.
The first one refreshes the data by updating the Command Text and runs the Stored Procedure in SQL with variables to return the data.
The second selects the 'offending' column and re-formats the cells using the 'Text To Columns' function to allow the V Lookup to work.
If I run these separately they work fine however I want to run from one button both the data refresh and then re-format macro.

A strange thing happens when I do this.
Both run, the data is returned and I can see the V Lookup values appear and then almost immediately they all return to #N/A. I have tried adding a delay but this makes no difference. I can see that the second macro is running as it ends up in the cell I specify.
Help would be appreciated. Thanks.



Open in new window

Sub Refresh_UK_TB()
Call RefreshTBQueryUK
Call Set_Accounts_For_VLookup_UK
End Sub

Sub RefreshTBQueryUK()
'Set the Query Command Text and refresh the data for UK
    With ActiveWorkbook.Connections("TBUK").OLEDBConnection
    .CommandText = "EXEC [dbo].[SP$$XXXX TRIAL BALANCE UK] '" & Range("D1").Value & "'" & "," & "'" & Range("D2").Value & "'"
    End With
    Range("A4").Select
    ActiveWorkbook.Connections("TBUK").Refresh
       
End Sub

Sub Set_Accounts_For_VLookup_UK()
    Application.Wait (Now + #12:00:10 AM#) ' Wait for 10 seconds
    Range("D5").Select
    'Select all cells with a value
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("D5"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Range("D20").Select
End Sub
Paul GAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
it could be that VBA is not running them consecutively but in parallel ie: it doesn't wait for the query to complete before running the format, try making the execution of the formatting macro dependent on completion of the query with either a global variable set at the end of the query or a loop that checks the number of rows returned before going on to the format
[ fanpages ]IT Services ConsultantCommented:
Is the OLEDB Connection's "BackgroundQuery" property set to True?

If so, please set this to False, & re-try your code as it is shown above.

However, I would also be tempted to add a DoEvents statement, if not at the end of the first subroutine, or not at the beginning of the second, certainly between the two.

If either (or both) of these suggestions still do not present the expected outcome, there is a third option regarding the use of the Connection's Refresh State but, from what you have provided so far, I think the first two suggestions will be adequate.

Please let me know the outcome either way.

Thanks.

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
Ejgil HedegaardCommented:
You could try to move the 10 seconds wait into the main sub just before the second Call, to be sure that the first sub is finished before the second run, like this.
Sub Refresh_UK_TB()
Call RefreshTBQueryUK
Application.Wait (Now + #12:00:10 AM#) ' Wait for 10 seconds
Call Set_Accounts_For_VLookup_UK
End Sub

It must be a huge query if it use 10 seconds to update, so I don't think that is the problem,
But a #N/A return from the vlookup indicate it has to do with the format of the value looked up, and the format in the lookup range.
One is a number, and the other is text, or something else is wrong.

Without some data it is difficult to see.
Paul GAuthor Commented:
Deselecting  'Enable background refresh' In the Data Connection Properties solved the problem.
Many thanks
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Thank you for closing the question so promptly.
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.