Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mapping Data to Excel
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mapping data on a world map | Excel Discussion (Misc queries) | |||
Excel to Excel Import and Data Mapping | New Users to Excel | |||
Mapping Data from one Excel file to Another | Excel Discussion (Misc queries) | |||
Mapping Data from One worksheet to Another | Excel Discussion (Misc queries) | |||
Automating the XML Data Mapping in Excel 2003 using C# | Excel Programming |