Solved

VB-script in Excel -  creating QueryTables

Posted on 2002-03-13
3
474 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:Ecmil
3 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
From where sheet14 is comming from?
0
 

Author Comment

by:Ecmil
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now