Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to Find the size of the file throwing exception
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to Find the size of the file throwing exception
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) On Thu, 18 Jun 2009 11:54:01 -0700, Bobby wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exception from HRESULT: 0x800A03EC - COM Exception Unhandled | Excel Programming | |||
How do I find invisible objects contributing to excel file size? | Excel Discussion (Misc queries) | |||
EXCEL VBA - Find Method or Range in IE throwing error | Excel Programming | |||
Unable to open excel file and when view the file size show as 1 KB | Excel Discussion (Misc queries) | |||
Unable to change plot area size | Excel Programming |