[SOLVED] 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

Deko

Why is TransferSpreadsheet no good?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"deko" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

> 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.

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

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"

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
[email protected]HIS


"deko" <[email protected]> wrote in message
news:[email protected]...
>> 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.
>
>
>

> 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)

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/...php?t=envbadac

http://support.microsoft.com/default...46&Product=xlw
How To Transfer Data from ADO Data Source to Excel with ADO

http://support.microsoft.com/default...73&Product=xlw
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks

--
Regards,
Tom Ogilvy


"deko" <[email protected]> wrote in message
news:[email protected]...
> 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"
>
>

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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/...php?t=envbadac
>
> http://support.microsoft.com/default...46&Product=xlw
> How To Transfer Data from ADO Data Source to Excel with ADO
>
> http://support.microsoft.com/default...73&Product=xlw
> SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in
Excel
> Workbooks
>
> --
> Regards,
> Tom Ogilvy
>
>
> "deko" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> >
> >
>
>

> 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/...php?t=envbadac
>
> http://support.microsoft.com/default...46&Product=xlw
> How To Transfer Data from ADO Data Source to Excel with ADO
>
> http://support.microsoft.com/default...73&Product=xlw
> SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in
Excel
> Workbooks

I will. Thanks for the help!

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

Glad you solved your problem.

--
Regards,
Tom Ogilvy

"deko" <[email protected]> wrote in message
news:[email protected]...
> > 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/...php?t=envbadac
> >
> >
http://support.microsoft.com/default...46&Product=xlw
> > How To Transfer Data from ADO Data Source to Excel with ADO
> >
> >
http://support.microsoft.com/default...73&Product=xlw
> > SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in
> Excel
> > Workbooks
>
> I will. Thanks for the help!
>
>

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

at this point it's all a blur...

I appreciate the help.