Access/Excel linking via VBA - Not saving spreadsheet

[Geoff Harrison]

Hi,

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

 

[Keith Wilby]

Hi Geoff,

Dim oMIWorkbook As Workbook
Dim oMIWorksheet As Worksheet

I'd declare these explicitly:

Dim oMIWorkbook As Excel.Workbook
Dim oMIWorksheet As Excel.Worksheet

'oMIWorkbook.Save

This line appears to be commented out. Intentional?

Regards,
Keith.

 

[Geoff Harrison]

Hi Geoff,



I'd declare these explicitly:

Dim oMIWorkbook As Excel.Workbook
Dim oMIWorksheet As Excel.Worksheet

Thanks for that. I've tried this but I end up with an error from the Office
Assistant claiming that I need to repair my installation of Office. Is there
something else I may be missing?

This line appears to be commented out. Intentional?

Yes because I have put in the line that follows which states:

oMIWorkbook.Close True

which should negate the need for an explicit save.

Unfortunately the Workbook.Saved flag never changes to true whether I
explicitly save or implicitly save on the .Close method. This of course means
that I never get to a point where my changes to the spreadsheet are saved.