excel sql vba

[ahmimas]

hi,
I wrote an excel vba application with a user form that reads data from a sheet and populate a list.
it was working fine, but when I removed a column in the spreadsheet and changed slightly the design, I start having the following error: please see the image.
there is the spot where the program stalls.
I have exactly the same code in another file and it is working fine.
the sheet name is Products.
Please help.

code is here:

Option Explicit
Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Private Sub UserForm_Initialize()
Dim lngWinstate As XlWindowState

With Application
.ScreenUpdating = False
lngWinstate = xlMaximized
Me.Move 0, 0, .Width, .Height
.WindowState = lngWinstate
.ScreenUpdating = True
End With

OpenDB
displayData


End Sub

Public Sub OpenDB()
closeRS
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub
Public Sub closeRS()
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
End Sub

Public Sub displayData()

strSQL = "Select Distinct [Code] From [Products$] Order by [Code]"

UserForm1.CmbPlc.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
If Not IsNull(rs.Fields(0)) Then
UserForm1.CmbPlc.AddItem rs.Fields(0)

End If

rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Exit Sub
End If
End Sub

 

[EFANYoutube]

Hi
how does your connection string compare to the working file
Usually this issue is cause by an incorrect connection string

Thanks
Excel Fan

 

[ahmimas]

Hi
how does your connection string compare to the working file
Usually this issue is cause by an incorrect connection string

Thanks
Excel Fan

thank Efan,
it is exactly the same. I also noticed that if I make a layout change in the working file spreadsheet, I start having the same issue without changing anything to the code.
in this situation, I must close excel and restart it.