Split Files

[ir121973]

Hi, I wonder whether someone could help me please.

I'm using the script below to split files into more manageable chunks.

Sub SplitTextFile()
Dim sFile As String  'Name of the original file
Dim sText As String  'The file text
Dim lStep As Long    'Max number of lines in the new files
Dim vX, vY           'Variant arrays. vX = input, vY = output
Dim iFile As Integer 'File number from Windows
Dim lCount As Long   'Counter
Dim lIncr As Long    'Number for file name
Dim lMax As Long     'Upper limit for loop
Dim lNb As Long      'Counter
Dim lSoFar As Long   'How far did we get?
On Error GoTo ErrorHandle
sFile = Application.GetOpenFilename()
If sFile = "False" Then Exit Sub




lStep = Application.InputBox("Max number of lines/rows?", Type:=1) + 1




lStep = lStep - 1
sText = _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile).ReadAll
vX = Split(sText, vbLf)
sText = ""
Do While lSoFar < UBound(vX)
   If UBound(vX) - lSoFar >= lStep Then
      ReDim vY(lStep)
      lMax = lStep + lSoFar
   Else
      ReDim vY(UBound(vX) - lSoFar)
      'Last row to copy is last row in vX
      lMax = UBound(vX)
   End If


   If lSoFar = 0 Then
   lNb = 0
   lMax = lMax
   Else
   lMax = lMax - 1
   lNb = 1
   End If


   For lCount = lSoFar To lMax
   vY(0) = vX(0)
      vY(lNb) = vX(lCount)
      lNb = lNb + 1
   Next
   lSoFar = lCount
   iFile = FreeFile
   lIncr = lIncr + 1
   Open sFile & "-" & lIncr & ".csv" For Output As #iFile
      Print #iFile, Join$(vY)
   Close #iFile


Loop
Erase vX
Erase vY
Exit Sub


ErrorHandle:
MsgBox Err.Description & " Procedure SplitTextFile"
End Sub

The script works fine, except for this line:

Open sFile & "-" & lIncr & ".csv" For Output As #iFile

When the files are created the format is: "filename"<filename>.csv-"incrementno".<incrementno>

I'd like to change this to "filename"-"incrementno"<filename><incrementno>.csv

I've looked at this all morning and tried different permutations of the code, but I can't get this to work.

I just wondered whether someone could look at this please and offer some guidance on how I can achieve this.

Many thanks and kind regards

Chris</incrementno></filename></incrementno></filename>

 

[Special-K99]

No.
That can't be behaving as you suggest.
The last element on that line is ".csv"
Therefore there can be nothing after the .csv
So you can't get

"filename".csv-"incrementno".

It CANNOT be behaving as you suggest.

Recheck the code you have posted.
Run your code again.

The code you posted looks like it will operate EXACTLY as you want.

 

[ir121973]

Hi Special-K99, thank you for taking the time to reply.

I too thought the same, but if you look at the original here Split a text file into smaller files with Excel VBA, you'll see that, other than copying the header row for each cut down file, my code is exactly the same. I've also run the original and I still get the same output.

Kind Regards

Chris

 

[Special-K99]

You say you are getting files with this format

"filename".csv-"incrementno"

So the extension is csv-"incrmentno"

Are you sure you are not getting this format

"filename".csv-"incrementno".csv

Print the value of sFile to a messagebox before creaqting the output file.
If it displays ".csv" then you need to remove that extension from the sFile variable

 

[ir121973]

Hi Special-K99, thank you for this.

I used the message box, and as you said it did show the filename.csv.

However upon changing the line to Open sFile & "-" & lIncr For Output As #iFile the file still saves with the incremental number after the .csv

Many thanks and kind regards

Chris

 

[Special-K99]

You need to remove the .csv from the variable sFile before the "Open sFile..."
Maybe

sFile=Left(sFile,Len(sFile)-4)
Open sFile & "-" & lIncr For Output As #iFile

 

[ir121973]

Hi @Special-K99, thank you for coming back to me with this and my apologies for no coming back to you sooner.

Unfortunately, this didn't work and ended up creating files, both with and without the incremental, in addition to some strange file structures.

I just wanted to let you know that I have now abandoned this script and have started from scratch, a more simplified example I found which I'm now adapting.

Many thanks and kind regards

Chris