Import Excel into Access table.

[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.

 

[Guest]

-----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.
.

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

 

[Jamie Collins]

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

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.

--

 

[Cyndy Grover]

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




.