Cannot import excel sheet

[AT76]

I have an application that exports an access table (ACCESS TABLE 1) to excel. In Excel I do a lot of modifications to the table. Then I would like to export this excel sheet back to an access table (ACCESS TABLE 2). Here is when I'm running into dificulties:

Private Sub modifyTable_Click()

Dim mysql As String
Dim dbCurrent As Database

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

DoCmd.OutputTo acOutputTable, "ACCESS TABLE 1", acFormatXLS, "Spreadsheet.xls", False

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlws As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\temp\Spreadsheet.xls")
Set xlws = xlWB.ActiveSheet

'DO MODIFICATIONS TO EXCEL SHEET!!!

'Export the Excel sheet back to ACCESS (ERROR HAPPENS HERE!)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ACCESS TABLE 2", "C:\temp\Spreadsheet.xls"


xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing

The error message I get is that the app is trying to copy "ACCESS TABLE 1" to "ACCESS TABLE 2". The tables have different # of columns and I get an error message because of this. My question is what Am I doing wrong with importing the Excel Spreadsheet into "ACCESS TABLE 2"?

Thank you!

 

[PHV]

I strongly suggest to close the workbook and quit the excel app before importing the worksheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

I tried doing the same to a local table and it worked. My question then is why can't I do this to a Linked Table?

 

[AT76]

I figured out how to do it with a linked table:

Example:

mysql = "INSERT INTO [MS Access;DATABASE=path].[linkedTableName] SELECT * FROM tablename"

Thanks!