Excel2003:External Data "Where's the code?" 1

[Larft]

I have an Excel 2003 spreadsheet that retrieves data from an Access database and have it set up to auto-refresh the data on open, three sheets in the workbook are set up to pull data over from separate tables in the database.

Everything is working fine although I had some fun when I wanted to modify the query to bring in some new data ranges in the database. If I modify the query and save it, it wants to add a number to the end of the query name like there are multiple versions of it somewhere.

My question is: Where the heck is the code that does all of this stored in the Excel spreadsheet? I'd like to be able to work directly with it if possible. I know the original query is saved to my C: drive but what about the modified one inside the workbook?

Any help would be appreciated.

 

[SkipVought]

Hi,

Turn on your macro recorder.

Select in the QueryTable resultset.

Data/Get External Data/Edit Query

proceed to the QBE Editor.

File/Return data toe Excel

Turn off the macro recorder.

Alt+F11 toggles between the VB Editor and Sheet. Observe your code. It cna look awful with many Array() elements in the Connection and CommendText, but it can be distilled like this if necessary...

    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  RC.MFG_SUPERVISOR "

    sSQL = sSQL & "FROM "
    sSQL = sSQL & "  FPRPTSAR.RESPONSIBILITY_CODES_MM RC "
 
    With wsData.QueryTables(1)
        .Connection = Array(Array( _
        "ODBC;DSN=DWPROD;;DBQ=DWPROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=" _
        ), Array("F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,
[sub]
[red][/red]
[/sub]

 

[Larft]

Thanks Skip,

Your procedure of how to get at the code was a bit different for me but I got it.

So... In other words, the actual code is buried inside an internal procedure?

To do what I want to do to modify the code I'd then have to assign it to a macro or module and run it with an Autoexec script, or something like that?

Thanks for your help!

 

[SkipVought]

I do this regularly. You can use the Workbook_Open event, for instance.

Modify the Connection string and/or the CommandText string on the fly and execute.

Skip,
[sub]
[red][/red]
[/sub]

 

[Larft]

Thanks again Skip,

As usual "You're The {CODE} Man" and get a star.
I'll probbly be back to revisit this one when I get in trouble trying it out.

Regards...