Batching a huge excel file with VB? 1

[jewilson2]

I have an excel file with ~ 50000 records and need to break it up into text files of only 98 records. I will also need to insert a header and trailer into each file making the record count 100 for each file.

Is there a way in VB to count and insert the header/trailer every 98 records in excel? Would this be done easier by exporting to txt then processing through a VB program?

Thanks in advance,
JW

 

[VBAjedi]

JW,

If I understand your issue correctly, this should work. Just update the obvious variables to match your situation (your destination directory, header text, etc), then paste this into a module of your source workbook. Try a first run with "x = 1 to 490" to generate five test workbooks before you do the whole thing.

Sub FileSplitter()
Dim TempWorkbook As Workbook, SourceWorkbook As Workbook
Dim SrcSht As Worksheet
Dim MyDirectory As String, MyFileName As String
Dim FileCounter As Integer
Dim x

Set SourceWorkbook = ActiveWorkbook
Set SrcSht = ActiveSheet
FileCounter = 0
MyDirectory = "C:\My Documents\Temp\"

For x = 1 To 63000
  If x Mod 98 = 0 Then
    FileCounter = FileCounter + 1
    SrcSht.Rows((x - 97) & ":" & x).Copy
    'Selection.Copy
    Set TempWorkbook = Workbooks.Add
    TempWorkbook.Sheets("Sheet1").Activate
    ActiveSheet.Cells(2, 1).Select
    ActiveSheet.Paste
    ActiveSheet.Cells(1, 1).Value = "My Header Text"
    MyFileName = "Results " & FileCounter
    TempWorkbook.SaveAs (MyDirectory & MyFileName)
    TempWorkbook.Close
  End If
Next x
End Sub

Let me know if this works for you!



VBAjedi

 

[SteveBausch]

Let me verify your situation:

50,000 rows of information.
You want to break it up into 98 rows per .TXT file.
At the start of each file will be a header line.
At the end of each file will be a footer line.

If the above is correct, then the following is a rough draft:

For ThisManyFiles = 0 to AboutFiftyThousand\98

'Open the file, insert your header line

For EachLine = (ThisManyFiles*98)+1 to (ThisManyFiles*98)+98
HoweverYouWriteToTheFile = Cells(EachLine,ThisColumn)
Next
'Insert the footer line, close the file.
Next

 

[VBAjedi]

Oh - didn't see that you wanted to save as text. You should still be able to do that with my code - just change the following two lines:

MyFileName = "Results " & FileCounter & ".txt"
TempWorkbook.SaveAs MyDirectory & MyFileName, FileFormat:=xlText

You may also want to suppress the alerts that pop up.


VBAjedi

 

[SteveBausch]

VBAjedi,

I like your proposal to copy over to a temp workbook, good idea.

I assume "FileFormat:=xlText" should be added?

And Application.Screenupdating=false would help speed up the process, since you are using .Select, which writes to screen.

 

[VBAjedi]

Good call on Sreenupdating=False.

I don't understand - are you saying that "FileFormat:=xlText" needs to be in a location other than where I specified it in my correction?


VBAjedi

 

[jewilson2]

COOL....

Thanks folks. I was able to make the first example work.

I couldn't get the second to work but probably only because I don't really understand the statement.

The only problem with the second one is the normal Excel "do you wish to save changes to XXXX.txt file" prompt. Of course you get this anytime you close a txt or csv file even if you just saved it.

Anyways, I can deal with a few prompts I suppose.

Thanks a million,
JW

 

[Nikita6003]

Hiya ...

Try

Application.DisplayAlerts = False

to switch off standard xl prompts. Xl will assume the default answer (i.e. "would you like to save changes" = "Yes&quot.

you could also try closing the file with the paramter [/code]False[/code] - this'll close without saving.

e.g.

l_wbTextFile.Close False

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[jewilson2]

Thanks Nik....

I'll give that a shot. Does it matter where in the statement that entry goes?

BTW...I just noticed an error in my post. It's the first example that gives all the prompts....I never got the second one to work.

Thanks again folks,
JW

 

[Nikita6003]

if you never want any xl-generated msgboxesin your code, start your sub with

Application.ScreenUpdating = False

& end it with

Application.ScreenUpdating = True

If you don't use the second statement, xl will not show you any alerts whatsoever (i.e. not even when you're working in xl yourself) until you close & reopen xl ... ;-)

If you just want to switch off/on for one line of code, use

Application.ScreenUpdating = False
' ... your code line here
Application.ScreenUpdating = True

Cheers
HTH
Cheers
Nikki
Look, mommy, I'm flying!