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