Get file size and include it in a message box

[clhare]

I have a template that creates a .prn printer file and then at the end of the macro it tells the user where the .prn file was saved. I am trying to update my macro's message box so that it will include the file size of the .prn file. (My macro already has a string assigned for the .prn file's folder path and filename.)

I updated my code to include 2 new lines to call a function and then added the file size into the message box as shown below:

...

Dim sSize As Integer
sSize = GetTheFileSize(strPath & strNewFileName)
     
' Let user know where to find the PRN file
MsgBox prompt:=""The PRN file you created has been saved as:" & _
    vbNewLine & "      " & strPath & strNewFileName & _
    vbNewLine & vbNewLine & "The PRN file size is: " & sSize & " KB", _
    Title:="SMART Processing: Print to File Completed", _
    buttons:=vbOKOnly
...

Then I added the following function at the end of the module:

Public Function GetTheFileSize(sPath As String) As Long
' This Function returns the Filesize in Kb
    
Dim iChannel As Integer
     
' Get free channel (file number)
iChannel = FreeFile
     
' Input file by that channel (file number)
Open sPath For Input As iChannel
     
' Return file size
GetTheFileSize = Format((LOF(iChannel) / 1024), "#.0")
     
End Function

When I run the template's macros, I get a file size of 0 KB. I tried setting a breakpoint at the line that calls the function and re-running the template to see if I could figure out what wasn't working... but then it worked! After several tests with a breakpoint and without it, I find that it only works when I have a breakpoint set. I can't figure out how to get this to work correctly without the breakpoint.

P.S. I've actually tried a couple of different ways to get the file size (as found on the internet), but I keep running into the same problem... the result displays as 0 even though the file is not 0.

Please help!!

Cheryl

[fumei]

Sub CurrentFileSize()
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.file

Set fso = CreateObject("Scripting.FileSystemObject")
Set fil = fso.GetFile(ActiveDocument.FullName)
MsgBox fil.Size
End Sub

Requires Reference to Microsoft Scripting Runtime.

[fumei]

Actually you can do it even shorter.

Sub CurrentFileSize()
Dim fso As Scripting.FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox fso.GetFile(ActiveDocument.FullName).Size
End Sub

And of course you would also want to turn that into your Function.

Public Function GetTheFileSize(sPath As String) As Long
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
GetTheFileSize = fso.GetFile(sPath).Size
End Function

[macropod]

Actually you can do it even shorter.

Sub CurrentFileSize()
Dim fso As Scripting.FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox fso.GetFile(ActiveDocument.FullName).Size
End Sub

And of course you would also want to turn that into a Function.

The OP will also need to use ''strPath & strNewFileName' instead of ActiveDocument.FullName', which means setting up the function so you can pass those parameters to it.

Function GettFileSize(StrNm As String)
    Dim fso As Scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    GettFileSize = fso.GetFile(StrNm).Size
End Function

[fumei]

I thought I did that with using sPath in the function.

[macropod]

I think there was a bit of an overlap. You didn't have your function in the post when I started my reply. You updated the post at 8:13 and I replied at 8:15.

[fumei]

Timing is everything. And yes I edited to add the function.

[snb]

Sub M_snb()
 msgbox replace("The PRN file you created has been saved as:~" & vbtab & strPath & strNewFileName & "~~The PRN file size is: ","~",vblf) & FileLen(strPath & strnewfilename)
End Sub