Babawande has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Changing VBA Code from an InputBox
Hi
Please kindly assist with this problem.
Below is an SQL query in a VBA module that pulls data out of a database
=============================================================
Sub CompassExtract()
sSQLDB = "PrivateDataBase"
mstrOLEDBConnect = "Provider=SQLOLEDB.1;" & _
"Data Source=FNS-PDBSQL-01;" & _
"Initial Catalog=" & sSQLDB & ";" & _
"Integrated Security=SSPI"
Set gcnn = New ADODB.Connection
gcnn.ConnectionString = mstrOLEDBConnect
gcnn.ConnectionTimeout = 0
gcnn.CommandTimeout = 0
gcnn.Open
myPriData = "SELECT PriJComCode, PriGroupCode, PriZd, PriItmWinsorK "
myPriData = myPriData & "FROM ReportData "
myPriData = myPriData & "WHERE PriScrID BETWEEN 721 AND 725 "
myPriData = myPriData & "AND PriItmCode = 0 "
myPriData = myPriData & "ORDER BY PriJComCode, PriGroupCode, PriZd DESC "
Set rst = gcnn.Execute(myPriData)
Worksheets("Sheet1").Range("a2").CopyFromRecordset rst
=============================================================
I am working (but its getting very difficult) on a VBA code that will change the SQL code through an input box i.e.
For example, I may want to change the SQL query from:
myPriData = myPriData & "WHERE PriScrID BETWEEN 721 AND 725 "
to:
myPriData = myPriData & "WHERE PriScrID BETWEEN 1000 AND 2000 "
by using an input box instead of going the the VBA code to change.
Please kindly assist.
Regards
RE: Changing VBA Code from an InputBox
Thanks for get back to me. I have tried to figure a solution out using study materials (enough crude), and am still improving it..Please find it below.
Sub InsertLine()
'this code is to insert a code into a particular line on an exist VBA code i.e line 1 or line 2
Call DeleteLine
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("M_Test")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum - 4, InputBox(Prompt, Title) 'this count from the last line of the code to the first line of the code,(therefore "3" mean line 3 of the code from the bottom)
End With
End Sub
Sub DeleteLine()
'this code is to delete a particular line in a code in a VBA code i.e line 1 or line 3
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("M_Test")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfLines + 1
.DeleteLines 7 'this count from the first line of the code to the last line of the code,(therefore "3" mean line 3 of the code from the top)
End With
End Sub
