Open file

  • Thread starter Thread starter Guest
  • Start date Start date
G

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("N1:P1").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
 
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 . .


Basil said:
Hiya,

Some of you may be aware of the automation problems that I have posted
Click to expand...
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
Click to expand...
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
Click to expand...
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("N1:P1").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
Click to expand...
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
Click to expand...
should I use a different close method??
 
Back
Top