Importing Excel spreadsheet into mssql via Visual Basic 6

By manojgadiyar ·

I am new to vb6. I have just started programming in vb6. Can anyone please explain me in detail as to how can i import excel spreadsheet into mssql via vb6.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Several ways

by rob In reply to Importing Excel spreadshe ...

One is:
Declare an Excell application object
Open the spreadsheet
do whatever formating/validation you need
either open a recordset in the sql database and append records or create a query and execute it.

Collapse -

Import data from Excel to SQL Server

by rikin0681 In reply to Importing Excel spreadshe ...


Modify SQL Servername, Databasename, username and passoword for accessing ur SQL Server in !!!!

Private Sub Command1_Click()
' CancelError is True.
'On Error GoTo ErrHandler
' Set filters.
CommonDialog1.Filter = "Excel Files (*.xls)|*.xls"
' Specify default filter.
CommonDialog1.FilterIndex = 2

' Display the Open dialog box.
' Call the open file procedure.
Text1.Text = CommonDialog1.FileName

Exit Sub

End Sub

Private Sub Command2_Click()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=SQLServerName;" & _
"Initial Catalog=DatabaseName;User ID=UserName;Password=password"

'Import by using OPENDATASOURCE.
strSQL = "INSERT INTO tablename SELECT * FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=" + Text1.Text + ";" & _
"Extended Properties=Excel 8.0')...[Student_St$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

Set cn = Nothing

End Sub

Related Discussions

Related Forums