Open file

[Guest]

Hiya

Some of you may be aware of the automation problems that I have posted before, if not don't worry. I'm trying a few different ways to combat my problem and need help with the current method I'm trying

I want to try and open an Excel workbook, paste a recordset in 1 sheet then close it. Then get a new recordset re-open the workbook, go to the next sheet and then close it

Before you ask, the reason I want to close and re-open it is that if I leave it open it is causing an automation error (server threw an exception). I want to see if opening the workbook fresh on every recordset (there are 5) will get round the prob. Here is the code I am using

Set XLObject = GetObject(TargetFile
GetPatReprst 'This populates the recordse

With XLObjec
.Application.Visible = Tru
.Parent.Windows(1).Visible = Tru
.Sheets(patientreport).Range("A9").CopyFromRecordset rstdat
.Sheets(patientreport).Range("N11").EntireColumn.Delet
End Wit

XLObject.Close SaveChanges:=Tru

rstdata.Clos

It works fine for the first lot, but then throws an error when trying to do the following code for the second time: Set XLObject = GetObject(TargetFile

Can I use different code to open the workbook in 2nd-5th recordsets or should I use a different close method?

Many thanks

Basil

 

[JohnFol]

The code listed does not cover the opening and closing of the file.

I would suggest htis is probably better placed with an Excel NG rather than
Access . .

Hiya,

Some of you may be aware of the automation problems that I have posted

before, if not don't worry. I'm trying a few different ways to combat my
problem and need help with the current method I'm trying.

I want to try and open an Excel workbook, paste a recordset in 1 sheet

then close it. Then get a new recordset re-open the workbook, go to the next
sheet and then close it.

Before you ask, the reason I want to close and re-open it is that if I

leave it open it is causing an automation error (server threw an exception).
I want to see if opening the workbook fresh on every recordset (there are 5)
will get round the prob. Here is the code I am using:

Set XLObject = GetObject(TargetFile)
GetPatReprst 'This populates the recordset

With XLObject
.Application.Visible = True
.Parent.Windows(1).Visible = True
.Sheets(patientreport).Range("A9").CopyFromRecordset rstdata
.Sheets(patientreport).Range("N11").EntireColumn.Delete
End With

XLObject.Close SaveChanges:=True

rstdata.Close


It works fine for the first lot, but then throws an error when trying to

do the following code for the second time: Set XLObject =
GetObject(TargetFile)

Can I use different code to open the workbook in 2nd-5th recordsets or

should I use a different close method??