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

    MS Query source folder (2003 SP2)

    Is there a way to make the source folder which contains the source Excel spreadsheet file for a query always the current folder? I

  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 SP2)

    You could set the CommandText property of the QueryTable object in the Workbook_Open event procedure. Here is a simple example:

    Private Sub Workbook_Open()
    Dim strPath As String
    Dim strFile As String
    Dim strSQL As String
    strPath = Me.Path
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If
    strFile = strPath & "Source.xls"
    strSQL = "SELECT `Sheet1$`.Field1, `Sheet1$`.Field2 FROM `" & _
    strFile & "`.`Sheet1$` `Sheet1$`"
    Worksheets("Sheet1").QueryTables(1).CommandText = strSQL
    End Sub

    You must, of course, modify this to suit your situation.

  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 SP2)

    Thanks Hans.

    I am now getting a [Microsoft][ODBC Excel Driver]

  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 SP2)

    I posted a Workbook_Open event procedure. Such procedures belong in the ThisWorkbook module. You can refer to the workbook itself as Me in that module, just like you can refer to a worksheet as Me in its own worksheet module.
    The Auto_Open macro goes into a standard module. You cannot use the keyword Me there.

    The second `Sheet1` is the alias for the full name `Path and filename`.`Sheet1`. My code uses ` as string delimiter, not ' because MS Query uses `. I suggest that you should use ` too.

  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 SP2)

    Hans,
    Thanks for your help and patience - works like a gem now. Very much appreciated.

Posting Permissions

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