by David Koopman (11 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 7th December 2005
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limitation, this should be of assistance.
API Declarations
Private CMD As ADODB.Command
Private RS As ADODB.Recordset
VBcoders
'strSite = "BOS" for example
RS.Open
RS.PageSize = 65000 'Limit each page to 65000 rows based on Excel limitations
intPageCnt = RS.PageCount 'Recordcount / PageSize will equal number of sheets.
For intPage = 1 To intPageCnt
Close #1
Open strPath & strSite & ").xls" For Output As #1
strHeader = "BRANCH" & vbTab & "ACCT" & vbTab & "VALUE_DT" & vbTab & "ENTRY_DT" & vbTab & "CCY" & vbTab & "TRAN_AMT" & vbTab & "CREDIT/DEBIT" & vbTab & "RSN_CD" & vbTab & "RFRNE_NMBR"
Print #1, strHeader
RS.AbsolutePage = intPage
For lngRecord = 1 To RS.PageSize
strRows = RS!BRANCH & vbTab & RS!ACCT & vbTab & RS!VALUE_DT & vbTab & RS!ENTRY_DT & vbTab & RS!CCY & vbTab & FormatNumber(RS!TRAN_AMT, 2) & vbTab & Right(RS!TRAN_TYP, 1) & vbTab & RS!RSN_CD & vbTab & RS!RFRNE_NMBR
Print #1, strRows
RS.MoveNext
If RS.EOF Then Exit For
Next lngRecord
Next intPage
Close #1
No comments have been posted about Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limit. Why not be the first to post a comment about Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limit.
