Results 1 to 2 of 2

Thread: Report file size from file paths specified in cell - wildcards
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Report file size from file paths specified in cell - wildcards

    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:

    File path File size KB
    \\DocDirect_File1_detail\HOLD folder\ND_730NA_20190618.csv 66879
    \\Source files\OWN BRANDS\OB_I2AR_FILE_1_20190617.csv 152
    \\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

    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

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Report file size from file paths specified in cell - wildcards

    Bump

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •