Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query source folder (2003)

    My spreadsheet has a connection to another spreadsheet via MS Query. If my work colleagues then go and put the source and target files on their computers

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

    Re: MS Query source folder (2003)

    You write that you use MS Query to connect to another spreadsheet, but your code is intended for connecting to an Access database. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query source folder (2003)

    Hmm. I've been scratching my head so much my brains are leaking out. Sorry for the confusion.
    Yes, in this case I am connecting to an access database (had a similar issue before but you helped me out, Hans).
    So is it possible to say something like
    "Whatever folder the query is trying to read from, replace it with the current folder"
    Thanks again.

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

    Re: MS Query source folder (2003)

    Try this:
    <code>
    Sub ChangeConn()
    Dim qt As QueryTable
    Dim wsh As Worksheet
    Dim strPath As String
    Dim strConnect As String

    ' Path of active workbook
    strPath = ActiveWorkbook.Path
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If
    ' Full path of database
    strPath = strPath & "Databasename.mdb"
    ' New connect string
    strConnect = "ODBC;DSN=MS Access database;DBQ=" & strPath & _
    ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    For Each wsh In ThisWorkbook.Worksheets
    For Each qt In wsh.QueryTables
    qt.Connection = strConnect
    qt.Refresh
    Next qt
    Next wsh
    End Sub
    </code>
    Replace Databasename.mdb with the name of your database, and MS Access database with the name of your (user or machine) ODBC data source name.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query source folder (2003)

    Hans, thanks very much. I don't know what they pay you but it ain't enough!

    Much appreciated.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Query source folder (2003)

    His pay doubles every time he answers a question...and it's a nice round number. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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