Querying external data with macros.

[Blyss]

Hallo

I'm trying to import some external data, and my query for it is that it needs to be a specific date. I've og the macros for importing it down, but 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.
And if so, could anyone help me with that bit of coding?

Any ideas? Thanks,
bLySs

 

[xlbo]

Easiest way is this - record yourself setting up the query and then post the code it generates - we'll show you how to mod it but basically, it involves splitting the SQL string that is gebnerated and changing the
querytables.add
element to
With activesheet.querytables(1)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Blyss]

Here's the code so far...


With Workbooks("PERSONAL.XLS").Worksheets("Sheet3").QueryTables.Add( _
"ODBC;DRIVER=SQL Server;SERVER=ugh07;UID=abc123;PWD=abc123;APP=Microsoft® Query;WSID=A2854A" _
, Sheets("Sheet3").Range("A1"))
.Sql = Array( _
"SELECT vw_ret.AccountNumber, vw_ret.Sip, vw_ret.DescriptionLine1, vw_ret.ReferenceNumber, vw_ret.Indicator, vw_ret.date" & Chr(13) & "" & Chr(10) & "FROM abc123.dbo.vw_GACT vw_GACT" & Chr(13) & "" & Chr(10) & "WHERE (vw_ret.Tra" _
, "nsactionType='s') AND (vw_ret.Date={ts '2003-12-11 00:00:00'})AND (vw_ret.Indicator='l')")
.PostText = True
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.SavePassword = True
.SaveData = True
.Refresh False
End With

Hope you can help me,
Cheers,
bLySs

 

[xlbo]

ok - this should be pretty much what you need - I'm not a big SQL buff so not sure what the "ts" does but this is the klinda logic you need to apply:

qDate = InputBox("Enter Query Date")

If Not IsDate(qDate) Or qDate = "" Then
    MsgBox "Invalid Date entered - No query will be run"
    Exit Sub
End If

With Workbooks("PERSONAL.XLS").Worksheets("Sheet3").QueryTables(1)
        .Sql = "SELECT vw_ret.AccountNumber, vw_ret.Sip, vw_ret.DescriptionLine1, vw_ret.ReferenceNumber, vw_ret.Indicator, vw_ret.date " & _
               "FROM abc123.dbo.vw_GACT vw_GACT " & _
               "WHERE (vw_ret.TransactionType='s') " & _
               "AND (vw_ret.Date={ts '" [COLOR=red][b]& qDate &[/b][/color] "'}) " & _
               "AND (vw_ret.Indicator='l')"
        .Refresh False
End With

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

You may simply use MS-Query with parameters.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244