unable to Find the size of the file throwing exception

B

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
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top