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
HIS


"deko" wrote in message
m...
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
HIS


"deko" wrote in message
m...
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.SetSourceD ata Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(1, 3),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False
xlapp.Workbooks(strXlsFile).ActiveChart.ApplyDataL abels
Type:=xlDataLabelsShowValue

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