Import Excel(2003) into Access(2003)

[bcoates]

I have a Excel workbook with many worksheets, each worksheet corresponds to a table in database. How do i write a vbscript that would sit on the desktop that imports the worksheets to the tables and overwrites the exisitng data?

 

[barny2006]

' choose table name here depending on workstation or 
' from a text file that contains the workstation name and
' the table name. or you can put them in an array
' connect to the database

db_path = "c:\acces_database_name.mdb"  
Set objConnection = CreateObject("ADODB.Connection")
 
objConnection.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source = " & db_path

dim table_name, xcl_file_path, sql_string
table_name = "some_table_name"
xcl_file_path = "c:\xcl_files\your_xcl_file_name.xcl"

sql_string = "SELECT * INTO " & table_name & " FROM [Excel 8.0;HDR=Yes;" & _         
        "Database="& xcl_file_path & "].[Sheet1$]"               
objConnection.Execute sql_string