Writing to Excel from Access 2000

[SteveM]

I've done this before but for some reason I can't now. Here is my code to
write a recordset to Excel.
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection

Dim xApp As Excel.Application
Dim xWkbk As Excel.Workbook
Dim xSheet As Excel.Worksheet
Set xApp = New Excel.Application
Set xWkbk = Excel.Workbooks.Add
Set xSheet = xWkbk.Worksheets(1)

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly (strSQL contains a
valid query)

xSheet.Rows("1:1").Select
With xApp.Selection.Interior (this is where the error occurs)
.ColorIndex = 15
.Pattern = xlSolid
End With

I tried to add only the pertinent code. Any way this will work every other
time. I get the "Object variable or With block variable not set" Run-time
error 91.

What am I doing wrong?

Steve

 

[Klatuu]

I am not positive this will work, but try:

xSheet.Rows("1:1").Select
With xApp.Selection
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With

 

[Alex White MCDBA MCSE]

A tip here go into excel record a macro, do some formatting of the
spreadsheet, stop the recording, look at the code in the macro, great for
learning how to automate word/excel.

 

[SteveM]

Thanks but that didn't do it.

I am not positive this will work, but try:

xSheet.Rows("1:1").Select
With xApp.Selection
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With

 

[SteveM]

Thanks Alex, I try it. The wierd thing is it works every other time.

 

[Alex White MCDBA MCSE]

Hi Steve,

Aha, I have had this problem, works the first time and then not the second,
something to do with referencing the wrong columns and excel then getting
very confused, when you say first time and second time is this within the
same procedure e.g. the same spreadsheet or the next spreadsheet to be
opened, I had some real funnies with this, if you continue to have the
problem post back here and I will dig out my code and have a good look at it
to try and job my memory.

 

[SteveM]

Thanks Alex,
This happen with I call this procedure every EVEN time. It's a control on a
form and it is suppose to send the data in a textbox to a spreadsheet. Same
data each time. Works once, not the second then works the third...

Interesting thing is, I break the code right before the error(the even
times) and look at excel and it is just blank, no spreadsheet or anything. So
I stop the procedure and try again. Then later when I open another
spreadsheet, there's my phantom spreadsheet. When i tried to reboot, Window
could shut down because of these file being opened.

Any help would be appreciated. Weird thing is I've used this same code
before and it worked everytime.

Puzzeled,
Steve

 

[Alex White MCDBA MCSE]

Hi Steve,

It's something to do with excel not closing down properly, I have had a look
at my code and this is what solved it for me now for this to work excel has
to be early bound

Dim objXcel As New Excel.Application
On Error Resume Next
objXcel.Quit
Set objXcel = GetObject(, "excel.application")
If Err Then
Err = 0
Set objXcel = CreateObject("excel.Application", "")
End If

the idea is before the object is even created I am killing excel then
creating it, do ask me why this worked but I was pulling my hair out and
tried everything, this is not good code but it works, it kills any remenace
of excel beforehand, if you check in task manager after the first pass I
think excel.exe is still in memory give it a go.

post back here if it still does not work.

 

[SteveM]

Hey Alex you were correct! Excel was remaining in memory. Nothing I could do,
short of quiting Access would shut it down. Your code worked.

Thanks for your help. So is this an Access 2000 problem?

Steve

 

[Alex White MCDBA MCSE]

Hi Steve

No its excel problem via automation.... word has a different set of problems
so cannot be access.

glad it fixed it, strange the things you have to do to get some things to
work, "close it before opening it." mmmm