Open/Close Excel from Access 1

[ajking]

I have a project where I take an excel form with three sheets and import the data into access. I have got it working using:
DoCmd.TransferSpreadsheet acImport, 8, "tbTechnical", "C:\CNS Excel Import\Email_CNS.xls", True, "ExportTechnical"
DoCmd.TransferSpreadsheet acImport, 8, "tbScan", "C:\CNS Excel Import\Email_CNS.xls", True, "ExportScan"
DoCmd.TransferSpreadsheet acImport, 8, "tbEmail", "C:\CNS Excel Import\Email_CNS.xls", True, "ExportEmail"

However the first step at the moment is to manually open the excel form so the transfer can take place and then close the excel form manually.
I have been trying to automate the process with little success. If anyone out there can help I would be most grateful as I am down to the last hundred hairs on my head ;-). TIA


'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[Remou]

You can use automation to open and close a number of applications, including Excel, however, it should not be necessary to open Excel to use TransferSpreadsheet.

 

[ajking]

Hi Remou
If the excel form is not open I get an error message: "error 3274 - external table is not in the expected format"

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[Remou]

What version of Excel are you importing? I would imagine that it is the 8 (acSpreadsheetTypeExcel8 and 9) that is causing the problem.

 

[ajking]

Excel 2003 sp3. As I said, the import works fine and my access form populates correctly. I just want to automate the opening of the workbook and to close it after the import.

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[Remou]

Dim xl As Excel.Application

Set xl=CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\docs\ltd.xls")
xl.Quit

I do not recall anyone, ever, having to open Excel to import a spreadsheet via Transferspreadsheet.

 

[ajking]

That code worked fine for the first time but when I tried it again a windows type popup appeared which had a title -" 'file now available' with the following text in the body:
'Email_CNS.xls' is now available for editing
Choose Read-Write to open it for editing"
I shut the database down and opened it again with the same result i.e. the popup 'pops up'. If I cancel the 'edit' choice, the excel file comes to the top and I have to close it manually

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[Remou]

Add to the end:

Set xl=Nothing

 

[ajking]

Remou, you are a star! many thanks!

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980