Report file size from file paths specified in cell - wildcards

[choughton]

Hi,

I have a rather specific query i need help with.

I have a spreadsheet which has a series of cell values that relate to daily files we receive from around the business.

As part of a wider process i run a series of macros to copy and move these files from their source folders.

I would now like to report the sizes of these files as a control check to flag files that are empty, but i'm having trouble doing this for files where i don't always know their full name - e.g. where the file name contains a time stamp.

Example file paths:

[TABLE="width: 500"]
<tbody>[TR]
[TD]File path[/TD]
[TD]File size KB[/TD]
[/TR]
[TR]
[TD]\\DocDirect_File1_detail\HOLD folder\ND_730NA_20190618.csv[/TD]
[TD]66879[/TD]
[/TR]
[TR]
[TD]\\Source files\OWN BRANDS\OB_I2AR_FILE_1_20190617.csv[/TD]
[TD]152[/TD]
[/TR]
[TR]
[TD]\\Source files\OWN BRANDS\Cardnet Chargebacks\Chargebacks * 20190616.csv[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So for the first 2 file paths listed above, i know the exact file name, but the third file path (the 'Chargebacks' file) there is a unique report reference in the file name i don't know so i use a wild card (*) to pick up the file and move it, which works fine, but i can't seem to report on the file size using the additional code i've written, whereas i can for the other 2.

The code i've written specifically to report on the file size is as follows (bear in mind there are about 50 files so i use a loop to search through them all). I know the issue is to do with using the 'File Item' as a String, as it's not the exact file name, so what should i use instead? Any suggestions?:

Sub Test()


Dim FileItem As String

Range("C$2").Select
Do While ActiveCell.Address <> "$C$49"
ActiveCell.Offset(1, 0).Select

If ActiveCell.Value = "" Then
ActiveCell.Offset(0, 1).Value = ""

Else
On Error Resume Next
FileItem = ActiveCell.Value

ActiveCell.Offset(0, 1).Formula = Round((FileLen(FileItem) / 1024) + 0.5)


End If

Loop

Application.ScreenUpdating = True
End Sub

 

[choughton]

Bump