excelvbams-wordfilesystemobjectcreateobject

VBA Word 2013: Scripting.FileSystemObject: Runtime Error 424 - object required

I'm not very firm in VBA and I've been trying to use a function under Word which I wrote in Excel some time ago. (Where it is working flawlessly!)

The Function goes through a text file and returns strings after a certain (sought for) other string:

Function keySearch(ByVal sSearch As String) As String
Dim fso As New FileSystemObject
Dim FileNum
Dim DataLine As String
Dim posOf_A As Integer
Dim filepath As String

keySearch = ""

'Create filesystem object
Set fso = CreateObject("Scripting.FileSystemObject")

'filepath to textfile
filepath = ActiveWorkbook.Path & "\temp.txt"

Set FileNum = fso.OpenTextFile(filepath, 1) '<--- This is where the error occurs!

Do While Not FileNum.AtEndOfStream
    DataLine = FileNum.ReadLine
    posOf_A = InStr(1, DataLine, sSearch, vbTextCompare)
    If posOf_A = 1 Then
        keySearch = Right(DataLine, Len(DataLine) - Len(sSearch))
    End If
Loop

FileNum.Close

End Function

The error occurs in the line where the text file is supposed to be opened. Error message: Runtime Error 424 : Object required.

I've already split the line up as far as possible narrowing down the search from this original line of code from the under Excel flawlessly working function:

FileNum = CreateObject("Scripting.FileSystemObject").OpenTextFile(ActiveWorkbook.Path & "\temp.txt", 1)

But I just don't seem to be able to make it work.. I've seen multiple examples on the Internet doing exactly this in (seemingly) exactly the same way I'm doing it...

P.S.: Microsoft Scripting Runtime is activated.

Thanks in advance for any kind of help which is really appreciated!!

Solution

  • Change ActiveWorkbook.Path to ActiveDocument.Path. You are trying to reference an active excel workbook for the file path.