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

 

[Ron Rosenfeld]

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 ?

Here's one way:

==========================
Option Explicit
Sub GetDir()
Const sDir As String = "C:\Temp\*"
Dim sFileNamePart As String
Dim aFileNames() As String
Dim lFileNamesCount As Long
Dim i As Long, lStart As Long, lLength As Long
sFileNamePart = Range("A1").Text

If sFileNamePart = "" Then
MsgBox ("You must define a File Name substring in $A$1")
Exit Sub
End If

ReDim aFileNames(0 To 0)
aFileNames(0) = Dir(sDir)
Do Until aFileNames(UBound(aFileNames)) = ""
ReDim Preserve aFileNames(UBound(aFileNames) + 1)
aFileNames(UBound(aFileNames)) = Dir
Loop

For i = 0 To UBound(aFileNames)
If InStr(1, aFileNames(i), sFileNamePart, vbTextCompare) > 0 Then
lFileNamesCount = lFileNamesCount + 1
lStart = InStr(aFileNames(i), "-") + 1
lLength = InStr(lStart, aFileNames(i), "-") - lStart
Debug.Print aFileNames(i), Mid(aFileNames(i), lStart, lLength)
End If
Next i

Debug.Print "Filenames containing " & sFileNamePart & ": " & lFileNamesCount

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)