Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1624
  • Last Modified:

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

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
eylkrn
Asked:
eylkrn
3 Solutions
 
rettiseertCommented:
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
 
nffvrxqgrcfqvvcCommented:
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
 
BrianGEFF719Commented:
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
Industry Leaders: 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!

 
inthedarkCommented:
?  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
 
BrianGEFF719Commented:
>>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
 
eylkrnAuthor Commented:
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
 
DanRollinsCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now