Need a Vbscript to import excel into access

Asked By 140 points N/A Posted on -

I have been working to migrate Data from ERP Software to Legacy System which is based on C# or I have all data exported into excel format but it’s not possible to migrate all data in individually with different databases migration issues like issue of wrong data type format or any data losses, I want to do this through VBscripting. Is there any code for Vbscript to import excel into access?

Best Answer by Rubi Sharlene
Best Answer
Best Answer
Answered By 0 points N/A #180120

Need a Vbscript to import excel into access


Hello Clinton,

With VBScripting migrate data from an Excel sheet to Access is a straight forward task. I have write the following VBScript code to read data from Excel file:

Code: Public Sub import_from_excel()
Dim conn_excel As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=c:
eport_10102006.xls;Extended Properties=Excel 8.0")
sqlcommand = String.Empty
sqlcommand = "Select * from ['Multiple PN$']"
Dim excel_adaptor As New OleDbDataAdapter(sqlcommand, conn_excel)
Dim excel_DS As New DataSet
For testing purposes
Dim excel_DT As New DataTable
Dim f_ed As New excel_data
f_ed.dg_excel.DataSource = excel_DT
End Sub

Thank you.

Rubi Sharlene

Answered By 0 points N/A #180119

Need a Vbscript to import excel into access


Excel contain an ODBC driver, so you can access it just like you would a database.
<BR><BR>Alternatively, save the XLs as csv and parse it at in asp/vbscript.

Or you can import records by using this command:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Employees", "C:ScriptsEmployees.xls", True, "A1: F25"
Or use the attach script.This code snipped assumes first field and second field are text data types.

Hope you will enjoy it.
Thanking you
Franke Mary

Login/Register to Answer

Related Questions