Send Access Data to Excel - Please ignore my other post. Newsreader went berserk

[Richard Choate]

I'm going bananas! My Access code sets up a new instance of Excel and a new
ADO connection via the following code, after I dim the variables:

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";" & "Jet OLEDBatabase Password=XXXXX"

' Open recordset based on Orders table
rst.Open mySQL, cnt

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.workbooks.Open("G:\Path\Path2\Path3\ExcelFile.xls")
Set xlWs = xlWb.Worksheets("Data")

Then, I have code to transfer data and massage it a little bit, and then I
close my references with this:

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

But then, when I try to open those excel files to see if everything looks
OK, they are locked for editing and the instances of Excel are still open. I
end up having to use the task manager to shut them down. Where did I go
wrong ???

Richard Choate

 

[Rob Bovey]

Hi Richard,

Please don't cross-post to so many newsgroups. Your problem is that you
are starting Excel in your code but you are not shutting it down when you're
finished with it. Add the changes shown below and your problem should be
solved:

' Release Excel references
Set xlWs = Nothing
xlWb.Close False ''' Close the workbook.
Set xlWb = Nothing
xlApp.Quit ''' Shut down Excel.
Set xlApp = Nothing

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Richard Choate]

Rob,
Thanks for the suggestion. I agree that cross-posting isn't great and tends
to annoy some people, but in this case I was in a huge hurry to find a
solution because my client is getting the read-only message when he tries to
open Excel and I feel the need to get it fixed quickly. I didn't think it
would cause any harm today.
Thanks again,
Richard

I'm going bananas! My Access code sets up a new instance of Excel and a new
ADO connection via the following code, after I dim the variables:

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";" & "Jet OLEDBatabase Password=XXXXX"

' Open recordset based on Orders table
rst.Open mySQL, cnt

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.workbooks.Open("G:\Path\Path2\Path3\ExcelFile.xls")
Set xlWs = xlWb.Worksheets("Data")

Then, I have code to transfer data and massage it a little bit, and then I
close my references with this:

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

But then, when I try to open those excel files to see if everything looks
OK, they are locked for editing and the instances of Excel are still open. I
end up having to use the task manager to shut them down. Where did I go
wrong ???

Richard Choate

 

[Douglas J. Steele]

Even if it's an emergency, and you feel a cross-post is appropriate, you
should still pick relevant groups. Your question has nothing to do with
macros or activexcontrols, for example.

 

[Richard Choate]

Look...I thought it did. I have had enough of people trying to be newsgroup
cops. I already acknowledged that it wasn't a great idea, OK ? So knock it
off already ! I don't need multiple people bitching about something like a
little girl in grade school. I was using ADO in my code but not macros. At
the time, I temporarily forgot that Access sees macros differently than
Excel, where all VBA code is considered "macro code", so it was another
screw-up. There, I'm guilty ! So roast me !

I've always had great respect for you as somebody who would help people and
not be petty. What is wrong with you today? You know I don't come in here
every day and ask people to do my projects for me, and cross post all over
the place, and never try to help anybody else, or have a bad attitude, or be
ungrateful for the help I get from you or others. I did have an emergency
and I overdid it. OK, there you have it. Does anybody else want to come and
pile on now????
Richard Choate


Even if it's an emergency, and you feel a cross-post is appropriate, you
should still pick relevant groups. Your question has nothing to do with
macros or activexcontrols, for example.

 

[PC]

Deep breaths - in through the nose, out through the mouth!!