Hello

Is there a way to count how many files are there in a folder? thanks

in vb, you can use filesearch in excel up to 2003 vers, lookup that keyword
in help and i think one of the examples does it

in 2007 filesearch has been abandoned and you have to use dir, again the
examples in help contain an appropriate example, but with dir you have to
loop through each file using a counter x=x+1 so x ends up as the result

in both cases you can use wildcars too, so you could count the no of xls files

I have some code but not on me

somone else will no doubt beat me to it!

cheers

"Wanna Learn" wrote:

Hello

Is there a way to count how many files are there in a folder? thanks

Option Explicit
Sub testme()
Dim myFolderName As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
myFolderName = "C:\My Documents\excel"

If FSO.folderexists(myFolderName) Then
MsgBox FSO.getfolder(myFolderName).Files.Count
Else
MsgBox "no folder!"
End If

End Sub

Wanna Learn wrote:

Hello

Is there a way to count how many files are there in a folder? thanks

--

Dave Peterson

Thanks Dave

Do I copy this macro to any document in the folder? Thanks

"Dave Peterson" wrote:

Option Explicit
Sub testme()
Dim myFolderName As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
myFolderName = "C:\My Documents\excel"

If FSO.folderexists(myFolderName) Then
MsgBox FSO.getfolder(myFolderName).Files.Count
Else
MsgBox "no folder!"
End If

End Sub

Wanna Learn wrote:

Hello

Is there a way to count how many files are there in a folder? thanks

--

Dave Peterson

If it's something that you do lots of times, then I would think that it would
only belong in a single workbook.

I saved this from a previous post:

In fact, lots of people put this kind of routine in a workbook named
personal.xls (or .xla or .xlsm or ...).

Then they store this macro workbook in their XLStart folder. Then each time you
open excel, excel will open this workbook. In fact, they usually hide this
workbook (window|Hide in xl2003 menus), then close excel so that they can save
the workbook in that hidden state.

Then you can hit alt-f8 (to see the run macro dialog) to run your macro.

If you plan on sharing your code with others, then don't use the personal.*
name. Since excel can only open one workbook with that name at a time, you'll
be making your users decide which utilities they really need.

Instead, you can name your utility macro workbook:
LostGuyUtils.xls

It can still go into your XLStart folder and the macros will be available all
the time.

If you decide to create an addin, you'll notice that the procedures aren't
visible in the alt-f8 dialog.

Instead, I'd create an addin and tell the users to open the addin when they need
the macro.

So you'll have to give the users a way to run the macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

Wanna Learn wrote:

Thanks Dave

Do I copy this macro to any document in the folder? Thanks

"Dave Peterson" wrote:

Option Explicit
Sub testme()
Dim myFolderName As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
myFolderName = "C:\My Documents\excel"

If FSO.folderexists(myFolderName) Then
MsgBox FSO.getfolder(myFolderName).Files.Count
Else
MsgBox "no folder!"
End If

End Sub

Wanna Learn wrote:

Hello

Is there a way to count how many files are there in a folder? thanks

--

Dave Peterson


--

Dave Peterson