Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default 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..
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default 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.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Files with Macro or switch windows with macro Noe Excel Programming 6 September 30th 09 04:06 PM
open files run macro Steve Excel Discussion (Misc queries) 3 June 13th 08 10:43 PM
macro to open in all files Tiya Excel Programming 1 July 11th 06 05:57 PM
macro open files MSHO Excel Programming 1 January 12th 06 05:18 PM
Macro to open *.dat files and save as .txt (comma delimited text files) [email protected] Excel Programming 2 November 30th 05 06:50 AM


All times are GMT +1. The time now is 02:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"