Excel2003: change query DB 1

[glyrocks]

I've built a spreadsheet we're using as a template to track certain things in a Access database. Using the Import External Data, I set up a simple query using a test database. Everything returns fine, all my calculations do what I want. But now I want to duplicate the template, keeping my calculations and whatnot, but change the DB query source. I need the same query, just need it to query a different database. The tables and fields will all be the same, only the name/location of the DB will change. Crystal Reports has a function that allows you to switch DB without changing anything else; is there something similar I can do in Excel? Thanks,

dylan

 

[SkipVought]

Hi,

Turn on your macro recorder.

Edit the query and File > Return data to Excel

Turn off the recorder.

alt+F11 toggles between the VB editor and the sheet.

post the recorded code for assistance in changing the source. Include the path & DB for the new source.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[glyrocks]

Brilliant! I think I can take it from there. Thanks a ton, I would never have thought of that!

dylan

 

[SkipVought]

Some tips on the code.

I always pretty up my query code like this. Your connection string will be different as MS Access...

Sub GetChartData()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = "C:\"
    
    sDB = "DBname"
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;)"

    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  Dte"
    sSQL = sSQL & ", Qty"
    sSQL = sSQL & ", Typ"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `Join$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE (PARTNO_201='" & [SelectedPN] & "')"
'
    With [b]wsChartData.QueryTables(1)[/b]
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

replace SELECTION with your sheet name. change QueryTable to QueryTables(1) assuming that you have only one query table on the sheet.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}