Mapping Data to Excel

I have data in MS SQL and I need to map that data to an Excel spreadsheet
where the layout is all ready determined. I have looked into using XML
however this Excel layout has been around for a long time and it would have
to be overhauled. I am looking for other routes to map data to Excel. I
want to have a map that will be easily changed if minor changes happend to
the layout. Any ideas?

Thanks

George,

If the names in the columns match the fields in your db this is not that
difficult.

Construct a query based on the contents of your column headers, retrieve the
data as a record set, and copyfromrecordset into the right place in your
spreadsheet.

e.g.
if the top row contains headers, something like this (untested)

Dim strSQL as string
Dim rsData as ADODB.Recordset
Dim strCon as string
Dim lField as long

set rsdata = new adodb.recordset
lfield = 1
strSQL = "SELECT "
With Sheets(1)

Do while not (isempty(.cells(1,lfield))

strsql = strsql & .cells(1,lfield).text & ","
lfield = lfield +1

loop

end with

strsql = left(strsql,len(strsql)-1)
strsql = strsql & " FROM MyTable"

strcon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DATA;Data Source=MyDb"

With rsData

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open strSQL, strCon, , , adCmdText
Set .ActiveConnection = Nothing

End With

sheets(1).cells(2,1).CopyFromRecordset rsData
set rsData = nothing



Robin Hammond
www.enhanceddatasystems.com



"george" <george@spamme.com> wrote in message
news:OqET47TSGHA.5500@TK2MSFTNGP12.phx.gbl...
>I have data in MS SQL and I need to map that data to an Excel spreadsheet
>where the layout is all ready determined. I have looked into using XML
>however this Excel layout has been around for a long time and it would have
>to be overhauled. I am looking for other routes to map data to Excel. I
>want to have a map that will be easily changed if minor changes happend to
>the layout. Any ideas?
>
> Thanks
>