Hi,
I have a bunch of workbooks that are used to capture information about implants used in surgical cases. I use a small VBA process each morning to update a querytable which contains all of the core patient information for future cases, however it is not working properly.
The code goes through the process OK, or so it appears, opens and closes each sheet, and saves them appropriately, but the query table doesn't seem to refresh no matter what I do.
Here's what I have thus far... any suggestions;
Code
Sub RevisedRefresh()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "G:\SHS\Theatre\Common\Reception\Implants"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Worksheets("Patients").Select
With Range("D10").QueryTable
.BackgroundQuery = False
.Refresh
End With
Worksheets("Interface").Select
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Display More
Thanks in advance...
Paul