Manipulating Excel Using Access 1

[Sech]

I am having real problems trying to get a report creation process in my database to work correctly. The process is very complex. Basically what it does is export a number of reports to Excel files, and as it does this it adds them to a template Excel file which has a Main Menu linking to each of the reports. It then deletes the original report files, and saves the template to a relevant location. This seems to work correctly when run once, but if I try running multiple report batches without first exiting the database, it then does not add them to the template file correctly. So I assume it is because the Excel object is not shutting down correctly, so when I re-run the process it fails.

The code I have when first opening the Excel template invisibly is as follows:


'Start a new excel application in the background
Set oxlApp = New Excel.Application

'Turn off Excel warnings
oxlApp.Application.DisplayAlerts = False

'Open the report template spreadsheet
Set oxlTemplateBook = oxlApp.Workbooks.Open("C:\GroupedReportTemplate.xls")


Once I have finished adding the reports to the invisible template file, the finishing code which is designed to add finishing touches to the template, save it and then completely close the Excel objects is as follows:


'Open Main Menu sheet of the template
Set oxlWorksheet = oxlTemplateBook.Sheets("Main Menu")

'Disable gridlines and tabs
oxlApp.ActiveWindow.DisplayGridlines = False
oxlApp.ActiveWindow.DisplayHeadings = False
oxlApp.ActiveWindow.DisplayWorkbookTabs = False

'Save new reports spreadsheet to relevant directory and filename
oxlTemplateBook.SaveAs FileName:=strMultiDir & gstrSchedule & " Reports " & Format(Date, "ddmmyy") & ".xls"

'Close the template workbook
oxlTemplateBook.Close False

'Turn on Excel warnings
oxlApp.Application.DisplayAlerts = True

'Quit the Excel application
oxlApp.Quit

'Clear vars
Set oxlWorksheet = Nothing
Set oxlTemplateBook = Nothing
Set oxlApp = Nothing


I know that the Excel objects do not completely close, as if I try opening the created template file without first exiting the database, it is hidden and cannot be displayed correctly. Does anyone know how to get the objects to close completely so the process can be run multiple times without causing errors?

 

[KenReay]

Hi

is this by change Office97?

I recall a similar propblem with Office97, but cannot recall the details, have you tried a search of the microsoft KB site

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

I've seen a thread recommending using the End statement at the end of the sub.

[tt] 'Clear vars
Set oxlWorksheet = Nothing
Set oxlTemplateBook = Nothing
Set oxlApp = Nothing
End
End Sub[/tt]

This should release all variables (which of course could have some implications on other code, too, but as a last resort, perhaps?).

Roy-Vidar

 

[Sech]

Nice one Roy, the End command appears to sort it out. But do you or anyone else know the "proper" way to quit from Excel using code?

 

[wizarddrummer]

Greetings,

I have manipulated sheets from within a database and have used the same closing conventions (without the End) quite successfully.

I have also gathered info from the database within a spreadsheet.

The following code is an example of a combination of the first two.

The code gets information from a form.
opens a sheet without making it visible, but if you need to do that it's easy.
and calls a function in the sheet to populate itself (the real live version it fills up a about 25 seperate sheets of information.

Private Sub cmdUpdateSpreadsheet_Click()
Dim strFile As String
Dim xlApp As Excel.Application  ' Declare variable to hold the reference.
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim strAppPath As String
Dim strMsg As String
Dim strErrMsg As String
Dim dblCellB11 As Double
Dim dblCellB12 As Double
    
    Me.cmdPrintSheet.Enabled = False
    Me.cmdPreviewSheet.Enabled = False
  
    strMsg = "Please enter a value for cell: "
    If IsNull(Me.txtB2) Then
        MsgBox strMsg & "B2"
        Exit Sub
    End If
    If IsNull(Me.txtB3) Then
        MsgBox strMsg & "B3"
        Exit Sub
    End If
    If IsNull(Me.txtB11) Then
        MsgBox strMsg & "B11"
        Exit Sub
    End If
    If IsNull(Me.txtB12) Then
        MsgBox strMsg & "B12"
        Exit Sub
    End If
    
    On Error GoTo err_cmdUpdateSpreadSheet
    strMsg = vbCrLf & "Please enter a number in Field B11"
    dblCellB11 = CDbl(Me.txtB11)
    strMsg = vbCrLf & "Please enter a number in Field B12"
    dblCellB12 = CDbl(Me.txtB12)
    strMsg = ""
    
    Set xlApp = CreateObject("excel.application")
    
    strAppPath = Application.CurrentProject.Path
    strFile = strAppPath & "\SpreadSheetTest1.xls"
    Set xlWB = xlApp.Workbooks.Open(strFile, , False)
    Set xlWS = xlWB.Worksheets("Sheet1")
    xlWS.[B2] = Me.txtB2
    xlWS.[B3] = Me.txtB3
    xlWS.[B11] = dblCellB11
    xlWS.[B12] = dblCellB12
    
    xlWB.Save
    xlApp.DisplayAlerts = False
    
    
    xlApp.Run "UpdateInmateInformation", Date, Time()
    
    xlWB.Close
    xlApp.Quit  ' When you finish, use the Quit method to close
    Me.cmdPrintSheet.Enabled = True
    

exit_cmdUpdateSpreadSheet:
    'release all variables
    Set xlApp = Nothing ' the application, then release the reference.
    Set xlWB = Nothing 'workbook
    Set xlWS = Nothing 'worksheet
Exit Sub

err_cmdUpdateSpreadSheet:
    strErrMsg = Err.Description & strMsg
    MsgBox strErrMsg, vbCritical, "Entry Error!"
    Resume exit_cmdUpdateSpreadSheet
End Sub

The differences I see with respect to the code I have seen here is that I use CreateObject("excel.application") instead of the new keyword. Years ago ( and this was lots of years ago) I seem to remember that there was some internal difference with respect to the assembler code between the two methods of instantiating an object and what happened internally with memory allocation and garbage collection.
The second difference is that I call a function in the spreadsheet to pull all of the information over to it.

The third difference is I save the sheet before I quit.

The fourth difference is that i have a sheet that is open that is also closed at the end.

I have never seen any examples or explanations of why and end statement would be necessary, perhaps it's another bug in the long list of Microsoft's endless list...
I hope this helps.

Regards,
wiz

 

[Sech]

Nice one Wiz, Ill try this out with my code and see if I can get it to work...

 

[vedicman]

Here's a different twist.....

I've had greater success PULLING access data into XL as opposed to PUSHING access date into XL.

Let me know if you'd like more details.

Franco

 

[CajunCenturion]

I am not a fan of using the End statement at all for a couple of reasons.

One, it clears the sympton, but it does not solve the problem. Terminating execution with and End statement also hides potential memory leaks, which is quite likely in this case because there is a hanging reference somewhere that has not been properly disposed of.

Second, it can only be used if those cases where the termination (in this case of Excel) is congruent with the termination of the Application. If you still have other processing to do, End is not an option.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein