Excel beginner: counting rows in a external file

[pimar]

Hi all.
Very basic question: I need to fill a cell of my spreadsheet with th
number of lines(row) in an external files (that is automaticall
created by another tool). Which is the best/easy way to do it?

thanks in advanc

 

[Don Guillett]

use this is yourworkbook.xls is open
Sub oss()' IF open
MsgBox
Workbooks("yourworkbookt.xls").Sheets("sheet1").Range("c24:c27").Rows.Count
End Sub

 

[pimar]

Thanks Don for your reply, but this is not what I need.
I'd be more detailed: I use to generate a file (CSV) as result of m
daily job. Then. I use to count the nunber of rows in that file and t
report this number in my spreadsheet for my boss. What I'd like to d
is to let my workbook to automatically read the rows in my CSV file an
save this number in my spreadsheet.
Would be easy to run a very simple batch program from the workbook, i
possible, or to use the excel query. But since I'm very new with excel
I don't know which is the fastest (best) way.
So I'm asking for sggestoins.
Thank

 

[Andy Wiggins]

I've had to do this on a couple of occasions. However, the data was used
further on in the process, not just simple to fill a cell with the number of
rows, so my method might be a bit long-winded for you, but it gets the
result you want. Possibly, from your point of view, this is neither best or
easy, but it does work.

1) Open the external file into an empty Excel worksheet
2) Count the number of rows in the UsedRange
3) Use (2) as the number of rows in the file (after adjusting for any header
row that might exist)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[pimar]

Thanks Andy,
this is what I usually do right now. My problem is that I have a lot
number of files (more than 10) that I should load and then count the
rows...And belive me, it is really boring.. That;s why I'm lokking for
a more efficient (and automatic() way to do this job.

Btw, thanks a lot for your reply
*pmm

 

[Mike Woodhouse]

Thanks Don for your reply, but this is not what I need.
I'd be more detailed: I use to generate a file (CSV) as result of my
daily job. Then. I use to count the nunber of rows in that file and to
report this number in my spreadsheet for my boss. What I'd like to do
is to let my workbook to automatically read the rows in my CSV file and
save this number in my spreadsheet.
Would be easy to run a very simple batch program from the workbook, if
possible, or to use the excel query. But since I'm very new with excel,
I don't know which is the fastest (best) way.
So I'm asking for sggestoins.
Thanks

If you can cope with (fairly simple) VBA, you could try this:

[note that you'll need a reference in your project to Microsoft
Scripting Runtime (scrrun.dll) and Split() is not defined in Excel 97
and before]

Public Function LinesInCSVFile(filepath As String)

Dim fs As FileSystemObject ' Need to create a reference to
Dim ts As TextStream ' Microsoft Scripting Runtime
Dim s As String ' holds the content of the file
temporarily

Set fs = New FileSystemObject ' set up
access to file system
Set ts = fs.OpenTextFile(filepath, ForReading) ' open the
file

s = ts.ReadAll ' get file
content for working

LinesInCSVFile = UBound(Split(s, vbCrLf)) ' create an
array of lines and count them
If Right(s, 2) <> vbCrLf Then ' if the file
ends in as vbCRLF there will be an
LinesInCSVFile = LinesInCSVFile + 1 ' unwanted
extra (empty) entry in the array but otherwise
End If ' we're one
short (Split arrays start at 0)

ts.Close ' tidy
Set ts = Nothing ' up
Set fs = Nothing ' objects used

End Function


HTH,

Mike

 

[Dave Peterson]

Another way using FileSystemObject:

These do the same thing, but the first requires a reference to "microsoft
Scripting Runtime" (tools|References inside the VBE).


Option Explicit
Sub testme01()

Dim FSO As Scripting.FileSystemObject
Dim NumberOfRecords As Long
Dim myFileName As String
Dim myFile As Scripting.TextStream

myFileName = "C:\myfilenamehere.txt"
Set FSO = New Scripting.FileSystemObject
Set myFile = FSO.OpenTextFile(myFileName, ForAppending)

NumberOfRecords = myFile.Line

MsgBox NumberOfRecords

myFile.Close

End Sub

Sub testme02()

Dim FSO As Object
Dim NumberOfRecords As Long
Dim myFileName As String
Dim myFile As Object

myFileName = "C:\myfilenamehere.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set myFile = FSO.OpenTextFile(myFileName, 8) 'forappending = 8
NumberOfRecords = myFile.Line

MsgBox NumberOfRecords

myFile.Close

End Sub

It's easier to develop with the reference set--you get all of the VBE's helpful
intellisense. But right before you give it to the user (your boss??), it's
better to go back to using the object. (This is called late binding (and with
the reference, it's called early binding.)

By using late binding, you don't have to worry about different versions of the
reference on the other pc's.

 

[pimar]

Thanks Dave, thanks Mike,
that was what I'm looking for. That's great.

A couple of questions (sorry, but I'm really bad on VBE and macro i
general). I've used the easiest suggestion from Dave (testme02
routine:
a) is there a way to pass a "parm" to the subroutine?
b) can I call a macro from a cell in my workbook, and have the resul
stored in the cell itself? Something like call a function from th
cell? And pass the filename as parm?

Thanks again for your input...
*pm

 

[Dave Peterson]

You can't call a macro (Sub) from a cell, but you could use a formula:

You can try this instead of the other procedure:

Option Explicit
Function LinesInText(myFileName As String) As Variant

'Application.Volatile

Dim FSO As Object
'Dim FSO As Scripting.FileSystemObject

Dim myFile As Object
'Dim myFile As Scripting.TextStream

'Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(myFileName) Then
Set myFile = FSO.OpenTextFile(myFileName, 8) 'forappending = 8
LinesInText = myFile.Line
myFile.Close
Else
LinesInText = CVErr(xlErrRef)
End If

End Function

(I commented out the stuff to use when you have the MS Scripting runtime
reference. But it helps when you're developing.)

Then in a cell:

=linesintext(a1)

==
Some things to consider. If that file is in unavailable (in use exclusively by
another program), then this'll blow up.

If you uncomment this commented line:
'application.volatile
would make the function recalculate whenever xl recalculates.

This might be useful if that text file can change (often???). But on the other
hand, if that file changes rarely, then it's just overhead for excel.

Depending on how many of these you use, you may notice xl slow down.

 

[pimar]

Hi Dave,
thanks for your reply.
I'm getting still problems: I have opened the VBEditor
(Tools->Macro->VBE), copyed the function as suggested by you (but when
I try to test it nothing happens). When I call the function from the
cell, that's what i get in the cell #NAME! What's wrong?
*pmm

 

[Dave Peterson]

Is the code in the same workbook as the worksheet that contains the function?

Is the code in a General module in that workbook's project?

 

[pimar]

Yes they are, Dave.
But when I insert the function from my worksheet, I get this erro
("the name is not valid"). BTW, should I see my new function in th
list of the available functions when I do an Insert?
Another strange thing, is that if there is the parameter defined in th
function signature, I'm not able to debug the function, while I'm abl
to that if I change the signature removing the param.
Is that normal stuff?

Thanks
*pm

 

[Dave Peterson]

My next guesses are grasping at straws....

What's the name of your function? (Is it still LinesInText?)
What's the name of the module that holds that function? Is it still Module1?
(the function's name should be different than the module's name.)

Do you have any workbook names (like range names) that are the same as the
function?

Do you have macros enabled?

=====
You should see the function name within the UserDefinedFunction Category when
you do Insert|function.

And since this function requires an argument, you can test it by calling it from
a test procedure:

Option Explicit
Sub testme01()
MsgBox LinesInText("C:\autoexec.bat")
End Sub

Then put your cursor on the Msgbox line and hit F8 and see what happens.

(You could also put a break point in the function, and enter the function in a
cell on the worksheet and the break point will cause the execution to, er,
break. Then you could hit F8 to see what's happening.)

 

[pimar]

I got it.
The problem was the name of the worksheet where the function wa
declared. Soryy if I had you to lose time.
Thanks very much, Dave. Your help has been GREAT.
ciao
*pima

 

[Dave Peterson]

Glad you got it working.

I got it.
The problem was the name of the worksheet where the function was
declared. Soryy if I had you to lose time.
Thanks very much, Dave. Your help has been GREAT.
ciao
*pimar