Inexplicable crash in VBA
Guenter
150 have been installed without a hitch - as far as I know under all past and
present versions of Excel. In ONE particular case (which unfortunately
happens to be the french government) the instruction :
OPEN Text_File_Name FOR OUTPUT AS #n
causes the code to terminate WITHOUT ANY SORT OF ERROR MESSAGE (behaves just
like an END statement). This happens in normal RUN mode as well as in
Step-by-Step Debug mode. Obviously since none of the code following the
statement is ever executed the application does not work.
The same error occurs with "APPEND". However, "OPEN FOR INPUT" works
correctly.
The environment is Excel 2000 under XP. The directory referenced exists and
is accessible (I can create a text file in the directory with WordPad). The
error occurs whether the file already exists or not. The Anti-Virus program
on the PC concerned (Kaspersky) was turned off - no difference. The same
error occurs on another PC at the same site with identical configuration.
I'm getting desperate - has anybody out there encountered a similar
situation? I would be very grateful for any sort of help on this!
--
Guenter Kloepper
CoRrRan
in news:BCAF85BA-E2B6-4060...@microsoft.com:
I can't explain why this is happening, but I have a suggestion.
Instead of using "OPEN Text_File_Name FOR OUTPUT AS #n" you could
have a look at the FileSystemObject:
http://tinyurl.com/4goza (<--linked to MSDN)
The FileSystemObject can perform all the functions that you can do
with the "OPEN"-statement.
I know it is not a direct solution to your problem, but perhaps you
can make the application work for the party concerned...
HTH,
CoRrRan
Harald Staff
Every time I've encountered "quiet termination" of a code there has been a
Chr(0) somewhere inside a string value.
Best wishes Harald
"Guenter" <Gue...@discussions.microsoft.com> skrev i melding
news:BCAF85BA-E2B6-4060...@microsoft.com...
Guenter
Thx for the input, but this doesn't seem to be the pb here. I get the error
even if I try a 3-line program in an empty workbook (completely isolated from
my application) and hard-code the path and file name as in
OPEN "c:\Temp\Test.txt" FOR OUTPUT AS #1
This is really, really weird
Guenter
Thx for the input. I checked out the link but it looks like the fso is only
for VB 6. it doesn't seem to be available with Excel VBA (and I need this
thing to be backwards compatible through Excel 97 . . .)
gk
Harald Staff
> Hi Harald,
>
> Thx for the input, but this doesn't seem to be the pb here. I get the
error
> even if I try a 3-line program in an empty workbook (completely isolated
from
> my application) and hard-code the path and file name as in
> OPEN "c:\Temp\Test.txt" FOR OUTPUT AS #1
>
> This is really, really weird
That is inded very very weird. But I got the understanding that your code
terminated without error message, so how can you tell that your 3-line code
errs ? Assuming it looks like this :
Sub test()
Open "c:\Temp\Test.txt" For Output As #1
End Sub
No file generated ? Proably not. Did you try to run it from Word and other
instaled Office programs ? Is the user allowed also to delete files in the
folder or just modify them ?
There are some things in french versions of Windows/Office (?) due to the
country's spesific rules of encryption. I don't know the detalis, but does
this err on all french systems or just a couple ?
Best wishes Harald
Guenter
Well, in my "real" program there is lots of stuff after the "OPEN" that
doesn't get executed, so that's how I localised the problem.
In the 3-liner you can only see it in Step-by-Step Debug mode : the first
and second lines light up in yellow, but when you execute the line with the
OPEN in it, you get the text cursor back and the END SUB line is never
highlighted . . . You can also put message boxes before and after the OPEN
line and see it that way.
The file is not generated.
Didn't think of trying it from Word - that's a good idea. Unfortunately the
customer is 700km away, but maybe I can just do a little routine in a Word
Macro to test the situation - thx for the tip
I created files in the Directory concerned with Note-Pad and deleted the
files with the Explorer so there doesn't seem to be a problem of access
protection
This program works on about 150 different systems in france . . . this site
is the ONLY ONE that presents this problem, so it's not a problem related to
the language version. We have also run the application under German and Swiss
versions of Excel and it works fine there too . . .
Guenter
CoRrRan
in news:9EECEE75-C9A0-44A6...@microsoft.com:
Oh, but it is available!!! Here, a sample code:
***********************************************
Sub CreateTextFile()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\testfile.txt", True)
a.WriteLine ("This is a test...")
a.Close
End Sub
***********************************************
Sub AppendTextFile()
Const ForReading = 1, ForAppending = 8
Const TriStateUseDefault = -2, TriStateTrue = -1, TriStateFalse = 0
Dim fs As Object, a As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.opentextfile("C:\testfile.txt", ForAppending,
TriStateUseDefault)
' This will only append a piece of text right after the last
character in the file
' and should be followed by "& Chr(10)"
a.Write "Appending text" & vbCrLf
' This method will write text directly after the last LINE in the
file"
a.WriteLine "This is the fourth line"
a.Close
End Sub
***********************************************
Sub ReadTextFile()
Const ForReading = 1, ForAppending = 8
Const TriStateUseDefault = -2, TriStateTrue = -1, TriStateFalse = 0
Dim FileSystem As Object, TextStream As Object
Dim msg(20) As String
Dim i As Integer
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set TextStream = FileSystem.opentextfile(FileName, ForReading,
TriStateUseDefault)
msg(i) = TextStream.readline
TextStream.Close
End Sub
***********************************************
This should get you started!
HTH,
CoRrRan
Bob Flanagan
It may be that some of the files have become corrupted or replaced by other
programs.
Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
"Guenter" <Gue...@discussions.microsoft.com> wrote in message
news:BCAF85BA-E2B6-4060...@microsoft.com...
Harald Staff
> The file is not generated.
Error then. Thought so. Can you have the code written in and ran in a
workbook freshly generated on the system where the code runs ? Any skilled
users there ? This for a test to avoid possible bogus references and
translation flaws.
> Didn't think of trying it from Word - that's a good idea. Unfortunately
the
> customer is 700km away, but maybe I can just do a little routine in a Word
> Macro to test the situation - thx for the tip
I'd say either Excel or VBA or Windows is corrupt on the system. A thing
like that 3-liner should simply work no matter what. If Word can do it then
it's Excel. (I'd love a few days in France to test this and enjoy some
Kronenbourgs in the evenings, but neither of us can afford that I guess <g>)
> I created files in the Directory concerned with Note-Pad and deleted the
> files with the Explorer so there doesn't seem to be a problem of access
> protection
Good. My experience is that code like that runs fine with "modify"
priviledges only. But you never know with the french <g>.
> This program works on about 150 different systems in france . . . this
site
> is the ONLY ONE that presents this problem, so it's not a problem related
to
> the language version.
Something must imo be seriously broken. So you just have to narrow it down
so we which part it is. The FSO approach may or may not solve this single
problem, but things like this indicate trouble deep down and you never know
when or where it strikes the next time. Or maybe I'm just paranoid... Please
post back progress or solutions, this is very interesting.
Best wishes Harald