Problem Killing excel Object...Used to be Access and Excel VBA intermingling

[doctor]

Hey everyone,

I got this button in access that exports data to a existing excel spreadsheet on my desktop. After it does this I need to run a macro to format the newly exported data. I was wondering if I could perhaps refine this process so that after I push the button in access it

1) Will autocreate a blank excel file
2) Export the data to this file [ I have this code already]
3) Open the newly created file
4) Run a Excel "Macro" to format the data [I have the code to format the data, just need to know if and how its possible to execute this code from access cause if we are creating a new excel sheet then there can be no predefinded macros, right?]

So in essence i need to figure out if i can play around with excel functions from within access VBA, If anyone can help me out with this id highly appreciate it.


Thanks

Doc

[Dave Sell]

If you are suggesting to begin this process from Access:

Can you run an .exe from a macro in Access? If so, the you could easily do all this by writing a VB app to manipulate the finished excel spreadsheet.

The Access macro could do its export thing and then run the VB .exe second.

You would need Visual Studio for this.

[doctor]

Yea, thats what im doing now, i got a Access form button that exports the data, and then a macro in excel that formats the data. It takes two steps and i need to formulate it down to one.

[Dave Sell]

What I'm saying is you can take the functionality from your Excel macro and put it in an executable created in VB.

Access could do the export in a macro, and also run the VB executable in the same macro.

Does this help?

[doctor]

Yeah .. Now i see what your saying... sounds like a good plan, how can i go about doing that.. ? Im looking for a 'make exe' in the vba editor but cant find it.. and how would access exec the export macro and the exe i create..

THX

[Dave Sell]

Originally posted by doctor
Yeah .. Now i see what your saying... sounds like a good plan, how can i go about doing that.. ? Im looking for a 'make exe' in the vba editor but cant find it.. and how would access exec the export macro and the exe i create..

THX

As far as I know, there is no way to make a .exe from the VBA editor. You need Visual Studio, and Visual Basic (I use version 6) to make executables.

Dave

[Ecniv]

Cut n paste the Excel code into a new module in Access.
Change the references and obtain the correct ones as required using automation (getobject etc).

No need to go to exe... which yes you can run.


Vince

[doctor]

app2.Quit
Set app2 = Nothing


Is this the proper way to close the application object that i set via


Set app2 = New Excel.Application

Cause after everything is all said and done EXCEL is still a process running in the back ground... why??

[Dave Sell]

First close the Workbook

m_wkbReportBook.Close True, OUTPUTEXCELFILE
    Set m_wkbReportBook = Nothing
    '
    m_appExcel.Quit
    Set m_appExcel = Nothing

I never have a dangling process when this executes properly.

You should be warned that if your program crashes it could leave a dangling Excel process out there. If that occurs, you must kill it before running it again. I have had alot of problems getting my code to work when a dangling Excel proc was out there.

Just be sure to kill all Excel procs before running the code anew.

Dave

[doctor]

m_appExcel is your application object so

m_appExcel.Quit quits the application, right?


and What is OUTPUTEXCELFILE?

[Dave Sell]

Originally posted by doctor
m_appExcel is your application object so

m_appExcel.Quit quits the application, right?

Yes.

and What is OUTPUTEXCELFILE?

A constant like "C:\Temp\report.xls"

[doctor]

How are you initializeing your workbook m_wkbReportBook?

[Dave Sell]

Its not a sheet - its a Workbook:

' Create a new Excel application
    Set m_appExcel = New Excel.Application
    m_appExcel.SheetsInNewWorkbook = 24
    Set m_wkbReportBook = m_appExcel.Workbooks.Add
    m_appExcel.SheetsInNewWorkbook = 3

[doctor]

hmm.. i tried that .. didnt seem to work either... any other

[Dave Sell]

Not sure if this helps, but these are my defs:

Private m_appExcel As Excel.Application
Private m_wkbReportBook As Excel.Workbook

[doctor]

hmmm nope I thought that would help but still didnt kill the excel process... this is getting weired.. ive tried a few different things and none seem to be working .. im all ears to try somthing else if you can think of anything...

[doctor]

http://support.microsoft.com/default...;EN-US;Q317109


Thats the link that describes my problem, basically im using vb in access and need to do some stuff in excel. I make an excel object and then have trouble exiting the app.. the app performs flawlessly BUT when i go to quit it doesnt terminate excel. I tried doing what they suggested but when i get a error "object variable or with block variable not set" for this line..
wkBook = app.Workbooks

what does that mean?

[Dave Sell]

Try:

Set wkBook = app.Workbooks

[doctor]

hmm, now i get a type mismatch??

[Dave Sell]

OK try my code:

Set m_wkbReportBook = m_appExcel.Workbooks.Add

[doctor]

nnnope... oh boy....

[doctor]

sorry should be more specific... it made the error go away but still a NoGo on the killing excel

[Dave Sell]

Well if you are running this code from an Access module there is a possibility that it won't work correctly. It may be your only recourse is to compile your VBA in a VB executable. Is that an option for you?

[doctor]

sure, an executable is def a option for me, how much do i have to modify my current code to pull this off? and do you think you could walk me through or send me a faq or tutorial that would show me how to do this?

BTW, since the last post (sorry should have updated) i got the error to go away BUT now the excel process sits there after program completion. Im sure the sequence of killing the process is right because i checked a few different sources of code. Im hoping that making this a executable may solve my problem.



Thanks!

[Dave Sell]

Im at work so I cant go into too much detail now, but ya the VB code is very similar to the VBA code. The main thing is getting the visual COM object added to your project, and then declareing the objects and so forth.

I will see if I can come up with a breif tutorial tonight.

Dave

[doctor]

OK dave, Thanks a lot for your help btw im playing around with this thing and i set excel to be visible... i see all the data get manipulated and in the end the excell app quits.. i can see it dissapear.. but the process is still there. Dont know if that helps, just an observation. I understand you are doing this on your own free time so whenever youre free to shoot some code my way go for it Ill keep you updated on things just so you know if i have any breakthroughs

Doc

[doctor]

Heey I fixed my problem ... i wasnt appending the excel object to my methods and apparently thats why it was not terminating the process. I am still interested in learning how to make a exceutable of all this, if you are still up for it )

Thanks for your time and effort

-Doc

[Dave Sell]

I'm definately up for it. There was so few documentation on the topic when I started into it, it should be done.

[Dave Sell]

http://www.vbforums.com/showthread.p...hreadid=297109

[doctor]

good stuff!

[Dave Sell]

Oops looks like I overlooked the part about making the VB project into an executable.

Do you know how to do that part? I guess I left that out of the tutorial.

[doctor]

Ah yes, I took it from where you left off, I do know how to make it into a exce, but I didnt once So you may want to include it for ppl who dont know how to do it..

thanks again
-Doc