Report file size from file paths specified in cell - wildcards


New Member
Jun 18, 2019

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:

File pathFile size KB
\\DocDirect_File1_detail\HOLD folder\ND_730NA_20190618.csv66879
\\Source files\OWN BRANDS\OB_I2AR_FILE_1_20190617.csv152
\\Source files\OWN BRANDS\Cardnet Chargebacks\Chargebacks * 20190616.csv


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

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

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

On Error Resume Next
FileItem = ActiveCell.Value

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

End If


Application.ScreenUpdating = True
End Sub

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics