Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Excel multiple worksheet populate from recordset
Using Access2000, the sample code below is what I have been modifying and
working on since the past week and I could not get it to work properly. What I wanted to accomplish: 1) read from a recordset and export to Excel 2) Excel is populated based from an ID (may possible be one or multiple) and renames the worksheet based from the ID 3) the code also format the fields The sample database may be downloaded at: http://www.geocities.com/mgtulips/sample_db.mdb TIA! Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Excel multiple worksheet populate from recordset
Put following in a module in your database..
Sub CreateXLS() Dim oRS As ADODB.Recordset Dim oCN As ADODB.Connection Const sXL = "d:\myexport.xls" If Dir(sXL) < "" Then Kill sXL Set oCN = New Connection oCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name Set oRS = oCN.Execute("SELECT DISTINCT t.ID from tmp_result_all as T") While Not oRS.EOF oCN.Execute " SELECT * INTO " & _ " [Excel 8.0;Database=" & sXL & "].[" & oRS(0) & "]" & _ " FROM tmp_result_all AS t" & _ " WHERE t.ID = '" & oRS(0) & "';" oRS.MoveNext Wend oRS.Close oCN.Close End Sub ammend & improve where needed... :) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "B" wrote : Using Access2000, the sample code below is what I have been modifying and working on since the past week and I could not get it to work properly. What I wanted to accomplish: 1) read from a recordset and export to Excel 2) Excel is populated based from an ID (may possible be one or multiple) and renames the worksheet based from the ID 3) the code also format the fields The sample database may be downloaded at: http://www.geocities.com/mgtulips/sample_db.mdb TIA! Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate central worksheet template from multiple worksheets | Excel Discussion (Misc queries) | |||
Lookup list from different worksheet & auto-populate multiple cell | Excel Discussion (Misc queries) | |||
can excel populate fields in one worksheet to another? | Excel Worksheet Functions | |||
getting multiple data to populate another worksheet | Excel Worksheet Functions | |||
How to populate a multi-column activeX listbox on a spreadsheet with an ADO recordset | Excel Programming |