asked on

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

regmigrant

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

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ejgil Hedegaard

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.

Michael Purdham

ASKER

Deselecting 'Enable background refresh' In the Data Connection Properties solved the problem.
Many thanks

[ fanpages ]

You're very welcome.

Thank you for closing the question so promptly.