GetObject Function On Text Files

[William Horton]

I have the below portion of code in a program that collects a bunch of text
files and imports them into multiple worksheets in one Excel Workbook.

Dim InputFiles As Variant
Dim MyFirstWorkbook As Object
InputFiles = Application _
.GetOpenFilename("Text Files (*.txt), *.txt", , "*** PLEASE SELECT
INPUT FILES ***", , True)
If IsArray(InputFiles) <> False Then
Lower = LBound(InputFiles)
Upper = UBound(InputFiles)
Set MyFirstWorkbook = GetObject(InputFiles(1))

The code line "Set MyFirstWorkbook = GetObject(InputFiles(1))" produces the
error "File name or class name not found during Automation operation." I
realize that I must have to add some sort of class argument to the GetObject
function but do not know what. When I try the function with
GetObject(InputFiles(1), "Excel.Workbook") I get the error "ActiveXcomponent
can't create object." All the input files will be text (.txt) files. What I
need to know is how to adjust the GetObjectFunction class argument (or
something else) to get this to work properly. Can anyone help???

Further on in my code after I have opened a text (.txt) file in Excel using
the Workbooks.OpenText method. There I can successfully use the GetObject
function. I guess once the file is opened in Excel you are able to do this.
The code I am using here is:
Dim MyWorkbook As Workbook
Set MyWorkbook = GetObject(InputFiles(InputFileNo))
However, the text file is already opened as an Excel file when I do this.
That is not the case when I am trying to set the MyFIRSTWorkbook object.

Any help would be greatly appreciated.

Thanks,
Bill Horton

 

[sebastienm]

Hi,
The openText creates a new book and add it to the Workbooks collection. That
is, the index of this new book in the Workbooks collection is now equals to
Workbooks.Count. Try something like:

Dim MyFirstWorkbook As Workbok '<--- workbook instead of object

On Error Resume Next
Workbooks.OpenText ...... '<<<< change here
If Err<>0 Then 'case Open failed
msgbox "Cannot open file
Set MyFirstWorkbook = Nothing
Else 'case Open succes
Set MyFirstWorkbook = Workbooks(Workbooks.Count)
End if
On Error Goto 0

 

[William Horton]

Thanks Sebastienm. I guess I couldn't Get an Excel object of the text file
before it was opened as an Excel file because it was not an Excel object.
What I wound up doing was adding a new Excel workbook and using that as my
MyFirstWorkbook. I did that because my code loops through the portion that
you had given your suggestion in and I didn't want the value of
MyFirstWorkbook to change. I should have thought of and done this from the
beginning.

All I am doing is opening X number of text files in Excel and then moving
all those worksheets into one workbook. Originally I was using the first
text file as the one workbook but decided to create a new Excel workbook to
do that instead.

Thanks,
Bill Horton