Querying external data thru macros

[bLySs]

Hallu,

I'm trying to import some external data, and my query for it is that it
needs to be a specific date. I was wondering if i could write a macros
so, when i run it there will be a inputbox that pops up asking which
date i would like to query for before it imports the data.

Any ideas?Thanks,
bLySs

 

[Nick Hodge]

bLySs (This in XL2003, don't know if this will effect anything)...

Maybe you can extract the relevant from the code below, which I set up this
morning. It queries an access database on my hard drive called test.mdb,
takes two fields from a table called tblCatalogueHeader and sets up a
parameter of CatCode. It then adds a parameter called CatCode and sets the
parameter to prompt the user xlPrompt with the text shown. (It can also be
set with a fixed string or call the value from another cell. (I have
deliberately left all the methods out of the querytable setup as it is for
this example unnecessary code, but if you want the query to background
refresh, overwrite cells, etc, these will need to be set). I actually
learned something myself with this today, thanks!

Sub SetParameters()
Dim qry As QueryTable
Dim prm As Parameter
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MS Access
Database;DBQ=C:\Test.mdb;DefaultDir=C:;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblCatalogueHeader.CatCode, tblCatalogueHeader.AmountInv" &
Chr(13) & "" & Chr(10) & _
"FROM `C:\Test`.tblCatalogueHeader tblCatalogueHeader" & Chr(13) &
"" & Chr(10) & _
"WHERE (Catcode=?)")
.Name = "test"
End With
Set qry = ActiveSheet.QueryTables(1)
Set prm = qry.Parameters.Add("CatCode", xlParamTypeVarChar)
qry.Parameters(1).SetParam xlPrompt, "Enter a Catalogue Code (e.g 00000101)"
qry.Refresh
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[CLR]

Hi bLySs.............

I'm not much of a Coder, but if I understand your request correctly then the
following macro does for me what I think you want to do........that is,
bring up a pop-up window requesting some input......in my case a
"BOMNumber", (Bill of Materials number)......perhaps it's something you can
adapt from for your solution.........the rest of the code is a Query into
our MRP program called FourthShift........probably don't make much sense,
but here it is..........(watch out for the word-wrap)
----------------------------------------------------------------------------
---------------

Sub GetBOM_FromFourthShift()

' DOES AUTOMATIC QUERY FOR any BOM entered into InputBox

Dim inputstr

Const sFileName As String = "BOMQuery"

Worksheets("BOMQuery").Activate
inputstr = Range("BOMNumber").Value

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=fs1 - MSS-Fourth
Shift;ServerName=FS1.1583;ServerDSN=fs1 - 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.LEVEL,
ITEM_MULTILEVELBILL.PT_USE, ITEM_MULTILEVELBILL.SEQN,
ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.COM_TYP,
ITEM_MULTILEVELBILL.COMPONEN" _
, _
"T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.QUANTITY,
ITEM_MULTILEVELBILL.COMP_UM, ITEM_MULTILEVELBILL.QTY_TYP,
ITEM_MULTILEVELBILL.PARNT_DESC" & Chr(13) & "" & Chr(10) & "FROM
ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & "" & Chr(10) & "WHERE
(ITEM_MULTILEVELBILL.END_ITE" _
, "M='" & inputstr & "')")

.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

Range("Parnt_Desc").Value = Range("L2").Text

End Sub
Vaya con Dios,
Chuck, CABGx3

 

[Jamie Collins]

...

I'm not much of a Coder

I thought with that alias you were a hard-core dot net coder <g>.

Jamie.

--

 

[CLR]

lol............thanks anyway, but

CABGx3 = Coronary Artery Bypass Graft, times 3........... (a Triple Bypass
Survivor)

Vaya con Dios,
Chuck, CABGx3