Refreshing Excel wkshts w/ODBC Connections.

K

kfschaefer

I have several wksheets that need to be refreshed weekly, however. some of
them have Pivot Tables and some are worksheets with an ODBC connection to my
active mdb. I am getting "Read ONly" due to the connection to my active mdb.

I am using a mdb to run weekly to update the underlying tables and running
the queries that the wkshts are based on.

Here is the code I am currently using. and the problem with this code is
that I am still getting the ODBC connection warning popup and I need this to
be completely automated since it will be run at night.

Thanks,

Karen

Function OpenXL_Pivot(pstrWorkbook As String, ByRef objExcel As
Excel.Application)

Dim xlPivotCache As Excel.PivotCache
Dim strList As String
Dim xlWorkbook As Excel.Workbook

On Error GoTo Error_Label
objExcel.Workbooks.Open (pstrWorkbook)
Set xlWorkbook = objExcel.Workbooks.Open(pstrWorkbook, 3)
objExcel.DisplayAlerts = False
For Each xlPivotCache In ActiveWorkbook.PivotCaches
xlPivotCache.Refresh
Next xlPivotCache

Exit_Label:
On Error Resume Next
ActiveWorkbook.Save
ActiveWorkbook.Close True
Set xlPivotCache = Nothing
Set xlWorkbook = Nothing
Exit Function

Error_Label:
MsgBox Err.Description
Resume Exit_Label

End Function

Someone suggested that the " Set xlWorkbook =
objExcel.Workbooks.Open(pstrWorkbook, 3)" and '3 = update remote & external
references

as I said I still get the msg dialog.
 
Top