unable to Find the size of the file throwing exception

[Bobby]

When i excute this file. when the cursor reaches the position
" LResult = FileSize(Filename)" It is going to the "ErrorHandler:
ActiveSheet.Columns((GetColumnCount + 1)).ClearContents"

Please let me know what wrong iam doing in this code.


..Public Sub ExportToFile()
On Error GoTo ErrorHandler

Dim ts As TextStream
Dim Filename As String, fileContent As String, tableName As String,
delimiter As String
Dim rowCount As Long, columnCount As Long, dataColumn As Long, pageSize
As Long
Dim pageNumber As Integer
Dim tempRange As Range, tempCell As Range
Dim ws As Worksheet
Dim MyName As String
Dim xpathname As String
Dim fso As FileSystemObject
Dim LResult As Long
Dim ConstLen As Long



For Each ws In ActiveWorkbook.Worksheets
Filename = ws.Name
If Filename <> "Anvil" Then

Worksheets(Filename).Activate
'fileName = GetDefaultFileName(wkSheetName)
If Filename = "" Then Exit Sub

'fileName = Application.GetSaveAsFilename(fileName, "Data Files
(*.txt),*.txt", _
' 1, "Save Data File", "Export")

xpathname = ThisWorkbook.Path
Filename = xpathname & "\" & Filename & ".txt"


LResult = FileSize(Filename)
ConstLen = 0

If LResult = ConstLen Then
ConstLen = 1
ElseIf MsgBox("The file " & fso.GetFileName(Filename) & " already exists.
Do " & _
"you want to replace the existing file?", vbYesNo +
vbExclamation + _
vbDefaultButton2, PROJECT_NAME) = vbNo Then
Exit Sub
End If

Set fso = New FileSystemObject
fso.CreateTextFile Filename, overwrite:=True

Exit Sub
ErrorHandler:
ActiveSheet.Columns((GetColumnCount + 1)).ClearContents
MsgBox MSG2002, vbOKOnly + vbCritical, PROJECT_NAME
End Sub
Public Function FileSize(Filename As String) As Long
Dim objX As Object
Set objX = CreateObject("Scripting.FileSystemObject")
FileSize = objX.GetFile(Filename).Size
Set objX = Nothing
End Function

 

[Chip Pearson]

Specifically what error are you getting? In your error handler block,
use something like:

MsgBox "Error: " & CStr(Err.Number) & vbCrLf & Err.Description

This will display the error number and its description.

In the line of code

LResult = FileSize(Filename)

are you SURE (!) that the file named by FileName does in fact exist?

If not that, then the problem is with the code...

'fileName = Application.GetSaveAsFilename(fileName, "Data Files
(*.txt),*.txt", _
' 1, "Save Data File", "Export")

This sets fileName to the fully qualified file name chosen by the
user. E.g., "C:\Test\Temp\TheFile.txt". GetSaveAsFilename returns the
entire drive\folder\filename, not just the filename.

Then, you have

xpathname = ThisWorkbook.Path
Filename = xpathname & "\" & Filename & ".txt"

If xpathname is "C:\Folder", you're setting the FileName variable to

C:\Folder\C:\Test\Temp\TheFile.txt

which clearly isn't a valid file name.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)