Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    External Data Query based on Cell Value (2K)

    Hello All,

    I am hoping someone can provide some guidance on the following problem...

    I have recorded a macro to pull data into a worksheet from an external MSAccess database. What I would like to do is determine the database name (and/or location) from the contents of a cell in the same workbook, rather than from the text of the macro itself.

    In the example below, where the text FILENAME appears, how can I change this to an actual cell reference??

    Sub GrabData()
    '
    ' GrabData Macro
    ' Macro recorded 19/11/2002 by Rob Miller
    '

    '
    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=MS Access Database;DBQ=Cocuments and SettingsFILENAME.MDB;DefaultDir=Cocuments and Settings" _
    ), Array("Desktop;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
    ), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT DET.PCHDT, DET.DCT, DET.CGRESP, DET.INIT" & Chr(13) & "" & Chr(10) & "FROM `Cocuments and SettingsFILENAME `.DET DET" & Chr(13) & "" & Chr(10) & "ORDER BY DET.PCHDT" _
    )
    .Name = "Query from MS Access Database"
    .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

    Thanks in advance.

    Rob

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Data Query based on Cell Value (2K)

    I think this shoudl do it:

    Dim sConnStr As String
    sConnStr = "ODBC;DSN=MS Access Database;DBQ=" _
    & "Cocuments and Settings" & ThisWorkbook.Worksheets("Sheet1").[A1] _
    & ";DefaultDir=Cocuments and SettingsDesktop" _
    & ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    With ActiveSheet.QueryTables.Add(Connection:=sConnStr _
    , Destination:=Range("A1"))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: External Data Query based on Cell Value (2K)

    If I understand your question correctly, I think you just need to replace
    <pre>...;DBQ=Cocuments and SettingsFILENAME.MDB;...</pre>

    in your Connection string with something like
    <pre>...;DBQ='" & Range("dbFullName").Value & "';...</pre>


    dbFullName would be a defined name in your workbook.

    Not sure about the DefaultDir parameter. Maybe it's optional. You could try excluding it and see what happens. If it is needed, you could have two defined names in your workbook - dbDirectory and dbFilename, say - and modify the DBQ parameter accordingly.

    You would need to do the same idea for the other occurrence in the SELECT statement.

  4. #4
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Data Query based on Cell Value (2K)

    Thanks Jan. This works wonderfully!

    Rob.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Data Query based on Cell Value (2K)

    Good Afternoon Jan,
    I have a very similar problem as Colin but I just couldn't get the anser from your example. In my macro I want to filter the data based on the contents of another cell in the same workbook, could you please show me how I change '019734' to the value (foramted as text) of cell in say Sheet 1!$A$1 from my macro below.

    Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=Max Dat;DATAPATH=m:kewillmaxdat;DDFPATH=m:kewillmaxdat ;NullEnabled=no;FeaturesUsed=no;AccessFriendly=yes ;DateFormat=m" _
    ), Array("dy;")), Destination:=Range("A1"))
    .CommandText = Array( _
    "SELECT ""BOL^Tracking"".TRACKNO, ""BOL^Tracking"".ADDR1, ""BOL^Tracking"".ADDR2, ""BOL^Tracking"".ADDR3, ""BOL^Tracking"".ADDR4, ""BOL^Tracking"".ADDR5, ""BOL^Tracking"".ADDR6, ""BOL^Tracking"".CHECKBY, ""BOL^Tracking" _
    , _
    """.CITY, ""BOL^Tracking"".CNTRY, ""BOL^Tracking"".CUSTID, ""BOL^Tracking"".FRTAMT, ""BOL^Tracking"".FRTPAY, ""BOL^Tracking"".GROSSWEIGHT, ""BOL^Tracking"".GROSSWUOM, ""BOL^Tracking"".LOADBY, ""BOL^Tracking"".NAME, ""BO" _
    , _
    "L^Tracking"".NETWEIGHT, ""BOL^Tracking"".NETWUOM, ""BOL^Tracking"".PREPBY, ""BOL^Tracking"".SHPCDE, ""BOL^Tracking"".SPECINSTR1, ""BOL^Tracking"".SPECINSTR2, ""BOL^Tracking"".SPECINSTR3, ""BOL^Tracking"".SPECINSTR4," _
    , _
    " ""BOL^Tracking"".SPECINSTR5, ""BOL^Tracking"".STATE, ""BOL^Tracking"".STATUS, ""BOL^Tracking"".ZIPCD" & Chr(13) & "" & Chr(10) & "FROM ""BOL^Tracking"" ""BOL^Tracking""" & Chr(13) & "" & Chr(10) & "WHERE (""BOL^Tracking"".TRACKNO='019734')" _
    )
    .Name = "Query from Max Dat (not sharable)_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

    I appreceiate any help you can give.
    Stats

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: External Data Query based on Cell Value (2K)

    Try replacing<pre>"WHERE (""BOL^Tracking"".TRACKNO='019734')"</pre>

    by<pre>"WHERE (""BOL^Tracking"".TRACKNO='" & _
    ActiveWorkbook.Worksheets("Sheet1").Range("A1") & "')"</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •