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

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
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
 

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
Can somebody help me with this??

Thank you,

olimits7
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
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.
 

Forum statistics

Threads
1,141,665
Messages
5,707,696
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top