ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

File access with Microsoft Scripting Runtime

It is possible to use the Microsoft Scripting Runtime library to manipulate text files. Microsoft Scripting Runtime is included in these products: Windows98, Windows2000, IE5, and Office2000. The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Scripting Runtime.

Sub WriteToTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream
Dim l As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", _
        ForWriting, True)
    With f
        For l = 1 To 100
            .WriteLine "This is line number " & l
        Next l
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub

Sub AppendToTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream
Dim l As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", _
        ForAppending, True)
    With f
        For l = 1 To 100
            .WriteLine "Added line number " & l
        Next l
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub

Sub ReadFromTextFile()
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream
Dim l As Long
    Set fs = New FileSystemObject
    Set f = fs.OpenTextFile("C:\FolderName\TextFileName.txt", _
        ForReading, False)
    With f
        l = 0
        While Not .AtEndOfStream
            l = l + 1
            Cells(l, 5).Formula = .ReadLine
        Wend
        .Close
    End With
    Set f = Nothing
    Set fs = Nothing
End Sub

 

Document last updated 1999-12-17 12:48:50      Printerfriendly version

User comments:
Ole P. from Norway wrote (2006-04-09 18:09:38 CET):
Re: FSO Advantage VS VBA File Ops?
FSO has the advantage of offering the programmer more advanced file/folder functionality that you otherwise would have to program yourself when using VBA built-in file handling functionality.
But sometimes you want to have a little bit more control and use the built-in VBA file functions, e.g. when the anti-virus software causes problems.
JGH from Near Louisville, Kentucky wrote (2006-04-08 20:12:10 CET):
FSO Advantage VS VBA File Ops?
Just wondering what are Pros/Cons of Scripting ...File System Object versus the built-in VBA file operations? I use VBA a lot but until recently did not do any text file ops. Finally I wanted a lightweight repository for machine setups in our plant, that are developed in MS Excel. I didn't want repository in an Excel file and didn't see a need for XML. So I chose CSV in txt file, and got skeleton for my sub from you! Later it is fed to an array that provides starting cell values in new Excel machine-setup file. Would the built in file ops be more reliable? Only thing I can think of is when running from different PCs maybe reference to scripting runtime has to be done again manually? I'm a regular Erlandsen visitor - thank you for your helpful tips. I've learned quite a bit from you over the past few years.
Stanley Rozewski, CPA from Chicago, Illinois USA wrote (2004-01-18 05:00:57 CET):
Excellent Macro-It works beautifully.
I work for a tile contractor, where salesmen prepare quotes for jobs they are bidding on. A quote can consist of 4 different files, because they quote on separate rooms in a house. Also, each salesman maintains their own files in their own folders and they, of course, name each of their files differently, not consistently.

Your macro was a lifesaver for me. I was able to generate a complete listing of all files and folders, and locate many files I needed to find to support the salesmen's original quote, and that allowed me to determine how much profit was originally estimated in the quote, based on the amount they charged for labor and material.

Also, I developed a macro, in connection with your macro, that opens a workbook with the path that your macro provided. The macro opens that workbook with the value on the activecell. So that has been very helpful.

Also, then I modified your macro, that lists all paths and files within a specific folder, below the path in the current workbook, below the activecell. I save this list in an Excel file that serves as the Job Master file for each job, and allows me access to all relevant quote files related to a specific job.

Thanks so very much for sharing your knowledge.
Stanley Rozewski, CPA Chicago, Illinois USA

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address