import and update file in my form

G

GGill

I have in my form command button by clicking on button i will import from
excel file data to tblImport table. The import part is working. The problem i
have, sence my excel file has 3 worksheets and each worksheet has name. So
when i import these 3 worksheets to same table i will need also update field
'TABNAME'.

It will work if i have 3 command buttons for each worksheet.
Please let me know to make work using only 1 command button.

Thank you for helping.
--------------
strPath = "C:\File\Test.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImport",
strPath, True, "AAA!"
CurrentDb.Execute ("UPDATE tblImport SET tblImport.[TABNAME] = 'AAA'"),
dbFailOnError

'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImport",
strPath, True, "BBB!"
'CurrentDb.Execute ("UPDATE tbl_Import SET tblImport.[TABNAME] = 'BBB'"),
dbFailOnError

'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImport",
strPath, True, "CCC!"
'CurrentDb.Execute ("UPDATE tblImport SET tblImport.[TABNAME] = 'CCC'"),
dbFailOnError
 
B

Barry Gilbert

Create two temporary tables for importing worksheets 2 and 3. After all three
worksheets are imported, append the records from the two temp tables into
tblImport (using an append query) and modify the TABNAME field in the process.

Barry
 
Top