Data from SQL Database not refreshing when running a macro

[breezeit]

Hi All,

I have an excel file with quite a few connections to a SQL server. The excel file is fully automated which refreshes data when a single cell input is updated by the user. If I open this file and manually change the input, all data refreshes without any problems. However, since I need to replicate this file over 200 times (i.e. the single cell input needs to change over 200 times), I have a macro which runs this process. What I have noticed is that the macro runs without any warnings or errors, but does not update the data with the cell input is changed. This works if I manually change it though, but doing this 200 times manually is not a solution ofcourse.

I have tried enabling macros in excel options (I am using excel 2007) but still no luck. Any help will be much appreciated.

Many thanks in advance.

 

[Asala42]

Based on your enabling macro comment I have to ask - is this file your creation or something you inherited? If it's not your file you may want to verify with the creator of the file that you are using it correctly (a common issue when complicated macro heavy excel files get thrown to a new user).

All that aside, one of the best ways to troubleshoot a macro that's not working as planned is to step through it. Go into VBA (Alt+F11), click in the macro somewhere and hit F8 repeatedly to execute the code line by line. This should help you find where there's a problem if it's a coding issue.

 

[breezeit]

Based on your enabling macro comment I have to ask - is this file your creation or something you inherited? If it's not your file you may want to verify with the creator of the file that you are using it correctly (a common issue when complicated macro heavy excel files get thrown to a new user).

All that aside, one of the best ways to troubleshoot a macro that's not working as planned is to step through it. Go into VBA (Alt+F11), click in the macro somewhere and hit F8 repeatedly to execute the code line by line. This should help you find where there's a problem if it's a coding issue.

Thanks for the quick reply.

In response to your comments - I have created this file with my username so yes, it's my own file. Secondly, I also tried the troubleshoot and couldn't find anything obvious as my macro doesn't give any errors or warning while running.

The issue is that the data (which should come from the SQL database) doesn't refresh during the macro run but does refresh when the same process is followed manually. Could this be because excel does not recognise automated input changes where data needs to refresh by SQL? Or could there be something I can add to my code that would enable this automatic data refresh?

Thanks.

 

[Kyle123]

Can you please post your code, otherwise we're just shooting in the dark

 

[Asala42]

You might just need an Application.RefreshAll or similar line somewhere in your loop following the change to the key cell.

I'd need to see your code to be able to intelligently talk to it.

If you're not getting an error, that suggests either a problem relating to your loop or you may have accidentally disabled error msgs with an "On error resume next" line. If you have that you could try removing it and seeing if you're erroring out somewhere.

Also, have you stepped through the code line by line? That should narrow down where ther'es a problem

 

[breezeit]

Can you please post your code, otherwise we're just shooting in the dark

Hi,

My code is pasted below, but I'll also go through it in words here

1) My macro file contains the text (which is the specific input) which needs to go into the main file.
2) On executing the macro, my template file opens, takes the text from point 1 and adds it to the Inputs!B2
3) When the macro finished and I open the new file, I can see the cell reference has changed but none of the data has been refreshed. As I stated earlier, if I follow the same process manually, i.e. without running a macro, the data updates without any problems.

Here is my code:

Sub ARC()
'
mfile = ActiveWorkbook.Name

RowCount = LastRowInOneColumn("A")
i = 2
Do While i <= RowCount

'select folder (this section determines the file paths)
v = Cells(2, 4).Value
filename1 = Cells(i, 1).Value
thefoldername = Cells(4, 4).Value & "\"

Range("A1").Select
Application.DisplayAlerts = False
Workbooks.Open (v)
Application.DisplayAlerts = True
afile = ActiveWorkbook.Name

Windows(mfile).Activate
Cells(i, 1).Select
Selection.Copy

Windows(afile).Activate
Sheets("Inputs").Select
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:= _
thefoldername & filename1 & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(mfile).Activate


i = i + 1

Loop

ActiveWorkbook.Save
MsgBox ("DONE!")


End Sub

Many thanks.

 

[Kyle123]

Post the code for refreshing the query, were I to guess - the query table is running in async (background query) this means that it is running, but your code closes the workbook before it's finished. You'll want to disable the background query so that your code waits for the refresh to completes

 

[breezeit]

Post the code for refreshing the query, were I to guess - the query table is running in async (background query) this means that it is running, but your code closes the workbook before it's finished. You'll want to disable the background query so that your code waits for the refresh to completes

There isn't a code for Refresh in my code - should this not be happening automatically as it does when I open the file manually and change the input (Inputs!B2 cell)?

 

[Kyle123]

Then you need to turn off background query on the query table