VB-script in Excel - creating QueryTables

Hi!

I have recorded a macro in Excel which I need some help editing. The code:

Sub QueryOffer()

Dim BTdir As String


'--- Find Correct Path For Excel Workbook ---
    If Right(ActiveWorkbook.Path, 1) <> "\" Then
        xlwbpath = ActiveWorkbook.Path & "\"
    Else
        xlwbpath = ActiveWorkbook.Path
    End If

BTdir = xlwbpath & "BT2002.mdb"

    With Sheet14.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=V:\BT2002\BT2002.mdb;DriverId=25;FIL=MS Access;" _
        ), Array("MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))
       
       
        .CommandText = Array( _
        "SELECT OffHeader.Offer, OffHeader.CustArticle, OffHeader.IntArticle, OffHeader.Date, " & _
        "OffHeader.MadeBy, OffHeader.CustomerName, OffHeader.DrawingNo, OffHeader.Kode" & Chr(13) & "" & Chr(10) & "FROM `" & BTdir & "`.OffHeader OffHeader")
       
        .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
    Cells.Select
    Selection.AutoFilter
    Range("A2").Select

End Sub

Where it say: DBQ=V:\BT2002\BT2002.mdb I want the DBQ to point to BTdir instead of the written path.
Is this possible? Can the code be written in another way maybe ?

please help!

/Ecmil

EcmilAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
bruintjeConnect With a Mentor Commented:
Hi Ecmill,

This will do i guess

With Sheet14.QueryTables.Add(Connection:=Array(Array( _
       "ODBC;DSN=MS Access Database;DBQ=" & BTDir & ";DriverId=25;FIL=MS Access;" _
       ), Array("MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("A1"))

Just replace the mdb string with the btdir string

HTH:O)Bruintje
0
 
Richie_SimonettiIT OperationsCommented:
From where sheet14 is comming from?
0
 
EcmilAuthor Commented:
I tried to put in BTdir before and it didn't work but when I see your code, I see that I missed the " " around BTdir.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.