msquery issue. Need to copy remaining records on next sheet

DavidCorrez

New Member
Joined
Sep 11, 2002
Messages
7
I am using msquery in excel to download an Item Master List with well over 120,000 items.
As a Worksheet size is limited by 65,536 rows, excel only allows me to download that
amount of records. I want to be able to use vba or a method to download the complete number
of records, so when a worksheet is full it downloads the corresonding records on another
worksheet (the next sheet) until the complete list of records is downloaded.

Can anyone please help me?

DC
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
On 2002-09-12 02:56, DavidCorrez wrote:
I am using msquery in excel to download an Item Master List with well over 120,000 items.
As a Worksheet size is limited by 65,536 rows, excel only allows me to download that
amount of records. I want to be able to use vba or a method to download the complete number
of records, so when a worksheet is full it downloads the corresonding records on another
worksheet (the next sheet) until the complete list of records is downloaded.

Can anyone please help me?

DC

Hi,

Welcome to the board,

as for a VBA solution I don't think I can help.
BUT! if you have URN or a date field in your records, you could limit the first page using either for these.

i.e. URN >=1 AND <65,535
and from 65,536+ on the next sheet.

Hope this helps,
 

Tommy Bak

Active Member
Joined
Feb 25, 2002
Messages
288
Hi
I don't if if this can help you, but in item master records there is usually a lot of things to group in.
set msquery to return a pivottable and group on an important field.
This way you can get nearly unlimited amount of record from msquery.
Now you should be able to make a drilldown (doubleclick the item) for each field in the group. This will produce a new sheet with all records in this field.

regards tommy
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

You don't say what your data source is so I'll assume it's an Access database.

This code will need modifying to suit your needs but works OK. This code uses the ActiveX Data objects and you need to reference this in your code. To do that choose Tools, References and look for Microsoft ActiveX Data Object 2.x Library, where the 2.x depends on which version of MDAC you've got installed on your machine. THE CODE WILL NOT WORK UNTIL YOU DO THIS!

Have fun, and let me know if you can't get it to work.

Code:
Sub DownloadRecords()
    Dim adoRS As ADODB.Recordset, adoConn As ADODB.Connection
    Dim strSQL As String

    '////////////////////////////////////////////////////////
    'YOU MUST SET THE FOLLOWING REFERENCE:-
    'Microsoft ActiveX Data Objects 2.x Library

    'Change this SQL statement so that it matches the query you're currently using
    strSQL = "SELECT * FROM tblFex"

    'Stop screen from flickering & improve speed
    Application.ScreenUpdating = False

    'Open an ADO connection to your Access database
    Set adoConn = New ADODB.Connection
    adoConn.Provider = "Microsoft.Jet.OLEDB.4.0"    'Use 4.0 for Access 2K, 3.51 for Access 97
    adoConn.ConnectionString = "K:financepapmispap.mdb"  'Change to the path of your database
    adoConn.Open

    'Now open a recordset i.e. open your query
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly

    'Copy 65536 rows at a time to a new worksheet
    While Not adoRS.EOF
        Sheets.Add.Range("a1").CopyFromRecordset adoRS, 65536
    Wend

    'Close our ADO objects
    adoRS.Close
    adoConn.Close

    'Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

Edit
This code will only work in Excel 2000 onwards. This is because the CopyFromRecordset method could only work with DAO recordsets before this, not ADO.


_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-09-12 04:55
 

DavidCorrez

New Member
Joined
Sep 11, 2002
Messages
7
Thanks for the info guys. but I am getting data from an as/400 source.

Anyway on the topic of access, I am also trying to open a table in access which is a direct link to the AS400. When I want to read the values I get an error saying that the records cannot be read because there are no read permissions. Is there a way to overcome this.
 

Forum statistics

Threads
1,148,041
Messages
5,744,463
Members
423,877
Latest member
nlange41

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top