Access/Excel linking via VBA - Not saving spreadsheet

[Geoff Harrison]

Hi,

(I think I posted this to the wrong group initially)

I have an Access application that links to a number of spreadsheets via VBA.

The Access VBA opens either a template or an existing spreadsheet as an
object, modifies the content of the spreadsheet and then closes the
spreadsheet before continuing with other functionality.

When the template spreadsheet is opened, the file is modified and saved
correctly. As this is for a monthly extract of data, the requirement is that
mid-month we open an existing file and add to it.

However, when opening an existing monthly extract file the save function
does not work nor can I get the Access VBA to close the spreadsheet.

I've pasted the the relevant parts of the code below:

(The ExcelMonthlyIncidentTemplate variable points to a value in one of the
tables and the value is MITemplate.xls in a suitable file path)

Dim oExcel As Excel.Application
Dim oMIWorkbook As Workbook
Dim oMIWorksheet As Worksheet
Dim sExcelSheetName As String
Dim msgText As String

Set oExcel = New Excel.Application

' work out the name of the spreadsheet and test for its existence
sExcelSheetName = GetDatabaseParameter("EnvDiagramOutputFolder") &
"MonthlyIncident-" & Format(Now, "YYYY.MM") & ".xls"

Err.Clear
On Error Resume Next
Set oMIWorkbook = oExcel.Workbooks.Open(sExcelSheetName, , False)
If Err.Number <> 0 Then
msgText = "Report for this month (" & Format(Now, "mmm") & ") not
yet started." _
& vbCrLf _
& "Create it now?"
If MsgBox(msgText, vbYesNo + vbApplicationModal + vbQuestion,
"Monthly Incident report") = vbYes Then
Set oMIWorkbook =
oExcel.Workbooks.Open(GetDatabaseParameter("ExcelMonthlyIncidentTemplate"), ,
True)
End If
Err.Clear
End If

'oExcel.Visible = True
oMIWorkbook.SaveAs (sExcelSheetName)

Set oMIWorksheet = oMIWorkbook.Worksheets("Header")
oMIWorksheet.Activate
oMIWorksheet.Cells(4, 3).Value = Format(dDate, "'mmmm yyyy") & " (Report
amended: " & Format(Now, "dd/mm/yyyy hh:nn:ss") & ")"
oMIWorksheet.Cells(5, 3).Value = GetCurrentUserName
oMIWorksheet.Cells(6, 3).Value = "'" &
GetDatabaseParameter("DatabaseBackendVersion")
oMIWorksheet.Cells(7, 3).Value = sExcelSheetName

'oMIWorkbook.Save
oMIWorkbook.Close True
oExcel.Quit
Set oExcel = Nothing

Thanks.

geoff