Error 53 at 'Open filename for Output'

[Tony007]

I try to make a macro for saving an Excel sheet to a .CSV file with "@" as delimiter.
At the line "Open fname For Output As #fnum", the Error 53, "no file found" occurs.
The Excel-help is saying that if the file is not present, a new file will be made.
This error occurs now and then, How to proceed?
Full macro:

Sub SaveAsCSV()
Path = "O:\actueel\"
Fname1 = Path & Range("E2").Value & ".csv"
fname = Application.GetSaveAsFilename(Fname1, "CSV bestand (*.csv), *.csv", , "")
If fname = False Then
MsgBox "Macro Geannuleerd"
Exit Sub
End If
sep = "@"
fnum = FreeFile
Close #fnum
Open fname For Output As #fnum
lastrow = Range("K65536").End(xlUp).Row
For Row = 2 To lastrow
A = Cells(Row, 4).Value
B = Cells(Row, 1).Value
Print #fnum, A & sep & B & sep 
Next Row
Close #fnum
End Sub

 

[xenou]

I suppose I am addicted to the filesystemobject so this uses it instead:

Sub SaveAsCSV()
Dim FSO As Object [COLOR="SeaGreen"]'//FileSystemObject[/COLOR]
Dim ts As Object [COLOR="seagreen"]'//TextStreamObject[/COLOR]
Dim myPath As String
Dim Fname1 As String
Dim s As String
Dim lastRow As Long
Dim myRow As Long

[COLOR="seagreen"]'//Create filesystemobject[/COLOR]
Set FSO = CreateObject("Scripting.FileSystemObject")

myPath = "O:\actueel\"
Fname1 = myPath & Range("E2").Value & ".csv"

[COLOR="seagreen"]'//abort if file already exists[/COLOR]
If FSO.FileExists(Fname1) Then
    MsgBox "Macro Geannuleerd"
    Exit Sub
Else
    
    [COLOR="seagreen"]'Write to file[/COLOR]
    On Error GoTo Handler:
    Set ts = FSO.OpenTextFile(Fname1, 2, True, -2) [COLOR="SeaGreen"]'Parameters: FileName, For Writing, Create new file, Default Character Format (or -1 for Unicode, 0 for Ascii)[/COLOR]
    lastRow = Range("K65536").End(xlUp).Row
    For myRow = 2 To lastRow
        s = Cells(myRow, 4).Value & "@" _
            & Cells(myRow, 1).Value & "@"
        ts.Write s [COLOR="seagreen"]'//No line break.  Use writeline if new lines are desired instead[/COLOR]
    Next myRow
    
End If

Handler:
On Error Resume Next
ts.Close [COLOR="seagreen"]'//Close text stream[/COLOR]
End Sub

Note, in regard to the original code, I don't see why your error occurs but it seems unnecessary to use the getsaveasfilename method when you already know the name of the file...

Also using #fnum without initializing might default to 0 which I'm not sure if you can use 0 as a file number (usually we start with 1 or use the freefile method to pluck a number).

Hope this helps,
Alex

 

[Tony007]

Alex,
Thanks for you for your reaction.
The GetSaveAsFilename is used to let the user decide in which (project-) directory the file has to be saved.
I get the value 1 from the Freefile variable.

When i use your code, the Error 91 (object variable or with block variable not set) occurs at the line
"if FSO.FileExists(fname) then"
and also at
"Set ts = FSO.OpenTextFile(Fname1, 2, True, -2)"

Any ideas???

Thx in advance

 

[Andrew Poulsom]

Your code worked perfectly when I tested it, even if the Path didn't exist (current path used). What is assigned to fname when it fails?

 

[Tony007]

fname is everytime the same, when it works ok, and when it fails. I even tested it with the line
fname = "C:\test.csv", that means not with the getsaveasfilename.

Once it works, it works for 1hr, even if i restart Excel, later on, Error 53 occurs again (with the same VBA code)

 

[RoryA]

Is O: a network drive or removable drive? I would suspect some sort of intermittent communication error if so.

 

[Tony007]

Addition: Not only on my computer the code fails, but also on 2 computers of my collegues. Same Error 53.

 

[Andrew Poulsom]

Maybe it's a network connection issue.

 

[Tony007]

O: is a network drive

 

[Tony007]

Maybe it is a network issue.
I will change the code and use
fname = "testing.txt"
and see if this works.