msquery issue. Need to copy remaining records on next sheet

[DavidCorrez]

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

 

[Ian Mac]

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]

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]

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.

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]

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.