Solved

weird problem - with creating and write file in Excel VBA - run time error 53. in VB it work fine.

Posted on 2006-06-19
9
1,601 Views
Last Modified: 2008-01-09
Hi,

This is very weird problem.
I want to open a file and add it some lines.
I'm using this syntax. It work OK in visual basic. However in VBA I get runtime error 53 : file not found. and the file  DOES exist. even if the file doesnt exist it doesnt work -> I want to create it at the first line of the prog. and then add lines to it later in the program.
Anyway, I tried to do it with "for binary" and "for random" - the excel just stuck(sometime it work ,sometime not).
I really need this. Any other solution how to append to file in vba? or fix this?
again, this code run perfectly in visual basic . the only problem is when I run it in Excel Office 2003.


Dim lFreefile As Long
 lFreefile = FreeFile

Open "C:\testbench.txt" For Append As #lFreefile
      Print #1, Str
Close #lFreefile


Anyone have solution?

Thanks,
Eyal.
0
Comment
Question by:eylkrn
9 Comments
 
LVL 13

Accepted Solution

by:
rettiseert earned 200 total points
ID: 16935299
You can use fso to write text files, like this:

        Const file1 = "C:\testbench.txt"

        '*** Const ForAppending = 8  ' Obtained from type library

        Dim Text
        Dim fso                 ' Object variable
        Dim txtStream           ' Text stream

        ' Create a FileSystemObject object to access the file system.
        Set fso = CreateObject("Scripting.FileSystemObject")

        If fso.FileExists(file1) Then  ' Check whether the file exists.
            Set txtStream = fso.OpenTextFile(file1, ForAppending)    ' Output file

            txtStream.WriteLine "Set Born=Hello" ' Append line.

            Set txtStream = Nothing    ' Release object.

        Else
            'File was not found
        End If
0
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 16936065
The problem could be that you are opening a file handle using FreeFile but your Print # line should also include the file handle.

Before:
Print #1, Str

Change to:
Print #lFreefile, Str
0
 
LVL 19

Assisted Solution

by:BrianGEFF719
BrianGEFF719 earned 150 total points
ID: 16939984
The problem is that you have lFreeFile declared as a long. Filehandles are always DOUBLE.
Also, if the file doesn't exists when you open in append mode it should be created anyway.



Here is working code:

Dim dFree As Double
dFree = FreeFile

Open "c:\myText.txt" For Append As #dFree
    Print #dFree, "Hello"
Close #dFree



-Brian
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 17

Assisted Solution

by:inthedark
inthedark earned 150 total points
ID: 16940488
?  Filehandles are always DOUBLE.
Not in the VB help. In there, under FreeFile, the documentation states they are integer.  But this is not going t solve your problem.


I places the followin into Excel 2000

Sub MyTest()

Dim lFreefile As Long
lFreefile = FreeFile
Open "C:\testbench.txt" For Append As #lFreefile
Print #lFreefile, "My test string"
Close #lFreefile

End Sub

It works every time. Which means that your have a different problem.

Do this first:

1) http://office.microsoft.com/en-us/default.aspx

Then click on check for updates.

Also run Windows update.

If you still have the problem....

2) What type of anti-virus is fitted to your system?
3) When this file is created is it being opened by any other process?
4) How much free space is available onyou drive C?
5) In your Excel Help About. What is the build/service release number of your version?



0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 16945531
>>Not in the VB help. In there, under FreeFile, the documentation states they are integer.  But this is not going t solve your problem.



Then why are you using  #)? LOL, If you're going to use a long for a file handle atleast be consistant and use & or if you're going to use an integer again be consistant and use %.

Brian
0
 

Author Comment

by:eylkrn
ID: 16947419
Thanks for everyone on the comments. :)



rettiseert - your solutionworks. thanks. however, still interesting why the orginal doesnt work.

BrianGEFF719 - seem to work also , I'll try it after tomorrow with "load".

inthedark  - I tried it on several computers - indeed wierd. and checked everything(including antivirus etc)


again, thanks for everyone for you helps.

Eyal
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 17306717
Note to BrianGEFF719:
The pound sign (crosshatch, whatever) in eg., PRINT #1 is not an indication of a datatype -- it is a leftover from BASIC syntax -- what's called a language idiom -- from over 30 years ago. :-)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question