Replace string with input by Pop-up

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

Hi All............

I have a macro that I recorded and it works fine. My problem is that I
would like for a pop-up to appear when it starts and accept my typed input
to replace the string 'A01X02601-02' with whatever new string I want to type
in........is that a do-able thing?

Here's the macro:

Sub QueryTest()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=FS - MSS-Fourth Shift;ServerName=FS.1583;ServerDSN=FS -
MSS-Fourth Shift;UID=clr;PWD=chuck;ArrayFetchOn=1;ArrayBufferSize=8" _
), Array(";DBQ=M:\Mfgsys\System;CODEPAGE=1252;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ITEM_MULTILEVELBILL.END_ITEM, ITEM_MULTILEVELBILL.PARENT,
ITEM_MULTILEVELBILL.PARNT_DESC, ITEM_MULTILEVELBILL.REV,
ITEM_MULTILEVELBILL.MB, ITEM_MULTILEVELBILL.DRWG,
ITEM_MULTILEVELBILL.COMPONEN" _
, _
"T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.COMP_MB,
ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.OUT_REV" & Chr(13) & "" &
Chr(10) & "FROM ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & "" &
Chr(10) & "WHERE (ITEM_MULTILEVELBILL.END_ITE" _
, "M='A01X02601-02')" & Chr(13) & "" & Chr(10) & "ORDER BY
ITEM_MULTILEVELBILL.COMPONENT")
.Name = "Query from FS - MSS-Fourth Shift_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3
 
Hi
try (without error checking)
Sub QueryTest()
Dim inputstr
inputstr = inputbox ("Please enter a text")

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=FS - MSS-Fourth
Shift;ServerName=FS.1583;ServerDSN=FS -
MSS-Fourth Shift;UID=clr;PWD=chuck;ArrayFetchOn=1;ArrayBufferSize=8" _
), Array(";DBQ=M:\Mfgsys\System;CODEPAGE=1252;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ITEM_MULTILEVELBILL.END_ITEM,
ITEM_MULTILEVELBILL.PARENT,
ITEM_MULTILEVELBILL.PARNT_DESC, ITEM_MULTILEVELBILL.REV,
ITEM_MULTILEVELBILL.MB, ITEM_MULTILEVELBILL.DRWG,
ITEM_MULTILEVELBILL.COMPONEN" _
, _
"T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.COMP_MB,
ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.OUT_REV" & Chr(13) & ""
&
Chr(10) & "FROM ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & ""
&
Chr(10) & "WHERE (ITEM_MULTILEVELBILL.END_ITE" _
, "M='" & inputstr & "')" & Chr(13) & "" & Chr(10) & "ORDER BY
ITEM_MULTILEVELBILL.COMPONENT")
.Name = "Query from FS - MSS-Fourth Shift_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
 
Just a few gotchas with concatenating the parameter into the select
statement.
If the "text" contains a single-quote, then the query is going to fail with
a syntax error.

If possible, use the Parameters feature of QueryTable.
I believe you can even specify to have the parameter stored in a cell. So
when you change the cell content, the query automatically refreshes using
the new parameter.
 
Thanks Frank...........

I appreciate that, and will try it first thing monday when I get back to the
network.........that's exactly the sort of thing I was trying to do but
alas, just could not figure it out...........

Thanks again,
Vaya con Dios,
Chuck, CABGx3
 
Thank you Rob............this is the first time I've ever tried using Query
and I find that feature you're describing to be very
interesting........that's also what I was trying to accomplish but had no
clue as to how to do it............I'm anxious to get back to the network
on monday and give it a try.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3
 
Back
Top