Here's a method that I know will work. However, there are some complications.
Here's how it's done:
- Put the spreadsheet with the data in a separate workbook. This worksheet should execute the refresh query when it's opened and then close once the data is updated.
- Create a batch file to call the "Data" Excel file.
- Within a different workbook, create a procedure (macro) for the user to call. This procedure will call the batch file, which subsequently calls the Excel file. Since you are calling a batch file and not Excel directly, the Excel procedure will continue because the command shell is released so quickly and opens the other Excel file in a different thread. This allows you to continue working within the main Excel file.
Here are some complications:
- I included a method to alert the user that the data has been udpated. There are timing issues where it's possible to try to check if the data has been update when the workbook is not accessible, which forces the user to try to update values. I included a method called my time which pauses the execution of the code so it only checks every so many seconds.
- The updated worksheet will pop up in a new window, so the user will need to click on their original worksheet and keep working. You could learn to hide this if you're comfortable with Windows scripting (I haven't learned that yet).
Here are some files and code. Be sure to read the comments in the code for why some things are there.
FILE: C:\DataUpdate.xls
We'll make a workbook called "DataUpdate.xls" and put it in our C:\ folder. In cell A1 of Sheet1, we'll add our QueryTable which grabs external data.
Option Explicit
Sub UpdateTable()
Dim ws As Worksheet
Dim qt As QueryTable
Set ws = Worksheets("Sheet1")
Set qt = ws.Range("A1").QueryTable
qt.Refresh BackgroundQuery:=False
End Sub
Sub OnWorkbookOpen()
Dim wb As Workbook
Set wb = ActiveWorkbook
'I put this If statement in so I can change the file's
'name and then edit the file without code
'running. You may find a better way to do this.
If ActiveWorkbook.Name = "DataUpdate.xls" Then
UpdateTable
'I update a cell in a different sheet once the update is completed.
'I'll check this cell from the "user workbook" to see when the data's been updated.
Sheets("Sheet2").Range("A1").Value = "Update Table Completed " & Now()
wb.Save
Application.Quit
End If
End Sub
In the ThisWorkbook object in Excel, there's a procedure called Workbook_Open(). It should look like the following so it executes the update code when it is opened.
Private Sub Workbook_Open()
OnWorkbookOpen
End Sub
NOTE: I found a bug when this file closed if 1) you accessed the file from the command line or shell and 2) you have the Office Live Add-in installed. If you have the Office Live Add-in installed, it will throw an exception on exit.
FILE: C:\RunExcel.bat
Next, we're going to create a batch file that will open the Excel file we just made. The reason that call the Excel file from within the batch file and not directly from the other Excel file using Shell is because Shell will not continue until the other application closes (at least when using Excel.exe "c:\File.xls"). The batch file, however, runs its code and then immediately closes, thus allowing the original code that called it to continue. This is what will let your uses continue working in Excel.
All this file needs is:
cd "C:\Program Files\Microsoft Office\Office10\"
Excel.exe "C:\DataUpdate.xls"
If you're handy with Windows Scripting, you do fancy things like open the window in a hidden mode or pass a parameter of the file name or Excel location. I kept it simple with a batch file.
FILE: C:\UserWorkbook.xls
This is the file that the user will open to "do their work in." They'll call the code to update the other workbook from within this workbook and they'll still be able to work in this workbook while this one is updating.
You need a cell in this workbook where you'll check the "Update Table Completed" cell from the DataUpdate workbook. I chose cell G1 in Sheet1 for my example.
Add the following code to a VBA module in this workbook:
Option Explicit
Sub UpdateOtherWorkbook()
Dim strFilePath As String
Dim intOpenMode As Integer
Dim strCallPath As String
Dim strCellValue As String
Dim strCellFormula As String
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("G1")
strCellFormula = "='C:\[DataUpdate.xls]Sheet2'!A1"
'This makes sure the formula has the most recent "Updated" value
'from the data file.
rng.Formula = strCellFormula
strFilePath = "C:\RunExcel.bat"
intOpenMode = vbHide
'This will call the batch file that calls the Excel file.
'Since the batch file executes it's code and then closes,
'the Excel file will be able to keep running.
Shell strFilePath, intOpenMode
'This method, defined below, will alert the user with a
'message box once the update is complete. We know that
'the update is complete because the "Updated" value will
'have changed in the Data workbook.
AlertWhenChanged
End Sub
'
Sub AlertWhenChanged()
Dim strCellValue As String
Dim strUpdatedCellValue As String
Dim strCellFormula As String
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("G1")
strCellFormula = "='C:\[DataUpdate.xls]Sheet2'!A1"
strCellValue = rng.Value
strUpdatedCellValue = strCellValue
'This will check every 4 seconds to see if the Update value of the
'Data workbook has been changed. MyWait is included to make sure
'we don't try to access the Data file while it is inaccessible.
'During this entire process, the user is still able to work.
Do While strCellValue = strUpdatedCellValue
MyWait 2
rng.Formula = strCellFormula
MyWait 2
strUpdatedCellValue = rng.Value
DoEvents
Loop
MsgBox "Data Has Been Updated!"
End Sub
'
Sub MyWait(lngSeconds As Long)
Dim dtmNewTime As Date
dtmNewTime = DateAdd("s", lngSeconds, Now)
Do While Now < dtmNewTime
DoEvents
Loop
End Sub
As you can see, I constantly updated the formula in the "Listening Cell" to see when the other cell was updated. Once the data workbook has been updated, I'm not sure how you'd force an update in code without rewriting all the cells. Closing the workbook and reopening it should refresh the values, but I'm not sure of the best way to do it in code.
This whole process works because you're using a batch file to call Excel into a different thread from the original file. This allows you to work in the original file and still be alerted when the other file has been updated.
Good luck!