Macro to open CSV files
 

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

Macro to open CSV files
 

On Sunday, March 31, 2013 10:14:41 PM UTC-7, rhhince wrote:
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.

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

Macro to open CSV files
 

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

Macro to open CSV files
 

On Monday, April 1, 2013 12:27:54 AM UTC-5, wrote:
On Sunday, March 31, 2013 10:14:41 PM UTC-7, rhhince wrote:

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.



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.

Macro to open CSV files
 

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

Macro to open CSV files
 

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

Macro to open CSV files
 

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