Changing VBA Code from an InputBox

Resolved · Urgent Priority · Version 2003

Wed 25 Aug 2010, 11:54 Delegate Babawande said...

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

Fri 27 Aug 2010, 10:17 Delegate Babawande said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Validating text entries

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula: