Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
unable to create a file if already exiists
I am creating a simple text file. And appending the data to the file. i need
to run the same macro every time. It is fine when i run the script at least once. When i am running for the first time it is creating the file and it is showing the file is exist window. Please help or let me know is there another way of creating a file and error handling if file exists. ************ Set fso = New FileSystemObject fso.CreateTextFile fileName, overwrite:=True If fso.FileExists(fileName) Then If 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 End If Set ts = fso.OpenTextFile(fileName, ForWriting, True) With Range(ActiveWorkbook.Worksheets("Anvil").Cells(1, dataColumn), _ ActiveWorkbook.Worksheets("Anvil").Cells(rowCount, dataColumn)) For Each tempCell In .Cells If tempCell.Row < rowCount Then tempCell.Value = Left(tempCell.Value, Len(tempCell.Value) - 1) Call ts.WriteLine(tempCell.Value) Else tempCell.Value = Left(tempCell.Value, Len(tempCell.Value) - 1) Call ts.Write(tempCell.Value) End If Next End With |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
unable to create a file if already exiists
Looks to me like you're creating the file and THEN testing to see if it
exists, so even the very first time you run the macro, the file exists when you test for it. Perhaps as simple as moving the fso.CreateTextFile fileName, overwrite:=True statement down below your first If ... End If block? I don't use the fso very often, choosing to do it the old fashioned way, so if my suggestion doesn't work for you, give a shout and I'll provide some old fashioned, tried and true, code to test if it exists using DIR$() and ancient commands like OPEN and WRITE. "Bobby" wrote: I am creating a simple text file. And appending the data to the file. i need to run the same macro every time. It is fine when i run the script at least once. When i am running for the first time it is creating the file and it is showing the file is exist window. Please help or let me know is there another way of creating a file and error handling if file exists. ************ Set fso = New FileSystemObject fso.CreateTextFile fileName, overwrite:=True If fso.FileExists(fileName) Then If 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 End If Set ts = fso.OpenTextFile(fileName, ForWriting, True) With Range(ActiveWorkbook.Worksheets("Anvil").Cells(1, dataColumn), _ ActiveWorkbook.Worksheets("Anvil").Cells(rowCount, dataColumn)) For Each tempCell In .Cells If tempCell.Row < rowCount Then tempCell.Value = Left(tempCell.Value, Len(tempCell.Value) - 1) Call ts.WriteLine(tempCell.Value) Else tempCell.Value = Left(tempCell.Value, Len(tempCell.Value) - 1) Call ts.Write(tempCell.Value) End If Next End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to create a file if already exiists | Excel Discussion (Misc queries) | |||
Unable to click on charts & unable to create any new charts Excel | Charts and Charting in Excel | |||
Unable to open excel file and when view the file size show as 1 KB | Excel Discussion (Misc queries) | |||
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. | Charts and Charting in Excel | |||
Unable to create any link/hyperlinks in a particular excel file | Excel Discussion (Misc queries) |