Macro to Open then Close Excel

[Stone123]

Hello,


I'm an Access Novice, and I'm trying to create a macro or grou
of macros to open a Excel spreadsheet, save it, and then close Exce
and the spreadsheet. I've tried using the SendKey option, but it end
up closing Accesss instead of Excel. Any help would be muc
appreciated.


Thanks,
Ston

 

[ChrisBat]

hey there stone - here's what i've got that works:

Dim ex as new excel.application
dim wrkbk as excel.workbook
dim xlApp as Excel.application
set xlApp = Excel.Application
Set wrkbok = ex.Workbooks.Add

ex.Visible = true
ChDir "C:\______________________" ' drive and path of file
Workbooks.Open Filename:= "C:\_________" ' drive, path
' and filename
' of file
ActiveWorkbook.SaveAs Filename:= "C:\____________"
' drive, path and filename that you want to save as

continue on with the code from here, until you've entered
everything else that you want to occur between saving and
closing excel.

to close excel, i've used:

ex.Quit
Set ex = nothing
wrkbk.close
set wrkbk = nothing
xlApp.Quit
set xlApp = nothing

****Please note, I have trouble with closing the excel
process this way - test it with your code a couple of
times, after each time go into Task Manager under the
processes tab, and look for EXCEL.EXE - I cannot figure
out how to get rid of this, and you will have to select it
and then "END PROCESS" to close Excel completely. If you
don't and try and open the file, you will get a sharing
violation error. If you try and open another file in
Excel, the application will open but your file will not.
Very frustrating, and if you figure out what to do, please
let me know.
Hope this helps!
ChrisBat

 

[Bryan Reich [MSFT]]

Chris,
Why are you creating the following variable?

dim xlApp as Excel.application
set xlApp = Excel.Application

You create this excel instance and then never use it. Also, you should close
your workbook before you quit your excel instance. So your closing code
should look as follows:

wrkbk.close
set wrkbk = nothing
ex.Quit
Set ex = nothing
--
Bryan
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

hey there stone - here's what i've got that works:

Dim ex as new excel.application
dim wrkbk as excel.workbook
dim xlApp as Excel.application
set xlApp = Excel.Application
Set wrkbok = ex.Workbooks.Add

ex.Visible = true
ChDir "C:\______________________" ' drive and path of file
Workbooks.Open Filename:= "C:\_________" ' drive, path
' and filename
' of file
ActiveWorkbook.SaveAs Filename:= "C:\____________"
' drive, path and filename that you want to save as

continue on with the code from here, until you've entered
everything else that you want to occur between saving and
closing excel.

to close excel, i've used:

ex.Quit
Set ex = nothing
wrkbk.close
set wrkbk = nothing
xlApp.Quit
set xlApp = nothing

****Please note, I have trouble with closing the excel
process this way - test it with your code a couple of
times, after each time go into Task Manager under the
processes tab, and look for EXCEL.EXE - I cannot figure
out how to get rid of this, and you will have to select it
and then "END PROCESS" to close Excel completely. If you
don't and try and open the file, you will get a sharing
violation error. If you try and open another file in
Excel, the application will open but your file will not.
Very frustrating, and if you figure out what to do, please
let me know.
Hope this helps!
ChrisBat

-----Original Message-----

Hello,


I'm an Access Novice, and I'm trying to create a macro or group
of macros to open a Excel spreadsheet, save it, and then close Excel
and the spreadsheet. I've tried using the SendKey option, but it ends
up closing Accesss instead of Excel. Any help would be much
appreciated.


Thanks,
Stone


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

.