Question

Locked

Importing Excel spreadsheet into mssql via Visual Basic 6

By manojgadiyar ·
Hi,

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.

3 total posts (Page 1 of 1)  
| 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 ...

Code:

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

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.
CommonDialog1.ShowOpen
' 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

cn.Close
Set cn = Nothing

End Sub

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums