Updating Excel repots from MS Access using VBA

[subhanak]

Hi,
I am using the following sub to update a reporting metrics (MS Access/Excel). Currently, this sub refreshes the reports perfectly; however, I would like to modify it to add the following:
1. Save the workbook
2. Close it
3. Open password protected workbooks
Your suggestion and assistance is appreciated.
Thanks
=============================================================
Public Sub RefreshMatrics()
Dim ObjXL As Object, x
On Error Resume Next
Set ObjXL = CreateObject("Excel.Application")
With ObjXL.Application
.Visible = True
.workbooks.Open "Path..."
x = .ActiveWorkbook.refreshall
End With
Set ObjXL = Nothing
End Sub
=============================================================

 

[Micron]

Try this untested air code:

Dim xlx As Object, xlw As Object
Dim svChartPath As String
On Error GoTo ErrHandler....'first error handled from anything above should reset to zero if code is allowed to continue

svChartPath = DLookup(... I get info like this from a table
'more code?
On Error Resume Next  'redirect error handling until after test for Excel running already
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance of Excel?
If Err.Numer<>0 Then 'Excel not running, create new instance
 On Error GoTo ErrHandler
 Set xlx = CreateObject("Excel.Application")
 xlx.Visible = False 'or true
 xlx.ScreenUpdating = False 'or true
 xlx.DisplayAlerts = False 'to stop prompt when saving altered file
 Set xlw = xlx.Workbooks.Open(svChartPath,,,,"Password")

 xlw.Save
 xlx.ScreenUpdating = True
 xlx.DisplayAlerts = True
 xlx.Quit
End If

ExitHere:
'clean up stuff
Set xlw = Nothing
Set xlx = Nothing

ErrHandler:

 

[subhanak]

Hi,
Thank you very much for your assistance.
xlx.DisplayAlerts = True/False is generating this error: Method or data member not found.

Do you know what I am missing? Please.

Thanks

 

[Micron]

It can only be one or the other, not True/False. I do not see where I posted anything that looks like what you just posted.

 

[subhanak]

Sorry for the confusion, I meant

xlx.DisplayAlerts = True
xlx.DisplayAlerts = False

Generates this error: Method or data member not found.

 

[Micron]

Hard to pin down without seeing all of the code. Please post what you have.