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,598 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now