Macro to open CSV files

[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..

 

[lhkittle]

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.

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

 

[lhkittle]

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

Or from the top down.

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

Howard

 

[rhhince]

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

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

 

[lhkittle]

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

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

Howard

 

[GS]

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

 

[GS]

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

--
Garry

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