Macro ODBC Connection

[jgilbert]

I am using Microsoft Excel to pull from a database using the ODBC connection. When changes are made to the database, we sometimes have to reselect the data source when we try to run a refresh in excel. Currently, I macro'd excel file is opened by task scheduler every morning that refreshes and saves several query-linked excel files. Recently a change was made to the database, and now the task scheduler won't run correctly because when it opens the macro'd excel file, the odbc drive prompt appears.

Is there a way to code a data source selection into the macro? My current macro looks like this:

Sub DailyApplications()
'
' DailyApplications Macro
' Macro recorded 8/26/2005 by J
'
    Selection.QueryTable.Refresh BackgroundQuery:=False
    ActiveWorkbook.Save
    Workbooks.Open Filename:= _
        "S:\IS\REPORTS, LAYOUT CRITERIA & TOTALS\SQL Queries\MPD- SQL\Daily applications\Emp_St_Dt.xls"
    Selection.QueryTable.Refresh BackgroundQuery:=False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Workbooks.Open Filename:= _
        "S:\IS\REPORTS, LAYOUT CRITERIA & TOTALS\SQL Queries\MPD- SQL\Daily applications\Open_Dt.xls"
    Selection.QueryTable.Refresh BackgroundQuery:=False
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.Quit
    
End Sub

 

[SkipVought]

Hi,

Turn on your macro recorder.

Data/Get External Data/Edit Query

Finish and File/Return data to excel

Turn off the recorder

the generated code includes the connect string

Post back if you need help

BTW, in your code is is best to explicitly quallify the querytable

  With [b]YourSheet.QueryTables(1)[/b]
    .Connection = YourConnectString
    .Refresh BackgroundQuery:=False
  End With

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}