Hello All,
This is my first post and I got an Issue - Hopefully it will be a good start for me on this forum -
Issue: I'd like to program Select statement in VB where I have Querytext which is 'Select' statement and 'From' part - and then there is 'where' part - The first part will remain constant during the excercise. For Instance (Select Col A, B, C, D, E....n from Table name) and the second part is tricky (atleast for me) 'where' clause, I will have two arguments namely 'cutoff date' and IN <list of CustIDs>. Now there are more than 100K custIDs.
I want the program to format select statement, picks up custids from other tab of excel, saves everything on a text file and then sends the text file to sql, and in return i get the resultant data back in excel - all by clicking one button -
Considering I am new to VB domain, heres what I tried to wrote something. Also I have 64bit excel and using SQL 2012.
' Set file details
fileDir = "C:\Users\k****\Documents"
filePath = "C:\Users\k****\Documents" & Node & "sql.sql"
'check if directory exists, if not create it
If Dir(fileDir, cbDirectory) = "" Then
MkDir fileDir
End If
' open the file
Open filePath For Output As #1
'Write to file
outputText = sqlQuery3
Print #1, outputText
'open connection
sqlCon.ConnectionString = Conn
'Cn.CursorLocation = adUseClient
sqlCon.Open
'set and execute sql command
Set sqlCommand.ActiveConnection = sqlCon
sqlCommand.CommandText = sqlQuery3
sqlCommand.CommandType = adCmdText
sqlCommand.Execute
'open recordset
Set sqlRecordSet.ActiveConnection = sqlCon
sqlRecordSet.Open sqlCommand
'If
' Not sqlRecordset.EOF
'Then
'ActiveSheet.Range("A1").CopyFromRecordset sqlRecordSet
'Else Msgbox "No records returned!" End If
'copy data to excel
ActiveSheet.Range("A1").CopyFromRecordset (sqlRecordSet)
'close connections
sqlRecordSet.Close
sqlCon.Close
'Close file
Close #1
Sub Data_Fetching3()
End Sub
Any help would be appreciated -
Many Thanks,
K
This is my first post and I got an Issue - Hopefully it will be a good start for me on this forum -
Issue: I'd like to program Select statement in VB where I have Querytext which is 'Select' statement and 'From' part - and then there is 'where' part - The first part will remain constant during the excercise. For Instance (Select Col A, B, C, D, E....n from Table name) and the second part is tricky (atleast for me) 'where' clause, I will have two arguments namely 'cutoff date' and IN <list of CustIDs>. Now there are more than 100K custIDs.
I want the program to format select statement, picks up custids from other tab of excel, saves everything on a text file and then sends the text file to sql, and in return i get the resultant data back in excel - all by clicking one button -
Considering I am new to VB domain, heres what I tried to wrote something. Also I have 64bit excel and using SQL 2012.
' Set file details
fileDir = "C:\Users\k****\Documents"
filePath = "C:\Users\k****\Documents" & Node & "sql.sql"
'check if directory exists, if not create it
If Dir(fileDir, cbDirectory) = "" Then
MkDir fileDir
End If
' open the file
Open filePath For Output As #1
'Write to file
outputText = sqlQuery3
Print #1, outputText
'open connection
sqlCon.ConnectionString = Conn
'Cn.CursorLocation = adUseClient
sqlCon.Open
'set and execute sql command
Set sqlCommand.ActiveConnection = sqlCon
sqlCommand.CommandText = sqlQuery3
sqlCommand.CommandType = adCmdText
sqlCommand.Execute
'open recordset
Set sqlRecordSet.ActiveConnection = sqlCon
sqlRecordSet.Open sqlCommand
'If
' Not sqlRecordset.EOF
'Then
'ActiveSheet.Range("A1").CopyFromRecordset sqlRecordSet
'Else Msgbox "No records returned!" End If
'copy data to excel
ActiveSheet.Range("A1").CopyFromRecordset (sqlRecordSet)
'close connections
sqlRecordSet.Close
sqlCon.Close
'Close file
Close #1
Sub Data_Fetching3()
End Sub
Any help would be appreciated -
Many Thanks,
K
