How to populate Excel Range from Access RecordSet?

[deko]

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

 

[Nick Hodge]

Deko

Why is TransferSpreadsheet no good?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[deko]

Why is TransferSpreadsheet no good?

Well, for one thing, the path to my Excel workbook is more than 64
characters - this blows up the function. Also, I need to keep the Worksheet
a particular name. If I use TransferSpreadsheet, the worksheet is
automatically renamed the to the name of object exported. (I suppose I
could change it back, though.) I'm also thinking an rst will give me more
control.

 

[deko]

I'm testing this, but there must be a way to dump the entire rst without
looping (?)

Do Until rst.EOF
For i = 0 To rst.Fields.Count - 1
CurrentField = rst(i)
Sheet.cells(j, i + 1).Value = CurrentField
Next i
rst.MoveNext
j = j + 1
Loop

 

[deko]

If I build a Recordset from a 2-column table or query, then this should dump
each column out to the worksheet, is this correct?

Do While Not rst.EOF
For m = 0 To rst.Fields.Count - 1
varCurrentField = rst(m)
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(n, m + 1).Value
= varCurrentField
Next m
rst.MoveNext
n = n + 1
Loop

This is barfing with: Error Number 1004: "Application-defined or
object-defined error"

 

[Nick Hodge]

Deko

I can't test the 64 char limit as mine are less than that, but why not save
it to the root and then move it with VBA. If you are using the 'Range'
property in TransferSpreadsheet and using a version of Excel after 5 it will
add a new worksheet to the workbook specified and the name in the Range
property will be the name of the worksheet.

Does that help at all.

If not we may need to look at ODBC or ADODB or similar. Equally, if the end
product is Excel we could easily bring it in through VBA in Excel also.
(Pull rather than push)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[deko]

I can't test the 64 char limit as mine are less than that, but why not
save

it to the root and then move it with VBA. If you are using the 'Range'
property in TransferSpreadsheet and using a version of Excel after 5 it will
add a new worksheet to the workbook specified and the name in the Range
property will be the name of the worksheet.

Does that help at all.

If not we may need to look at ODBC or ADODB or similar. Equally, if the end
product is Excel we could easily bring it in through VBA in Excel also.
(Pull rather than push)

Thanks for the reply. Saving to root and then moving the file might well be
a solution. But I've went ahead and used a Recordset for now. It's kind of
slow. I'll bet TransferSpreadsheet is quicker. In any case, the task at
hand is to get the charts looking better. If you care to offer any comments
on this chart code, that would be great.

'===== populate worksheet with data ====='
Set rst = db.OpenRecordset("ExcelData")
Do While Not rst.EOF
For m = 0 To rst.Fields.Count - 1
varCurrentField = rst(m)
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(n, m + 1).Value
= varCurrentField
Next m
rst.MoveNext
n = n + 1
Loop

'===== add chart ====='
xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName & " Chart"
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlLineMarkers
'*** I think this next line needs help ***
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(1, 3),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False
xlapp.Workbooks(strXlsFile).ActiveChart.ApplyDataLabels
Type:=xlDataLabelsShowValue

(All this code is contained within a loop - approx 40 worksheets and charts
need to be generated)

 

[Tom Ogilvy]

if rst(m) is an entire column, then varCurrentField is an array, then you
are trying to put that in one cell

If varCurrentField is a two dimensional array (num rows, 1 column) then

xlapp.Workbooks(strXlsFile).Worksheets(j + 1) _
.Cells(n, m + 1).Resize(Ubound(varCurrentField) - _
lbound(varCurrentField) + 1,1).Value = _
varCurrentField

If it is 1 D, then you probably need

xlApp.Transpose(varCurrentField)
rather than
varCurrentField

Perhaps you can look at:

http://www.erlandsendata.no/english/index.php?t=envbadac

http://support.microsoft.com/default.aspx?scid=kb;en-us;295646&Product=xlw
How To Transfer Data from ADO Data Source to Excel with ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973&Product=xlw
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks

 

[deko]

if rst(m) is an entire column, then varCurrentField is an array, then you
are trying to put that in one cell

If varCurrentField is a two dimensional array (num rows, 1 column) then

xlapp.Workbooks(strXlsFile).Worksheets(j + 1) _
.Cells(n, m + 1).Resize(Ubound(varCurrentField) - _
lbound(varCurrentField) + 1,1).Value = _
varCurrentField

If it is 1 D, then you probably need

xlApp.Transpose(varCurrentField)
rather than
varCurrentField

Perhaps you can look at:

http://www.erlandsendata.no/english/index.php?t=envbadac

http://support.microsoft.com/default.aspx?scid=kb;en-us;295646&Product=xlw
How To Transfer Data from ADO Data Source to Excel with ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973&Product=xlw
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks

 

[deko]

if rst(m) is an entire column, then varCurrentField is an array, then you

are trying to put that in one cell

If varCurrentField is a two dimensional array (num rows, 1 column) then

xlapp.Workbooks(strXlsFile).Worksheets(j + 1) _
.Cells(n, m + 1).Resize(Ubound(varCurrentField) - _
lbound(varCurrentField) + 1,1).Value = _
varCurrentField

If it is 1 D, then you probably need

xlApp.Transpose(varCurrentField)
rather than
varCurrentField

Well, the rst has 3 columns and lots of rows, so I believe that's a 3D
array.

This appears to be working:

Do While Not rst.EOF
For m = 0 To rst.Fields.Count - 1
varCurrentField = rst(m)
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(n, m + 1).Value
= varCurrentField
Next m
rst.MoveNext
n = n + 1
Loop

What does:

Resize(Ubound(varCurrentField) - lbound(varCurrentField) + 1,1)

do?

Perhaps you can look at:

http://www.erlandsendata.no/english/index.php?t=envbadac

http://support.microsoft.com/default.aspx?scid=kb;en-us;295646&Product=xlw
How To Transfer Data from ADO Data Source to Excel with ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973&Product=xlw
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks

I will. Thanks for the help!

 

[Tom Ogilvy]

that is the identical code which you said was causing an error.

Glad you solved your problem.