Need a Vbscript to import excel into access

Asked By clinton sina 140 points N/A Posted on - 01/25/2013

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?

• Status: Open
• Question Views: 1145
• Answer Count: 2
• Vote Up 1 Vote Down

• Answer Accepted: Yes
• Question Category: Visual basic

Best Answer by Rubi Sharlene

Go To Solution

Best Answer

Best Answer

Answered By Rubi Sharlene 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
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

About Rubi Sharlene

Questions
0

Answers
103

Best Answers
31

• Vote Up 0 Vote Down

• Posted on - 02/03/2013
• Question Category: Visual basic

Answered By Franke Mary 10 points N/A #180119

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

About Franke Mary

Questions
3

Answers
102

Best Answers
23

• Vote Up 0 Vote Down

• Posted on - 02/20/2013
• Question Category: Visual basic