Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate Excel Range from Access RecordSet?
I have code that loops through about 40 Access mdbs and sucks in a bunch of
data into one internal Access table. The challenge now is getting that data out of Access and into an Excel Worksheet. I've tried DoCmd.TransferSpreadsheet - not the solution I need. This is the kind of thing I'm looking for: xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CopyFromRecordset rst xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CurrentRegion.Columns.AutoFit I want to dump whatever is in the rst into a blank Worksheet. Can someone help me with syntax or other suggestions? Thanks! Here's more complete code if you're interested: For Each fldSub In fld.SubFolders strSheetName = fldSub.Name strMdbPath = fld & "\" & strSheetName & "\MEAN.MDB" If LinkTable(strMdbPath, strXlsPath) Then db.Execute strSql, dbFailOnError Set rst = db.OpenRecordset("tblMean") j = xlapp.Workbooks(strXlsFile).Worksheets.Count xlapp.Workbooks(strXlsFile).Worksheets.Add(After:= xlapp.Workbooks _ (strXlsFile).Worksheets(j)).Name = strSheetName xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CopyFromRecordset rst xlapp.Workbooks(strXlsFile).Worksheets(j + 1).CurrentRegion.Columns.AutoFit k = k + 1 End If xlapp.Workbooks(strXlsFile).Save Next fldSub It took me a while to figure out this syntax: xlapp.Workbooks(strXlsFile).Worksheets.Add(After:= xlapp.Workbooks(strXlsFile ).Worksheets(j)).Name = strSheetName but it seems to be working now |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost! Excel multiple worksheet populate from recordset | Excel Programming | |||
Help! Excel multiple worksheet populate from recordset | Excel Programming | |||
Acquiring a single recordset from Access into Excel | Excel Programming | |||
How to populate a multi-column activeX listbox on a spreadsheet with an ADO recordset | Excel Programming | |||
open range (within workbook) as ado recordset - excel vba | Excel Programming |