Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
1,620 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 800 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 600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Assisted Solution

by:inthedark
inthedark earned 600 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

610 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