• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limit

Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limit

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




Rate Anyone who has struggled with exporting database records to Excel that exceed the 65,000+ row limit

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'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