Split LARGE Workbook into multiple WBs

[adambc]

I've got a LARGE WB (an extract from a legacy system that's been switched off!) that I need to process but it's soooo slow!

Plan is to spilt it into multiple WBs (manual test shows it will perform better as smaller WBs) ...

Every record has a 6 digit ID (NOT a record ID so there can be multiple rows per ID) and I want to split the file by ID range so that all records for a particular ID are in the same file ...

Is there an easier way than doing it than manually (which feels a bit tedious!)?

Thanks ...

 

[jkpieterse]

What exactly is slow? Is it slow to open, slow to calculate, what?

 

[adambc]

What exactly is slow? Is it slow to open, slow to calculate, what?

Both, it’s currently c 500MB, 500000+ rows, 165 columns

 

[jkpieterse]

What kind of calculations are you doing that make it slow to calc? Perhaps a different approach may help?

 

[adambc]

What kind of calculations are you doing that make it slow to calc? Perhaps a different approach may help?

It’s 1 of 70 extract files that I need to loop through with a known ID and copy rows containing the ID in a specific column to a new WB/WS …

All the other extract files are “sensible” sizes and each one takes seconds to open, copy the data and close whereas this file takes forever (I haven’t timed it but it runs into many minutes, maybe 20)

I can’t access the VBA right now but it’s along the lines of …

For i to lastrow
If (eg) CFi = ID Then
Copy entire row to target WB/WS
End If

 

[jkpieterse]

I would rewrite the code so it:
- Generates a unique list of IDs
- Runs through them
- Filters the table for each ID
- Copies visible rows

 

[adambc]

I would rewrite the code so it:
- Generates a unique list of IDs
- Runs through them
- Filters the table for each ID
- Copies visible rows

My fault (trying to post from my ‘phone!) but not sure I’ve explained what I’m doing properly …

Will update when I can get to a proper keyboard (and post my VBA) …

But the problem remains that the file takes a LONG time to open, whereas manually creating a file with eg 50000 rows was in line with the other extract files (and yes I know, there would be 10 files to process!) …

I could split the file manually but was looking for an easier alternative - but if there’s a better way to process the large file I’m all ears …

Watch this space for my VBA …

Thanks …

 

[jkpieterse]

If you can give us an idea of what you are doing with the data (calculation-wise), perhaps we can point at alternative approaches

 

[adambc]

If you can give us an idea of what you are doing with the data (calculation-wise), perhaps we can point at alternative approaches

@jkpieterse

My apologies for not replying sooner - I had to down tools at short notice until now ...

And in case you haven't seen my earlier posts, I am self taught (in retirement) and the "stuff" I'm doing is as a volunteer for a local charitable organisation so all help is gratefully received ...

Let me try to succinctly explain what I'm trying to do ...

The organisation shut down an ageing database a couple of years ago (I was not involved!) - a "secure" copy of the database was taken (which by all accounts was verified at the time) but a recent attempt to access the data has found that the file is corrupted beyond recovery! - however, the database tables were also exported to (c. 70) individual .xlsx files, but an attempt to reimport them has found that the exports themselves have problems negating this option! - leaving me with the following ...

1. c. 70 .xlsx files - data represents database tables but is in data ranges (not Excel tables) - ALL files have an "ID" column which is a 6 character numeric "field" (with leading 0's)

2. there is a "master" files that links Firstname/Surname to an ID - but there can of course be multiple links eg multiple John Smith(s) - I have built an InputBox based VBA routine that takes the Firstname and Surname entered by the user, returns a list of the possible IDs (with secondary identifying data), from which the user selects the correct Firstname/Surname/ID

3. I then need to use the ID to return a list of the filenames of the files containing the ID (once I've got this working I will also need to write the entire row alongside the filename)

This is my code up to this point ...

Sub Find_ID_In_IDColumn()

Dim Forename As String
Dim Surname As Variant
Dim Message, Title, ID
Dim SearchWB As Workbook
Dim IDColFind As Range
Dim ColNum As Long
Dim IDFind As String
Dim lr2 As Long


'Enter Forename (blank Forename is OK)

Forename = Application.InputBox("Enter Forename", "Forename")
    ThisWorkbook.Sheets(1).Cells(1, 2) = Forename

'Enter Surname

Do While Not Valid

Surname = Application.InputBox("Enter Surname", "Surname")
    If Surname = False Then
        Exit Sub
    ElseIf Surname = Empty Then
        MsgBox "You must enter a Surname"
        Valid = False
    Else
        ThisWorkbook.Sheets(1).Cells(2, 2) = Surname
        Valid = True
    End If

Loop

'Enter ID (ID List created in Cells(3, 2) using an Excel FILTER function)

Do While Not Valid

ID = Application.InputBox("Enter ID from ID List", "ID")
    If ID = False Then
        Exit Sub
    ElseIf ID = Empty Then
        MsgBox "You must enter a ID"
        Valid = False
    Else
        ThisWorkbook.Sheets(1).Cells(3, 2) = ID
        Valid = True
    End If

Loop

'Open SearchWBs (loops through list in SearchWBPathNames WS2/Column2)

lr2 = ThisWorkbook.Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr2

SearchWBName = ThisWorkbook.Sheets(2).Cells(i, 1)
SearchWBPathName = "SHAREPOINT PATHNAME/" & SearchWBName
Set SearchWB = Workbooks.Open(SearchWBPathName)

'Find ID column number in SearchWB

With SearchWB.Sheets(1).Rows(1)
    Set IDColFind = .Find(What:="ID", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not IDColFind Is Nothing Then
        ColNum = IDColFind.Column
    End If
End With

'ID to search for

IDFind = ThisWorkbook.Sheets(1).Cells(3, 2)

'Find ID in SearchWB/ID column - if found write SearchWB filename to WS3/Next Row

Filename = ActiveWorkbook.Name

If Trim(IDFind) <> "" Then
    With Sheets(1).Columns(ColNum)
        Set rng = .Find(What:=IDFind, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

Dim lr As Long
lr3 = ThisWorkbook.Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
nr3 = lr3 + 1

        If Not rng Is Nothing Then
            ThisWorkbook.Sheets(3).Cells(nr3, 1) = Filename
        End If
    End With
End If

'Close SearchWB (without saving)

SearchWB.Close

Next i

End Sub

... which takes time to work through 70 files, time stands still when it hits the large file above ...

Any ideas, alternative approaches, etc very welcome ...

 

[adambc]

STILL HAVE A PROBLEM WITH MY LARGE FILE!

EVERYTHING is working perfectly (and quickly) until I add my large file to the list of files to search - Excel “locks” up into “not responding” mode!

The only way round it that I can see is to split the file into a series of smaller files but I want to have all instances of the same ID (in the IDFind column) in the same file which means I can’t just split the file by number of rows - my ideal would be 10 files of +/- 50000 rows but not with IDs across files …

But I can’t get my head round keep splitting if I get to 50000 but still have a few rows with the same ID as row 50000?

Any ideas?

PS it would be a one off routine - the large file is a one off extract from a defunct system