Import Excel into Access table.

  • Thread starter Thread starter Cyndy Grover
  • Start date Start date
C

Cyndy Grover

I need help on coding with vba away to import an Excel
spreadsheet into a temporary table,then run a append query
to get the data into the main table, then delete temporary
table all using vba.
 
-----Original Message-----
I need help on coding with vba away to import an Excel
spreadsheet into a temporary table,then run a append query
to get the data into the main table, then delete temporary
table all using vba.
.
Click to expand...
sub imp excel()
Set db = CurrentDB()

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "tablename", "excel file path&
file name", True, "name of worksheet!"
docmd.runsql "INSERT INTO MAIN TABLE SELECT TEMPTABLE.*
FROM TEMPTABLE;
db.tabledefs.Delete "TEMPTALE"
end sub
 
sub imp excel()
Set db = CurrentDB()

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "tablename", "excel file path&
file name", True, "name of worksheet!"
docmd.runsql "INSERT INTO MAIN TABLE SELECT TEMPTABLE.*
FROM TEMPTABLE;
db.tabledefs.Delete "TEMPTALE"
end sub
Click to expand...

Simpler:

INSERT INTO
MAIN_TABLE
SELECT
*
FROM
[Excel 8.0;HDR=YES;C:\excel_path\excel_file.xls;].[name_of_worksheet$]
;

but with the column list specified, of course.

Jamie.

--
 
Thank You
-----Original Message-----

sub imp excel()
Set db = CurrentDB()

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "tablename", "excel file path&
file name", True, "name of worksheet!"
docmd.runsql "INSERT INTO MAIN TABLE SELECT TEMPTABLE.*
FROM TEMPTABLE;
db.tabledefs.Delete "TEMPTALE"
end sub




.
Click to expand...
 
Back
Top