Link to home
Start Free TrialLog in
Avatar of mikekeller988

asked on

Scripting.FileSystemObject doesn't work in Access 2007 - What does?

We have the following bit of code originally created in Access 2000 (Windows XP).  Now that we are upgrading to Access 2007 the code is no longer supported.

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(TxtFileName, 1, TristateFalse)

Please lead me to the means to open, read and edit text files now that the only way I've known isn't working anymore.
Avatar of jmdl1983

Set fs = fs.CreateTextFile("c:\test.txt", True)
Set f = fs.OpenTextFile(TxtFileName, 1, TristateFalse)

Avatar of mikekeller988


The problem is that beggining in Access 2007 you can no longer assign a "FileSystemObject"  ie fs.
Microsoft has "Removed" it.

I need a new way to do this.
Here is the entire sub attached as a code snippet.
Sub ImportFile(TxtFileName As String)
    On Error GoTo Err_ImportFile
    Dim Order_Number As String
    Dim txtLine As String
    Dim ar1() As String
    'Dim Cutrite_EXD As Recordset
    '//  Open the table
    'Set Cutrite_EXD = New ADODB.Recordset
    'Cutrite_EXD.CursorType = adOpenKeyset
    'Cutrite_EXD.LockType = adLockOptimistic
    'Cutrite_EXD.Open "Cutrite_EXD", CurrentProject.Connection
    Dim db As Database
    Set db = CurrentDb()
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("select * from Cutrite_EXD where Pattern_Number = '~'")
    '//  Open the text file
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(TxtFileName, 1, TristateFalse)
    '//  Skip the first 4 lines
    For i = 1 To 4
    Next i
    '//  Read line 5
    txtLine = f.ReadLine
    '//  Split the line up at the slashes
    ar1 = Split(txtLine, "/")
    '//  Assign the Order number to its variable
    Order_Number = ar1(1)
    Dim myPos As Integer
    myPos = InStr(Order_Number, "-")
    If myPos <> 0 Then
       Order_Number = Left(Order_Number, myPos - 1)
    End If
    '//  Skip the next 2 lines
    For i = 1 To 2
    Next i
    '//  Loop through all of the remaining lines
    Do While f.AtEndOfStream <> True
        txtLine = f.ReadLine
        Erase ar1
        ar1 = Split(txtLine, ",")
 'GH & MK 6/26/07 tweaked routine, to illiminate type %4 total records from exd.
        If ar1(0) = "%3" Then
            rs!Order_Number = Order_Number
            rs!Pattern_Number = ar1(1)  '// Pattern_Number
            rs!Board_Identity = ar1(2)  '// Board_Identity
            rs!Width = ar1(3)  '// Width
            rs!Length = ar1(4)  '// Length
            rs!Qty_In_Stock = ar1(5)  '// Qty_In_Stock
            rs!Qty_Used = ar1(6)  '// Qty_Used
            rs!Area = ar1(7)  '// Area
            '//  Commit the data to the table
            '// Do Nothing, so if it = %4 want to skip to next line
        End If
    '//  Close the text file
    '//  Close the table
    Exit Sub
    If Err.Number = -2147217887 Then
        MsgBox "Duplicate Record: Order Number: " & Order_Number & " Board Idenity: " & ar1(1)
        MsgBox "Error: " & Err.Number & " " & Err.Description
    End If
    Resume Next
End Sub

Open in new window

Avatar of Jim Horn
>The problem is that beggining in Access 2007 you can no longer assign a "FileSystemObject"  ie fs.
{Potentially stupid question}
Does this mean you can't go into Tools:Reference and set a reference to 'Microsoft Scripting Runtime', aka FSO?
You can set the reference, but it doesnt' work.

Note this code works great when the use is in Access 2003, but not at all in Access 2007
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much jimhorn