Large .TAB File - Break Up Into Smaller .TAB Files??

[olimits7]

Hello,

I have a .tab file with around 60,000 records. I need to upload this up to a website, but since the file is to big it gets timed out.

Can somebody help me create a Macro that can break the file into smaller files??

For example, maybe I can put that I want to break the file 3 times into around the same size file for each one. Then it would output 3 different files (ex. excel 1.tab, excel 2.tab, excel 3.tab).

Thank you,

olimits7

 

[texasalynn]

take a look at this
http://www.mrexcel.com/board2/viewtopic.php?t=27224

 

[olimits7]

Hello,

I found this macro that splits the data on different sheets, but it seems it is setup for an Access database. And it splits each sheet once it reaches 65,535.

Can somebody help me modify this script so that I can use a .Tab/.Txt file instead??

And also modify it so it splits into different sheets once it reaches 15,000 rows??

Sub GetBigDataset() 
      Dim cnn As ADODB.Connection 
      Dim rst As ADODB.Recordset 
      Dim lOffset As Long 
      Dim fld As ADODB.Field 
      Dim strSQL As String 
      Dim strTarget As String 
      
      'define the database path and the SQL string 
      strTarget = "H:\Test_db.mdb" 
      strSQL = "SELECT * FROM Customers" 
      
      'create the connection 
       Set cnn = New ADODB.Connection 
       With cnn 
          .Provider = "Microsoft.Jet.OLEDB.4.0" 
          .Open strTarget 
       End With 
    
       Set rst = New ADODB.Recordset 
       rst.CursorLocation = adUseServer 
       rst.Open Source:=strSQL, ActiveConnection:=cnn, _ 
         CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _ 
           Options:=adCmdText 
    
      'import the data using CopyFromRecordset. 
      'transfers chunks of 65535 rows, creating a new sheet for each chunk 
      'create headings for each sheet 
      While Not rst.EOF 
         Sheets.Add 
         With ActiveSheet.Range("A1") 'create field headers 
           lOffset = 0 
           For Each fld In rst.Fields 
               .Offset(0, lOffset).Value = fld.Name 
               lOffset = lOffset + 1 
           Next fld 
         End With 
         ActiveSheet.Range("A2").CopyFromRecordset rst, 65535 
     Wend 

     'clean up 
     rst.Close 
     cnn.Close 
End Sub

Thank you,

olimits7

 

[olimits7]

Can somebody help me with this??

Thank you,

olimits7

 

[dcardno]

If this is a one-off job, it will be just as easy (in fact, quite a bit easier) to copy -say- 20K rows of data, paste them into a new file, and then save that file as excel1.tab (and repeat for the other two files) as to design and debug a VBA routine.