Writing to an existing Excel file

[mlynch199]

I am currently trying to write the contents of an SQL table to an existing Excel database. I can use CreateTextFile and this will open a new Excel project which works fine. I assumed OpenTextFile could be used with an existing file which contained macros but this does not seem to work.
Any help apprreciated

 

[QatQat]

I usually use a piece of VB6 in a dll to achieve that.

I found an article that got me started some time ago at freevbcode.com.

I do not remember the exact article but you can do a search in google this way

"write to existing excel site:

http://www.freevbcode.com"

You will probably need to modify the code so that you can pass your values to public variables in the dll.


QatQat


Life is what happens when you are making other plans.

 

[Sheco]

Tip:
When using the Excel via ActiveX/COM on your web server be very careful to close the application and release the object references on every page. Otherwise you may very well end up with multiple copies of Excel running in memory on your web server machine. You won't see the icons for Excel on the task bar but you can see them in the Task Manager.

IIS doesn't do as good of a job cleaning up orphan Excel objects as it does with ADO and FSO objects that go out of scope.

 

[theniteowl]

Sheco, do you know of a way on the server side to close out those application instances?
So far I have had to restart the server when they occur in order to close them out. They are not linked to IIS directly so stopping and restarting IIS does not clear them out.

Is it possible to close out application instances running under a different account name? Task Manager just tells you access is denied.

Paranoid? ME?? WHO WANTS TO KNOW????

 

[Sheco]

Perhaps if you wrap it up in a ActiveX DLL like QatQat suggested you could use MTS/COM+ to kill it.

Also you could have the public properties/methods of the object update a module-level date variable every time they were called and combine that with a nonblocking timer created with Win32API function WaitForMultipleObjects... this could be used as a sort of auto-kill after a given period of inactivity.

 

[QatQat]

Hi Sheco,

to my knowledge, after you call the workbook.save method in VB6, and as long as you destroy your VBscript object

set yourExcelDoc = nothing

you will be left with an excel file on the filesystem and nothing in the server memory.

I would post come code but this is becoming VB6 forum's matter.

Anyway, if you are interested, reply to this and I will post it in a few hours as in Johannesburg is bed time now.

CHeers Guys


QatQat

Life is what happens when you are making other plans.