Save and Close Excel using Access2003

[SJW_OST]

Simple question, I hope.

How do I use Access2003 to close an Excel workbook/file/application,
whatever, I want the Excel workbook/file/application saved and closed once I
have finished importing it to Access?
Do I need to switch which application is activated to do this?

Your help is greatly appreciated!

 

[Jeanette Cunningham]

SJW_OST,
If you are using automation to do the import, please post your code so we
can help.

Jeanette Cunningham -- Melbourne Victoria Australia

 

[SJW_OST]

I am using Macros to put this in a procedure. First I have the macro call on
the function below. The following function opens my excel workbook and runs
an Excel auto_open macro.

Public Function OpenPrepNatSum()
'
Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open "H:\Shortcuts&Links\M Y P R O J E C T
S\NationalSummary\PrepareNationalSummary.xls" 'File location
OpenExcel.Visible = True
OpenExcel.Run ("Auto_Open") 'Excel Macro to run
'OpenExcel.Workbooks.Close

End Function

I then have Access, using Macros, import the data into a table.
Now that the data is imported, I want Access to save & close Excel.

Would this work better if the whole thing was in a Module?

I hope this helps.
Again, much thanks.

 

[Jeanette Cunningham]

SJW_OST
to close excel, you need to do the opposite to this line which opens excel
Set OpenExcel = CreateObject("Excel.Application")

the opposite is
Set OpenExcel = Nothing

The line above needs to be the very last line that references OpenExcel.
Do everything else with excel, including closing the workbook before you put
in the line Set OpenExcel = Nothing

You end up with code like this:
--------------------------------
Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open "H:\Shortcuts&Links\M Y P R O J E C T
S\NationalSummary\PrepareNationalSummary.xls" 'File location
OpenExcel.Visible = True
OpenExcel.Run ("Auto_Open") 'Excel Macro to run

'other code here as needed to do the import

OpenExcel.Workbooks.Close
Set OpenExcel = Nothing
End Function

 

[SJW_OST]

That did it, Thank you very much!

SJW_OST
to close excel, you need to do the opposite to this line which opens excel
Set OpenExcel = CreateObject("Excel.Application")

the opposite is
Set OpenExcel = Nothing

The line above needs to be the very last line that references OpenExcel.
Do everything else with excel, including closing the workbook before you put
in the line Set OpenExcel = Nothing

You end up with code like this:
--------------------------------
Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open "H:\Shortcuts&Links\M Y P R O J E C T
S\NationalSummary\PrepareNationalSummary.xls" 'File location
OpenExcel.Visible = True
OpenExcel.Run ("Auto_Open") 'Excel Macro to run

'other code here as needed to do the import

OpenExcel.Workbooks.Close
Set OpenExcel = Nothing
End Function