Macro to open CSV files

  • Thread starter Thread starter rhhince
  • Start date Start date
R

rhhince

I wish to open CSV files and grab only the last 5000 lines of it to copy and paste in another xls file. Example: I open a CSV that has 5250 rows of data, but I only wish to copy the last 5000 rows. I am forced to delete the top 250 rows before being able to copy the 5000 rows of data. Is there a macro command to simply go to the bottom and grab those last 5000 rows to copy..
 
I wish to open CSV files and grab only the last 5000 lines of it to copy and paste in another xls file. Example: I open a CSV that has 5250 rows of data, but I only wish to copy the last 5000 rows. I am forced to delete thetop 250 rows before being able to copy the 5000 rows of data. Is there a macro command to simply go to the bottom and grab those last 5000 rows to copy.
Click to expand...

Hi rhhince,
A bit clunky but try something like this.

Option Explicit

Sub AllButTwoFiveOh()
Range("A10000").End(xlUp).Offset(-5000, 0).Resize(5000, 1).Copy Range("F1")
End Sub

Regards,
Howard
 
Hi rhhince,

A bit clunky but try something like this.



Option Explicit



Sub AllButTwoFiveOh()

Range("A10000").End(xlUp).Offset(-5000, 0).Resize(5000, 1).Copy Range("F1")

End Sub
Click to expand...

Or from the top down.

Sub AllButTwoFiveOhXX()
Range("A1").Offset(250, 0).Resize(5000, 1).Copy Range("F1")
End Sub

Howard
 
Hi rhhince,

A bit clunky but try something like this.



Option Explicit



Sub AllButTwoFiveOh()

Range("A10000").End(xlUp).Offset(-5000, 0).Resize(5000, 1).Copy Range("F1")

End Sub



Regards,

Howard
Click to expand...

Actually the CSV file changes often. Sometimes 5500 or 5850, but which to only grab the last 5000. That would not work, but thnaks.
 
Actually the CSV file changes often. Sometimes 5500 or 5850, but which to only grab the last 5000. That would not work, but thnaks.
Click to expand...

Does the first code I posted not grab the last 5000 rows?

Howard
 
Try this pasted into a standard module...

Sub GetLastData()
Dim sFilename$, vDataIn, vDataOut(), v
Dim lStart&, n&, k&, j&, lCols&
sFilename = Get_FileToOpen: If sfilename = "" Then Exit Sub
vDataIn = Split(ReadTextFileContents(sFilename), vbCrLf)
lStart = UBound(vDataIn) - 5000: If lStart < 0 Then lStart = 0
v = Split(vDataIn(0), ","): lCols = UBound(v) + 1
Redim vDataOut(1 To Ubound(vDataIn) + 1, 1 To lCols)
For n = lStart To UBound(vDataIn)
v = Split(vDataIn(n), ","): k = k + 1
For j = LBound(v) To UBound(v)
vDataOut(k, 1) = Split(vDataIn(n), ",")
Next 'j
Next 'n
With Range("A1").Resize(UBound(vDataOut), lCols)
.Value = vDataOut: .Columns.AutoFit
End With 'Range("A1").Resize
End Sub 'GetLastData

Function Get_FileToOpen$(Optional FileTypes$ = _
"All Files ""*.*"", (*.*)")
Dim v As Variant
v = Application.GetOpenFilename(FileTypes)
If (v = False) Then Get_FileToOpen = "" Else Get_FileToOpen = v
End Function 'Get_FileToOpen()

Function ReadTextFileContents$(Filename As String)
' Reads large amounts of data from a text file in one single step.
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFileContents = Space$(LOF(iNum))
ReadTextFileContents = Input(LOF(iNum), iNum)

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFileContents()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops! Major mistake...
Sub GetLastData()
Dim sFilename$, vDataIn, vDataOut(), v
Dim lStart&, n&, k&, j&, lCols&
sFilename = Get_FileToOpen: If sfilename = "" Then Exit Sub
vDataIn = Split(ReadTextFileContents(sFilename), vbCrLf)
lStart = UBound(vDataIn) - 5000: If lStart < 0 Then lStart = 0
v = Split(vDataIn(0), ","): lCols = UBound(v) + 1
Redim vDataOut(1 To Ubound(vDataIn) + 1, 1 To lCols)
For n = lStart To UBound(vDataIn)
v = Split(vDataIn(n), ","): k = k + 1
For j = LBound(v) To UBound(v) vDataOut(k, j + 1) = v(j)
Next 'j
Next 'n
With Range("A1").Resize(UBound(vDataOut), lCols)
.Value = vDataOut: .Columns.AutoFit
End With 'Range("A1").Resize
End Sub 'GetLastData
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top