Results 1 to 3 of 3

Ttrying to copy large data in sheet1 but only 65536 records get copied

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    883

    Ttrying to copy large data in sheet1 but only 65536 records get copied

    Hi guys

    I am writing the following code to copy data from Access database to Excel. the total number of records that get copied are 90000 so .It gives me error message .

    but is there anyway to get it done. I mean writing the piece of code to check if sheet1 is full then move to sheet 2 and then sheet3 and so on until all data gets copied.

    Private Sub CommandButton1_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=J:\Policies.mdb;"
    
    
    Set rs = CreateObject("ADODB.Recordset")
    
          
          strsql = "select Policy_No from  Policy_Nos where Policy_Type='FSL'"
          
          rs.Open strsql, cn
                
            
          Set ws = ThisWorkbook.Worksheets("FSL")
            
            
            For colIndex = 0 To rs.FIELDS.Count - 1
            ws.Cells(1, colIndex + 1) = rs.FIELDS(colIndex).Name
            Next
         
         ws.Cells(2, 1).CopyFromRecordset rs
         ws.UsedRange.Columns.AutoFit
          rs.Close
          
          
          
    End Sub
    Last edited by aman1234; 10-21-2014 at 09:50 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] insert copied data from sheet1 to sheet2
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2014, 10:21 AM
  2. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  3. facing error - subscript out of range - In allocating data in sheet name wise
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2013, 05:30 AM
  4. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  5. dde data copied from sheet1 to sheet2
    By chuckb74 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-02-2009, 03:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1