Opening Excel from Access

[NotOnUrNelly]

Hi

The problem I have having is

I need to import data from an excel spreadsheet, the raw data in the spreadsheet is not ready to fit the table in my database. So I need to run a macro that I created in Excel to delete some columns.

From Access I Can successfully

Open excel
Open correct spreadsheet
Run macro (in access)

This is done by using excel object library

The problems i am having is when I need to save and Close the spreadsheet

Saving/

I save the spreadsheet as the original name with a P on the end which means prepared. At this point if the new name already exists I get, a message saying

The spreadhseet already exist do you want to overwrite.

The answer to this will always be yes

so I could really do without the message apearing.

I then need to close the workbook which I can.

but I also want to completely close excel (which I cant)

The import code is connected to a button.

I am also finding when I choose to everwrite the existing spreadhseet with new one that. Icannot open the spreadsheet without closing access. Strange its like the code with the button is still linked to the spreadsheet..

Maybe I am not closing my function out properly..

The code for my button is hsown below

Private Sub Command0_Click()
'On Error GoTo Err_Command0_Click

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

With oApp
Workbooks.Open Filename:="C:\external.xls"

'******Perform Macro**********

Range("A1").Select
ActiveCell.FormulaR1C1 = Now()
Range("B1").Select
ActiveCell.FormulaR1C1 = "file"
Range("C1").Select
ActiveCell.FormulaR1C1 = "loaded"

End With
Range("J8").Select

With oApp
ActiveWorkbook.SaveAs Filename:="C:\external.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close 'close workbook

'************now need to close excel
End With



'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[yellow]

To stop Excel from prompting you to save the changes use:

APPeXCEL.DisplayAlerts = False

Then save your workbook, then:

APPeXCEL.DisplayAlerts = True

To close Excel use:

If Not (APPeXCEL Is Nothing) Then
APPeXCEL.Quit
End If
Set APPeXCEL = Nothing

This hopefully helps.