Excel Object Problems

Question

I currently have a database which has an export feature. This export feature exports a ton of Financial data to an Excel Spreadsheet. This is working well except for one problem.

Once the procedure has finished, if the user then goes to export another project. An automation error Occurs and that export doesn't work.

If the user first closes access then opens it again, they can do another export, but it fails once again if they attempt a second one.

Now I know everyone is probably saying, "Well you haven't closed your objects properly". So here is what code I am using to declare the objects

Dim appExcel As Excel.Application
Dim PhaseWB As Workbook
Dim PhaseWS As Worksheet

' Here is what I am using to Open them

Set appExcel = CreateObject("Excel.Application")
Set PhaseWB = appExcel.Workbooks.Open(SSPath, , False, , , , True) 

Set PhaseWS = PhaseWB.Sheets("Sheet1")     
PhaseWS.Copy before:=Worksheets(1)  ' <-- This is where the problem is on the second export.
Set PhaseWS = PhaseWB.Sheets(1)

' When I have finished with the objects this is how I am closing them

PhaseWB.Close SaveChanges:=True
appExcel.Quit
Set appExcel = Nothing
Set PhaseWB = Nothing
Set PhaseWS = Nothing

Does anyone see anything wrong with this? Is there a better way to release Objects/Variables from memory? Any Help anyone can offer would be greatly appreciated.

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Excel Object Problems
Date: 3 June 1999 21:36

Chris,

This code works - find the difference with yours ;-) (hint: the difference is only in one word):

  Dim appExcel As Excel.Application
  Dim PhaseWB As Workbook
  Dim PhaseWS As Worksheet

  Set appExcel = CreateObject("Excel.Application")
  Set PhaseWB = appExcel.Workbooks.Open(SSPath, , False, , , , True)
  Set PhaseWS = PhaseWB.Sheets("Sheet1")
  PhaseWS.Copy before:=PhaseWB.Worksheets(1)
  Set PhaseWS = PhaseWB.Sheets(1)
  PhaseWB.Close SaveChanges:=True

  appExcel.Quit
  Set appExcel = Nothing
  Set PhaseWB = Nothing
  Set PhaseWS = Nothing

But I'd better write it this way (more difference):

  Dim appExcel As Excel.Application
  Dim PhaseWB As Excel.Workbook
  Dim PhaseWS As Excel.Worksheet

  Set appExcel = CreateObject("Excel.Application")
  Set PhaseWB = appExcel.Workbooks.Open(SSPath, , False, , , , True)
  Set PhaseWS = PhaseWB.Worksheets("Sheet1")
  PhaseWS.Copy before:=PhaseWB.Worksheets(1)
  Set PhaseWS = PhaseWB.Worksheets(1)
  PhaseWB.Close SaveChanges:=True
  Set PhaseWS = Nothing
  Set PhaseWB = Nothing

  appExcel.Quit
  Set appExcel = Nothing

HTH,
Shamil