lyuzhongkun
New member
- Local time
- Yesterday, 20:52
- Joined
- Jan 14, 2019
- Messages
- 3
I want to wrote a vb to get data from SQL Server to Excel. But when I run this, I get an compile error msg "Expected variable or procedure, not module" and direct to the MsgBox line. I not sure why this happen.
Sub SQLDatabase()
Dim rs As ADODB.Recordset ' hold data
Dim cnSQL As ADODB.Connection '
Dim sqlString As String
Dim colOffset As Integer
Dim qf As Object
colOffset = 0
Sheets("Database").Select
Cells.ClearContents
Set cnSQL = New ADODB.Connection 'instantiates the connection.
cnSQL.Open "provider = SQLOLEDB.1;INtegrated Security =SSPI; Initial Catalog = sample1; Data source =LAPTOP-THDFQP9K\SQLEXPRESS"
sqlString = "select id, name, email from tbperson "
Set rs = New ADODB.Recordset
rs.Open sqlString, cnSQL, adOpenStatic, adLockOptimistic
If rs.EOF Then
MsgBox ("The recordset is empty, rs.eof =" & rs.EOF)
Else
MsgBox "# of record =" & rs.RecordCount
For Each qf In rs.Fields
Range("A1").Offset(0, colOffset).Value = qf.name
colOffset = colOffset + 1
Next qf
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
End If
rs.Close
Set rs = Nothing
End Sub
Sub SQLDatabase()
Dim rs As ADODB.Recordset ' hold data
Dim cnSQL As ADODB.Connection '
Dim sqlString As String
Dim colOffset As Integer
Dim qf As Object
colOffset = 0
Sheets("Database").Select
Cells.ClearContents
Set cnSQL = New ADODB.Connection 'instantiates the connection.
cnSQL.Open "provider = SQLOLEDB.1;INtegrated Security =SSPI; Initial Catalog = sample1; Data source =LAPTOP-THDFQP9K\SQLEXPRESS"
sqlString = "select id, name, email from tbperson "
Set rs = New ADODB.Recordset
rs.Open sqlString, cnSQL, adOpenStatic, adLockOptimistic
If rs.EOF Then
MsgBox ("The recordset is empty, rs.eof =" & rs.EOF)
Else
MsgBox "# of record =" & rs.RecordCount
For Each qf In rs.Fields
Range("A1").Offset(0, colOffset).Value = qf.name
colOffset = colOffset + 1
Next qf
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
End If
rs.Close
Set rs = Nothing
End Sub