Refreshing Data Query During VBA Macro

[goodguygreg]

Hi,

I have run into some trouble when trying to automate some excel work. At the moment, the excel workbook is using a plugin to draw data from a database, I believe through SQL. The plugin is used as a formula shown below, where A, B, C, D, and E are all parameters.
=DerivixExcelAddin.DerivixFunctions.DxDs(A, B, C, D, E)

Currently, the macro takes the inputted data, sorts specific portions of it and then pastes it into a separate worksheet. I have 20 specific inputs for parameter A that I need to cycle through and fetch data, with parameter B needing to be changed 6 times for each distinct input of parameter A. This would lead me to 120 times I would have to execute this program, multiple times a day if I were to do it without VBA.

My problem arises when I go to change one of the parameters, lets say change B to B2, the data doesn't refresh because the macro is running. This prevents me from having the macro loop through the different parameters as the data is not updated. Is there anyway to have the data update as I change the parameters? I have tried pauses and halts with no luck.

Any help would be appreciated greatly, thank you.

[dflak]

Try this: Go to Data -> Connections. Select the connection that you wish to run. Click on Properties. On the Usage tab, uncheck the block that says Enable background refresh. You might also throw in a DoEvents before the next iteration of the refresh.