Hi q,

AFAIK, it's not possible to do what you're looking to do. However, I have a
few questions for you:

1) Do you really need all of the rows from your Access table? If not, do
you just need a particular subset of the data, or do you simply need totals,
counts, etc?

2) If yes to #1, why are you bringing the data into Excel? Could you
accomplish the same goals using Access?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


q wrote:
Hello. VBA in Excel XP with Win 2k.

You can insert a spreadsheet into Word and you can insert a Word
document into Excel.

Can you insert an Access Table object into Excel? I'm trying to work
around the 65,536 row limit in XL.

WHAT ARE MY OPTIONS?
Is the best thing to automate Access from XL ????????????????????

Please everyone advise. I need some guidance here. Thanks in advance
for all responses/thoughts.

Jake

Thanks for the reply
I'm working with large amounts of data ported from an Oracle Financials DB. The data is summarized for reports used by accountants, but auditors require the details from which the summary reports are created. No work arounds here (believe me)

I have tried using the OWC10 spreadsheet control on a spreadsheet which bumps the line limit up to 262,000+ rows and also a list box control on the spreadsheet to hold the details, but in their own ways these solutions do not provide a practical solution

I think I'm down to either using a text file or porting my recordset to Access and then derive my summary reports from there. My users, accountants, use Excel, but I could make the Access side relatively invisible

Any more ideas? Please respond. Thanks again

Hi q,

If you really need all the data in Excel (ie, it must be editable by the
auditors), then you will have to create a recordset based on your data and
then pull that data into Excel, splitting it across worksheets. You should
be able to pull the data directly from Oracle and skip the Access
intermediate step.

Here's some code I just put together that will do just that. It requires a
reference to Microsoft ActiveX Data Objects v2.7 (or whatever your latest
version is). There's no error handling, and it's not very robust, but it
may do what you're looking for. You will need to fill in your connection
string and SQL statement accordingly.

Make sure the active worksheet is empty before running this code, as it will
overwrite the contents of the active sheet and add additional sheets if
needed.

Sub test()
Dim cnMain As ADODB.Connection
Dim cdMain As ADODB.Command
Dim rsMain As ADODB.Recordset
Dim nPages As Integer
Dim nPage As Integer
Dim ws As Worksheet
Dim vData As Variant
Dim avData() As Variant
Dim nCol As Integer
Dim lRow As Long

'/ set up connection
Set cnMain = New ADODB.Connection
With cnMain
.ConnectionString = "<YOUR CONNECTION STRING HERE"
.CursorLocation = adUseClient
.Open
End With

'/ get data into recordset
Set cdMain = New ADODB.Command
With cdMain
Set .ActiveConnection = cnMain
.CommandText = "<YOUR SQL STATEMENT HERE"
.CommandType = adCmdText
Set rsMain = .Execute
End With

'/ get # of pages, disconnect recordset
With rsMain
Set .ActiveConnection = Nothing
.PageSize = ActiveSheet.Rows.Count
nPages = .PageCount
End With

'/ close connection
Set cdMain = Nothing
cnMain.Close
Set cnMain = Nothing

'/ now, create each page and populate it
For nPage = 1 To nPages
If nPage 1 Then
Set ws = Worksheets.Add(after:=ActiveSheet)
Else
Set ws = ActiveSheet
End If
vData = rsMain.GetRows(rsMain.PageSize)

'/ transpose vData
ReDim avData(0 To UBound(vData, 2), 0 To UBound(vData, 1))
For lRow = 0 To UBound(vData, 2)
For nCol = 0 To UBound(vData, 1)
avData(lRow, nCol) = vData(nCol, lRow)
Next nCol
Next lRow

With ws
.Range(.Cells(1, 1), .Cells(UBound(vData, 2) + 1, _
UBound(vData, 1) + 1)).Value = avData
End With
Next nPage
Set ws = Nothing
rsMain.Close
Set rsMain = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


q wrote:
Jake,

Thanks for the reply.
I'm working with large amounts of data ported from an Oracle
Financials DB. The data is summarized for reports used by
accountants, but auditors require the details from which the summary
reports are created. No work arounds here (believe me).

I have tried using the OWC10 spreadsheet control on a spreadsheet
which bumps the line limit up to 262,000+ rows and also a list box
control on the spreadsheet to hold the details, but in their own ways
these solutions do not provide a practical solution.

I think I'm down to either using a text file or porting my recordset
to Access and then derive my summary reports from there. My users,
accountants, use Excel, but I could make the Access side relatively
invisible.

Any more ideas? Please respond. Thanks again.

Jake

Thanks so much for taking the time to do this. I have your code and will give it a try. I will tweak where needed. I already have my connection string and SQL so these are no problem

I am just curious, when you transposed the array (after using the "GetRows" method) is there a reason you didn't use the "Transpose" command? I am assuming you were just making it backward compatible or is there something I should know about "Transpose"?

I know in older versions of Excel, Transpose had a limitation. I'm using Excel XP with Win 2K, so "Transpose" should be good right

Again thanks so much for your assistance.

Hi q,

q wrote:
Thanks so much for taking the time to do this. I have your code and
will give it a try. I will tweak where needed. I already have my
connection string and SQL so these are no problem.

No problem - hope it goes smoothly.

I am just curious, when you transposed the array (after using the
"GetRows" method) is there a reason you didn't use the "Transpose"
command? I am assuming you were just making it backward compatible or
is there something I should know about "Transpose"?

I know in older versions of Excel, Transpose had a limitation. I'm
using Excel XP with Win 2K, so "Transpose" should be good right?

I tried Application.Transpose the first time, and it failed. The limit is
no longer ~5K elements, as it was in previous versions, but I think there is
some limitation on the overall length of the data involved. I was able to
use Transpose for arrays of dimension 65536x1 and 65536x2, assuming my data
was limited to numeric types. However, when I tried a SQL statement that
returned an array of size 65536x2 with the second column being a string
(varchar 65), the Transpose method failed. So if you're dealing with a lot
of data that may include strings, you may want to manually transpose the
array.

One thing you could do is use error handling. Try the Transpose method,
then trap the error that will occur if it fails. In which case you can
manually transpose the array. That would probably be the most efficient way
to do it.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

"Jake Marx" wrote in message ...

I tried Application.Transpose the first time, and it failed. The limit is
no longer ~5K elements, as it was in previous versions, but I think there is
some limitation on the overall length of the data involved. I was able to
use Transpose for arrays of dimension 65536x1 and 65536x2, assuming my data
was limited to numeric types. However, when I tried a SQL statement that
returned an array of size 65536x2 with the second column being a string
(varchar 65), the Transpose method failed. So if you're dealing with a lot
of data that may include strings, you may want to manually transpose the
array.

One thing you could do is use error handling. Try the Transpose method,
then trap the error that will occur if it fails. In which case you can
manually transpose the array. That would probably be the most efficient way
to do it.

Jake,
Interesting. I didn't know that the 5K limit (as mentioned in Q246335)
no longer applies. I have my own (now little used) CopyFromRecordset
variation which uses your suggested 'error handling' approach i.e. if
Application.Transpose causes an error then do it the long way. I just
tested it with a 65536x10 results set: no limitation was reached but I
gave up waiting for the 'long way' to finish!

One thing I've considered (but haven't got round to building a proper
example) is to use the recordset's 'pages' e.g. some quick code:

With rs
.ActiveConnection = Con
.CursorType = adOpenStatic
.Source = "SELECT MyCol1 FROM TakesMuchTime"
.Open
.PageSize = 65535
' Interate through Pages somehow!
' e.g. to do the last page:
.AbsolutePage = .PageCount
Sheet1.Range("A2").CopyFromRecordset rs
End With

Depends on the data but one way of iterating through the pages would
be to Sort/ORDER BY on a key column (or columns), iterate pages using
..AbsolutePage, get the key column values for the first rows of each
page, then on a second pass use the key values in the Filter method to
limit the rs to 65536 (or less) for CopyFromRecordset on each
iteration. Rather than two passes, it would be easier to Clone the
recordset and use the two recordsets side-by side when filtering, of
course.

A little project for one day when I've more time on my hands,
methinks.

--