sql.request and collecting data from a database

Posted by Eowyn on June 08, 2001 4:38 AM

I'm wondering wether or not it's possible to get sql.request to use as many rows and columns in excel as it needs? I have no problem marking a number of cells, entering =SQL.REQUEST("DSN=MyBase";;2;"SELECT ... ...") and hitting ctrl+shift+enter to get the result to fill out the selected cells. But what if I don't know how many cells are needed for the result? Is there any way to tell sql.request to use all the cells it needs..? Will I have to use macros / visual basic to get this done?

If so - any hints where I should start? I've never programmed macros / visual basic before.

Posted by Doug on June 08, 2001 6:09 AM

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=S103R96M;CMT=0;DBQ=AMRPRDDTA;NAM=0;DFT=0;DSP=0;TFT=0;TSP=0;DEC=0;XDYNAMIC=0;" _
), Array( _
"RECBLOCK=0;BLOCKSIZE=8;SCROLLABLE=0;TRANSLATE=1;LAZYCLOSE=0;LIBVIEW=0;REMARKS=0;CONNTYPE=0;SORTTYPE=0;LANGUAGEID=ENU;SORTWEIGHT" _
), Array("=0;PREFETCH=0;MGDSN=0;")), Destination:=Range("A1"))
.Sql = Array("SELECT SALSRP.RSCM1"& Chr(13) & "" & Chr(10) & "FROM S103R96M.AMRPRDDTA.SALSRP SALSRP" & Chr(13) & "" & Chr(10) _
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = True
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With

Destination='A1' tells it to start at A1. It will use as many cells as it needs like this. Hope this helps.
Doug

Posted by Mark W. on June 08, 2001 7:21 AM

Consider using Excel's Data | Get External Data menu
command instead.