How to populate Excel Range from Access RecordSet?

D

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
 
N

Nick Hodge

Deko

Why is TransferSpreadsheet no good?

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

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

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
 
D

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"
 
N

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

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)
Click to expand...

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

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
 
D

deko

Tom Ogilvy said:
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
Click to expand...
 
D

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
Click to expand...

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
Click to expand...

I will. Thanks for the help!
 
T

Tom Ogilvy

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

Glad you solved your problem.
 
D

deko

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

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

I appreciate the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to embed chart in active worksheet with VBA? 9
Excel VBA - How to enter a reference to another sheet? 9
How to move cells with VBA? 4
Sum a Spreadsheet Column from Access 1
How to add worksheets and rename with vba? 12
Excel Sheets (running from Access) 2
Automating Excel from Word - How to Sort a Range of Cells 6
Limiting Data Imported from Access to Excel 2

Top