how to close file automatically

[Joy]

In our macros, we will write our data to csv file.:

FileOpen Name:=GetTempDir & "\Import.csv", FormatID:="MSProject.CSV",
Merge:=2, map:="****- Import Map"


sometimes, during the running of macros, the file is opened itself. (i could
not figure out the reason now), and this cause a VBA error:
Method 'FileOpen' of Object '_MSProject' failed

this is not convenient for users.

is there any way to detect that it is open before write data into he file,
and close it for user?

or is there another way to hide it from the user? cos sometimes, if the
error msg is thrown out, users will get confused….

thanks

 

[Jack Dahlgren MVP]

Use an error handler to either skip the step or display a useful error
message.
Use the "On Error" method.

-Jack Dahlgren

 

[Joy]

you mean add

onerror go to 0

before the statement?

thanks

 

[Jack Dahlgren MVP]

Yes, but also add something to handle the error. Here is a snippet of
something I've been working on which opens an excel file and writes the
error message to a textbox on the form

On Error GoTo errorHandler
Set xlfile = xlApp.Workbooks.Open(tboxFileLocation.Value, , True)
lblTestResults.Caption = "Successfully opened " & tboxFileListLocation.Value
xlfile.Close
xlApp.Quit
Set xlfile = Nothing
Set xlApp = Nothing

End If
errorHandler:
If Not Err.Number = 0 Then
lblTestResults.Caption = tboxFileListLocation.Value & Err.Description
End If
Exit Sub

-Jack Dahlgren