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,608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 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