object question

[dagoose]

Hi All,

I have a problem with an MS Access app i have. It opens an excel workbook and populates it with Information. This works great but when I choose the button to run the process again it crashes with "run-time error 462:" I think this is related to not closing the object correctly:

Dim CHApp as Object
Set CHApp = CreateObject("Excel.Application")

What I need to do is check to see if the object already exists before I re-run the process so that it does not try to re-create the object.

Any Ideas on how best to achieve this.

Cheers

[si_the_geek]

Why dont you just close the object when you finish with it? Leaving it open will just waste memory and processor time.

As it is Excel, all you need to do is:

CHApp.Quit     'you dont need this line if you want to keep Excel open & visible
Set CHApp = Nothing

[alex_read]

If not (CHApp is nothing) then
    Set CHApp = CreateObject("Excel.Application")
Else
    ' Excel object already exists & has been set
End If

[dagoose]

many thanks for your replies, i have tried closing the object and adding the loop but I still get the same, it only runs the first time i select the button, I have included the code, can anyone spot anything silly that I have done.

Cheers


'Create the recordset
Dim dbCH As Database, rsCH As DAO.Recordset, rsCH2 As DAO.Recordset
Dim CHApp As Object
Dim Irec As Integer

Set dbCH = CurrentDb
Set rsCH = dbCH.OpenRecordset("Excel_Transfer", dbOpenDynaset)

Set CHApp = CreateObject("Excel.Application")
CHApp.Visible = True

Dim chwb As Excel.Workbook
Dim chws1 As Excel.Worksheet

If rsCH.EOF Then Exit Sub

Irec = 1

With rsCH

Set chwb = Excel.Workbooks.Add

'Add header page details
Set chws1 = Excel.Worksheets.Add

chws1.Range("A1").Value = "CRBD"
chws1.Range("A3").Value = "Header:"
chws1.Range("A4").Value = "Message Identifier"
chws1.Range("A4").Font.ColorIndex = 3
chws1.Range("C4").Value = ![MessageIdentifier]
chws1.Range("A5").Value = "Version"
chws1.Range("A5").Font.ColorIndex = 3
chws1.Range("C5").Value = ![Version]
chws1.Range("A6").Value = "Transaction type"
chws1.Range("A6").Font.ColorIndex = 3
chws1.Range("C6").Value = ![Transaction_Type]
chws1.Range("A7").Value = "Transaction Date & Time "
chws1.Range("A7").Font.ColorIndex = 3
chws1.Range("C7").Value = ![Transaction_Date]
chws1.Range("C7").NumberFormat = "DD/MM/YYYY"
chws1.Range("D7").Value = ![Transaction_Time]
chws1.Range("D7").NumberFormat = "HH:MM:SS"
chws1.Range("A8").Value = "Session Reference"
chws1.Range("A8").Font.ColorIndex = 3
chws1.Range("C8").Value = ![Session_Reference]
chws1.Range("A9").Value = "Sender Code"
chws1.Range("A9").Font.ColorIndex = 3
chws1.Range("C9").Value = ![Sender_Code]
chws1.Range("A10").Value = "Recipient Code"
chws1.Range("A10").Font.ColorIndex = 3
chws1.Range("C10").Value = ![Recipient_Code]
chws1.Range("A13").Value = "Trailer Record:"
chws1.Range("A14").Value = "Trailer Identifier"
chws1.Range("A14").Font.ColorIndex = 3
chws1.Range("C14").Value = ![Trailer_Identifier]
chws1.Range("A15").Value = "Number of Records"
chws1.Range("A15").Font.ColorIndex = 3
chws1.Range("C15").Value = ![Number_of_records]

chws1.Columns.AutoFit
chws1.Name = "Header and Footer"

Do Until .EOF
Set chws1 = Excel.Worksheets.Add

chws1.Range("A6").Value = "Message Number"
chws1.Range("A6").Font.ColorIndex = 3
chws1.Range("C6").Value = ![Message_Number]
chws1.Range("C34").Value = ![Status]
chws1.Range("D34").Value = ![Status]

chws1.Columns.AutoFit
chws1.Name = Irec
Irec = Irec + 1

.MoveNext
Loop
Irec = Irec + 1

Excel.Application.DisplayAlerts = False
chwb.Worksheets("Sheet1").Delete
chwb.Worksheets("Sheet2").Delete
chwb.Worksheets("Sheet3").Delete
Excel.Application.DisplayAlerts = True

End With

Set rsCH = Nothing
Set bdCH = Nothing
CHApp.Quit