File save fails

[Dozie]

Dear colleagues,

Please see the code fragment below and see if you can spot
the problem.

The startTime variable contains the date and time (to
microseconds) to ensure that users do not overwrite each
other on the network drive -- there are about 800 people
completing the survey and confidentiality is important.

During one run, the variable fname contained:
ISPQuestionnaire20040315152507687422.xls. But the file
actually saved was named "73852000" (no extension).

The msgBox reports file successfully created, but the file
existence test failed. We came to this test because we
would later want to save the file, using the fname and we
get error 53 because file does not exist.

The big mystery: it works fine on some machines and not on
others. One one machine that it works, it failed once.

Thanks in advance.

- Dozie



==========begin code fragment ============

' form file name
fname = Trim(folderName & "\ISPQuestionnaire" & _
startTime & Format(Int(100 + 500 * Rnd), "000")
& ".xls")

' save user name before changing to "ISP User" so we
can reset at the end
userName = Application.userName

' Load the answer file template
Workbooks.Open Filename:=tempName

Range("Manager").Value = "This evaluation for: " &
mgr ' already initialized
Application.userName = "ISP User" ' so that staff
member cannot be traced by workbook properties
ActiveWorkbook.SaveAs Filename:=fname
Set answerBook = ActiveWorkbook
Set answerSheet = answerBook.Worksheets("Answers")

MsgBox (fname & " has been successfully created ")

'check if answer template - tempname - exists
If fs.fileexists(fname) = False Then
MsgBox ("Error 05: Answer file was not
successfully created" & vbCrLf & _
"Please call ISS at extn 33123")

' close all workbooks
For Each theBk In Workbooks
If theBk.Name <> ThisWorkbook.Name Then
theBk.Close savechanges = False
End If
Next theBk

Application.userName = userName
ThisWorkbook.Close savechanges:=False
End If

 

[Tom Ogilvy]

Sounds like something interfered with your saving of the file.

Excel works on a copy of the file. It has a name like 73842000. When you
save the file, it deletes the original and then renames the copy. If the
renaming of the copy is not successful, then you end up with a file such as
you describe. This can be caused by network permissions or perhaps some
network based antivirus software. I don't think there is anything in your
code causing this problem.
Another thing to be aware of is the fully qualified filename can not exceed
256 characters I believe. I don't know what your directory structure looks
like,but perhaps this is something else to look at.