I am trying to refresh an excel through a macro in access. I am getting the following error on the refresh table code:
"the database has been placed in a state by user "admin" on machine "XXX" that prevents it from being opened or locked"
The excel refresh link uses the same access dB. Is it not possible to do so? Is there any workaround ?
My access and excel are 2007 version.
The code:
Code:
Dim XL As Object
Dim wb As Object 'Workbooks
Dim ws As Object ' Worksheet
Dim PT As Object ' PivotTable
DoCmd.SetWarnings False
'*********** Format Excel ***********
fileNameSPOC = "G:\BSDT Lease Admins\Daily Productivity Reports\SPOC Productivity.xls"
'fileNameFLA = "G:\BSDT Lease Admins\Daily Productivity Reports\SPOC Productivity (FLA).xls"
Set XL = CreateObject("Excel.Application")
Set wb = XL.Workbooks.Open(fileNameSPOC)
'XL.DisplayAlerts = False
XL.Visible = True
For Each ws In wb.Worksheets
For Each PT In ws.PivotTables
PT.PivotCache.BackgroundQuery = True
PT.RefreshTable
Next PT
Next ws
wb.Save
wb.Close
XL.Quit
Set wb = Nothing
Set XL = Nothing