returning a sql query to an excel spreadsheet

[croakyfrog]

i'm trying to conect to a sql db run a query and return the results into an excel worksheet - simple you might think but i keep getting mad errors like connection not defined. what's the actual syntax for doing this?

any pointers would be a help.

thnkas!

 

[ETID]

Are you using ODBC or MDAC?

 

[croakyfrog]

ODBC

 

[ETID]

Have you tried using MsQuery via Excel to link to your db?....you'll need to know your odbc driver or dsn name.

 

[SkipVought]

If you use MS Query, you can probably set the whole thing up visually with QBE and then it is only a matter of Refresh Data Skip,
SkipAndMary1017@mindspring.com

 

[croakyfrog]

can you do it with a macro though?

 

[ETID]

Yes,...easy way is to run through the steps witht the macro recorder on. then you can edit the code to splice in variables.

 

[croakyfrog]

ok i've recorded a macro, put it behind the button that i want to be able to click to remember it. the code points to a ms query. so can i just replace the link to the query with a sql statement?

the reason i want to do this is ive got 2 combo boxes, one with year, one with month so what i want to do is select whatever from the_table where datepart (table.month = selected month) and year = selected year

if you get my drift - ps i know this sql is wrong im just making an example

 

[croakyfrog]

ps the combos box is populated from a hidden sheet dont know if this makes a difference

 

[ETID]

did the recorder return something like this?....




With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=H:\NPET REPORTING\NPET REPORTING.mdb;DefaultDir=H:\NPET REPORTING;DriverId=25;FIL=MS Access;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;&quot)
.CommandText = Array( _
"SELECT `NPET MONTHLY OPEN WITH LAST COMMENT`.`PE NUMBER`, `NPET MONTHLY OPEN WITH LAST COMMENT`.DTCOMENT, `NPET MONTHLY OPEN WITH LAST COMMENT`.SEQ, `NPET MONTHLY OPEN WITH LAST COMMENT`.DTPRERVW, `NP" _
, _
"ET MONTHLY OPEN WITH LAST COMMENT`.`DATE OPEN`, `NPET MONTHLY OPEN WITH LAST COMMENT`.Status_Catagory, `NPET MONTHLY OPEN WITH LAST COMMENT`.`Elapsed Years`, `NPET MONTHLY OPEN WITH LAST COMMENT`.DTEV" _
, _
"LCMP, `NPET MONTHLY OPEN WITH LAST COMMENT`.PE_Number, `NPET MONTHLY OPEN WITH LAST COMMENT`.LOCATION, `NPET MONTHLY OPEN WITH LAST COMMENT`.TRADMODL, `NPET MONTHLY OPEN WITH LAST COMMENT`.CATEGORY, `" _
, _
"NPET MONTHLY OPEN WITH LAST COMMENT`.SPONSOR, `NPET MONTHLY OPEN WITH LAST COMMENT`.COMMENT" & Chr(13) & "" & Chr(10) & "FROM `H:\NPET REPORTING\NPET REPORTING`.`NPET MONTHLY OPEN WITH LAST COMMENT` `NPET MONTHLY OPEN WITH LAST " _
, "COMMENT`&quot
.Refresh BackgroundQuery:=False
End With

 

[croakyfrog]

oo no, nothing so complicated - this is what i got from it, it's got some formatting bits and bobs in too:



Rows("2:5044&quot.Select
Selection.Delete Shift:=xlUp
Range("A2&quot.Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Program Files\Microsoft Office\Queries\penny report.dqy", _
Destination:=Range("A2&quot)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
Rows("2:2&quot.Select
Selection.Delete Shift:=xlUp

End Sub

 

[ETID]

That looks more like a query definition.


I was thinking more on the lines of a direct link to your table or query from within excel...

----------------

From excel click Data>Get External Data>New Database Query>....then choose your db driver from the list...

(hopefully, if not there, then you can create one if you have the odbc driver installed...more on that later if needed)

follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties to refresh on open...fill formulas etc.
or edit the query via MsQuery.


--------------------
.....these are the steps that I refered to earlier,...for recording

 

[croakyfrog]

yes this is what i cant change you see i can point to the query file in the code but i want to put the actual sql in it

 

[ETID]

Hmmm....

when I turn on the recorder, then right click in the returned data, select edit query, (then msquery opens)
then select file>retun data to msexcel, then stop the recorder,....when i look at the code, i get a statement that includes the sql (as above) ...you can concatenate (splice) variables into the sql...

....this maybe easier to define if you add a criteria while in the msquery editor just to help you find the line in the sql statement where you need to splice in a variable.

as I did in the "where" portion of the following code returned by the recorder.

I also added the cell refs to pick up dates (you'll use your select or edit box maybe.....

the origional msquery had a criteria for a field called
TEVAL.DTEVLCMP ...so i replace the hard date with a variable
made up of conditionals and the SELECT_DATE$ variables.

------------------------------------------------------
Sub set_date()
'
' set_date Macro
' Macro recorded 3/7/2002 by xyz.
'

'
SELECT_DATE$ = Sheets("npets_data_closed&quot.Range("U1&quot
SELECT_DATE2$ = Sheets("npets_data_closed&quot.Range("U2&quot

MsgBox (SELECT_DATE$ + " - " + SELECT_DATE2$)


Sheets("npets_data_closed&quot.Range("A1&quot.Select
With Selection.QueryTable
.Connection = "ODBC;DSN=NPETS ODBC;"
.CommandText = Array( _
"SELECT TEVAL.EVALNUMB, TEVAL.DTPRERVW, TEVAL.PRERVWST, TEVAL.LOCATION, TEVAL.DTEVLCMP, TEVAL.ACTION, TEVAL.STATUSX, TEVAL.DTCHANGD, TEVAL.USERIDNO" & Chr(13) & "" & Chr(10) & "FROM LAB400T.NPETPROD.TEVAL TEVAL" & Chr(13) & "" & Chr(10) & "WHERE (TEVAL.STAT" _
, &quot;USX='COMPLETED') AND (TEVAL.DTEVLCMP<&quot; + SELECT_DATE$ + &quot; And TEVAL.DTEVLCMP>&quot; + SELECT_DATE2$ + &quot&quot
.Refresh BackgroundQuery:=False
End With
Sheets(&quot;npets_data_closed&quot.Visible = False
End Sub
----------------------------


Hope this helps

 

[croakyfrog]

thanks for all your help - this is what worked in the end though:

mcondb.Mode = adModeRead

mcondb.Open (&quot;driver={SQL SERVER}; server = 10.10.10.10; uid=uid; pwd=pwd; data source = db2&quot

Set comm.ActiveConnection = mcondb

'The SQL statement
strSQL = &quot;select * from tablename&quot;

comm.CommandText = strSQL
comm.CommandType = adCmdText
rstFromQuery.Open comm, , adOpenDynamic, dLockPessimistic

thanks for all your help though anyway