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

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
178
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
178
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??

Code:
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
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,037
Messages
5,545,667
Members
410,697
Latest member
srishtijain0708
Top