Getting filename data

[kittronald]

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

 

[Ben]

Hi kittronald,
1) The only way I know to count the number of files is to run through a loop
2) You can use the Split function to get the filename part. Split returns a 0 based array.

Hopfully this will demonstration will make more scene:

Sub test()
Dim scriptFSO As Scripting.FileSystemObject
Dim scriptFolder As Scripting.Folder
Dim scriptFile As Scripting.File
Dim sFileNamePart() As String
Dim lFileCount As Long

Set scriptFSO = New Scripting.FileSystemObject
Set scriptFolder = scriptFSO.GetFolder("C:\temp")
lFileCount = 0
For Each scriptFile In scriptFolder.Files
If scriptFile.Name Like "Oranges*" Then
lFileCount = lFileCount + 1
sFileNamePart = Split(scriptFile.Name, "-")
Debug.Print sFileNamePart(1)
End If
Next scriptFile
Debug.Print lFileCount & " files found"
End Sub

 

[Don Guillett]

1. You could use a DIR loop to open each file and check SHEET name and cell a1 against the file name.
2. Or, you could use DIR loop to make a list in your file and then use a formula to check sheet1!a1 against the filename in your list. NO opening necessary.

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

I'm trying to get information about files in a directory using Microsoft
Excel 2007 with a macro.

Cell A1 contains the text "Orange".

In the directory C:\Temp, there are three files:

Orange0-10-.csv
Orange1-10-.csv
Pear0-10-.csv

1) How can you determine the number of files in a directory whose name
starts with the value in cell A1 and assign that number to a variable ?

2) How can you get the numerical value between the two "-" characters
(i.e., 10) and assign that value to a variable ?



kittronald

 

[kittronald]

Thanks everyone.

Got it working !



kittronald

 

[kittronald]

Ben,

Upon running the macro, the following error occurs on line 2:

Dim scriptFSO As Scripting.FileSystemObject

Compile-error:

User-defined type not defined



kittronald

 

[kittronald]

Ben,

Opening the VB Editor, checking "Microsoft Scripting Runtime" under
Tools\References corrected the error.

However, the following error occurs on the Debug.Print line:

Run-time error '9':

Subscript out of range

Any ideas why this is happening ?


kittronald

 

[kittronald]

Figured it out.

Just had to remove the Debug.Print line.



kittronald

 

[Ben]

Figured it out.

Just had to remove the Debug.Print line.



kittronald

Sorry I wasn't able to reply earlier, and that I messed up the binding on the scripting module. I guess the subscript error was on "Debug.Print sFileNamePart(1)" because it came across a file without the delimiter, so there wasn't a second part in the array to return. (split returns a 0 based array)