I have been working to migrate Data from ERP Software to Legacy System which is based on C# or ASP.net 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?
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
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
excel_adaptor.Fill(excel_DS)
For testing purposes
Dim excel_DT As New DataTable
Dim f_ed As New excel_data
f_ed.Show()
excel_adaptor.Fill(excel_DT)
f_ed.dg_excel.DataSource = excel_DT
'conn_excel.Open()
conn_excel.Close()Â Â Â Â
End Sub
Thank you.
Rubi Sharlene
Need a Vbscript to import excel into access
Hi,
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
