joehoe007
04-22-2008, 06:46 PM
Hello All, New to VBA but not to excel. I'm trying to get this code to pull in data that I usually have too manually copy over. Trying to validate if the connection string / SQL statement or record set is not working. I don?t get in error. It runs and then ends. does not populate my spread sheet, no error messages either.
Any help would be great...
Private Sub CommandButton1_Click()
Const strConStr = "Provider=MSDASQL;DSN=xxx;SRVR=xx;DB=xxxx;UID=xxx;PWD=xxx"
Dim STRSQL_BUFFER As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim dtmStart As Date
Dim dtmEnd As Date
Dim strcell As Integer
dtmStart = #8/20/2008#
dtmEnd = #8/21/2008#
If adoConnection.State <> 1 Then
adoConnection.Open strConStr
End If
STRSQL_BUFFER = "SELECT * FROM d_camp_define WHERE convert(varchar(10), Dateadd(ss, create_tstamp,'01/01/1970'),101) BETWEEN '" & dtmStart & "' AND '" & dtmEnd & "'"
If adoConnection.State = 1 Then
adoRecordset.Open STRSQL_BUFFER, adoConnection
End If
Do While Not adoRecordset.EOF
Me.range("a" & CStr(strcell)) = adoRecordset.Fields(1)
strcell = strcell + 1
adoRecordset.MoveNext
Loop
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
Thanks:dunno
Any help would be great...
Private Sub CommandButton1_Click()
Const strConStr = "Provider=MSDASQL;DSN=xxx;SRVR=xx;DB=xxxx;UID=xxx;PWD=xxx"
Dim STRSQL_BUFFER As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim dtmStart As Date
Dim dtmEnd As Date
Dim strcell As Integer
dtmStart = #8/20/2008#
dtmEnd = #8/21/2008#
If adoConnection.State <> 1 Then
adoConnection.Open strConStr
End If
STRSQL_BUFFER = "SELECT * FROM d_camp_define WHERE convert(varchar(10), Dateadd(ss, create_tstamp,'01/01/1970'),101) BETWEEN '" & dtmStart & "' AND '" & dtmEnd & "'"
If adoConnection.State = 1 Then
adoRecordset.Open STRSQL_BUFFER, adoConnection
End If
Do While Not adoRecordset.EOF
Me.range("a" & CStr(strcell)) = adoRecordset.Fields(1)
strcell = strcell + 1
adoRecordset.MoveNext
Loop
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
Thanks:dunno