Can you help me? (2 Viewers)

lyuzhongkun

New member
Local time
Today, 10:15
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:15
Joined
May 21, 2018
Messages
8,463
Not sure if it is your only problem, but you use () when you return a value only from a function, exclude them when not.

msgbox "some message"
vs
rtn = Msgbox("Some Value",VByesNO)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:15
Joined
May 21, 2018
Messages
8,463
Also make sure your module name is not the same as your procedure.
 

lyuzhongkun

New member
Local time
Today, 10:15
Joined
Jan 14, 2019
Messages
3
Not sure if it is your only problem, but you use () when you return a value only from a function, exclude them when not.

msgbox "some message"
vs
rtn = Msgbox("Some Value",VByesNO)

I assure the msgbox is the only error. I change the format to msgbox "some message" but still failed. And what I found is that When I use msgbox in other modules in the same workbook, they all failed and pump up same compile error. But when I change to other workbooks, the error message is gone. Is that possible I set up something wrong in this specific workbook?
 

lyuzhongkun

New member
Local time
Today, 10:15
Joined
Jan 14, 2019
Messages
3
Also make sure your module name is not the same as your procedure.

Thanks, I found my mistake. I just doesn't know why I add a module named msgbox in the worksheet. How stupid am I and Thanks for your help!
 

Users who are viewing this thread

Top Bottom