Software·43,775 discussions

Importing Excel spreadsheet into mssql via Visual Basic 6

Locked

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.

Topic

Clarifications

In reply to Importing Excel spreadsheet into mssql via Visual Basic 6

Clarifications

Several ways

In reply to Importing Excel spreadsheet into mssql via Visual Basic 6

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.

Import data from Excel to SQL Server

In reply to Importing Excel spreadsheet into mssql via Visual Basic 6

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

[Author]

Replies