Methods Failed

[MacroMania]

I am experiencing some errors with my program and can not figure out what is going on. I have a VB Program that calls Excel to create a set of workbooks for each given text file in a directory. However, it errors out on 5 textfiles with the following error "Method 'OpenText' of object 'Workbooks' failed. What baffles me, is that I can manually open this file into Excel. So, I tried working around it and opening the files as objects and assigning each tab-delimited value into each corresponding cell. The first textfile opened fine, but the second file gave me the following error "Method 'Cells' of object '_Worksheet' failed". Strangely, when I went to debug the error, I pressed F8 and it still placed the value into the cell. This error occured again on the next 2 files and I was still able to place in the value. However, the last textfile gave me the following errors "Method '_Default' of object 'Workbooks' failed" and when I went to debug, I received the Memory Error Instructions Crash Dialog Box, and Excel Shut down. Anybody got any advice??

Thanks

 

[nath]

Hi there,

Post the code which is failing, so we can have a look.

Nath

 

[xlbo]

I'm guessing this is an activation problem - you probably have code that needs to have the woekbook maintaining the focus. If the focus has shifted to the textfile and you try and perform a method / operation with the workbook, you will get this error. As Nath says....post the code n we'll have a look Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[MacroMania]

I've been playing with the files and importing data a cell at a time, and I think it might have something to do with the word "TRUE". There are a few strings in the Bad Files and contains strings that begin with "TRUE " or "TRUE, ". If I change "TRUE" to "STRUE" (or add any other character to the beginning), it seems to work. I'm not getting my hopes up until I continue some more tests. However, I read that for Boolean texts, Excel needs to import 1/0 instead of True/False. I think that maybe on the other downfalls is that the strings are not inside qoutes ("&quot, but does a tab delimited file need qoutes???


Here's the code......

Code:
**************************
*** An array is created with a list of text files - filename(I).
*** It loops through each textfile, creating a formatted workbook.
*** However, it errors out on certain files with this code, causing Excel to crash.
*** But, if those bad files are removed it works just fine.
**************************
Workbooks.OpenText FileName:="\\path\filename(I)"

Error:
Method 'OpenText' of object 'Workbooks' failed




Code:
**************************
*** This code is opening textfile as Object and Reading it into Excel
**************************
Workbooks.Add
ExcelFile = Activeworkbook.Name
Open "\\path\filename(I)" For Input As #1
RowCtr = 1
Do Until EOF(1)
Line Input #1, TextLine
TextLineSplit = Split(TextLine, Chr(9), -1)
For SplitCtr = 0 To UBound(TextLineSplit)
Workbooks(ExcelFile).Sheets(1).Cells(RowCtr, (SplitCtr + 1)) = TextLineSplit(SplitCtr)
Next
RowCtr = RowCtr + 1
Loop
Close #1

Error line is:
Workbooks(ExcelFile).Sheets(1).Cells(RowCtr, (SplitCtr + 1)) = TextLineSplit(SplitCtr)

Error:
Method 'Cells' of object '_Worksheet' failed

Multiple Errors Gives Me:
Method '_Default' of object 'Workbooks' Failed
And Crashes Excel

 

[nath]

Your error is because you should write

Workbooks.OpenText FileName:="\\path\" & filename(I)

instead of

Workbooks.OpenText FileName:="\\path\filename(I)"

And equivalent in your second code.

Nath

 

[MacroMania]

I have it set up as FileName:="\\path\" & filename (I)
I had just shortened it to "\\path\filename(I)" because I was in a hurry.

Actually, I've figured out what the problem is, and this is kind of weird, but I guess makes sense. When I use the command "Workbooks.OpenText FileName:="\\path\filename.xls" ", everything that usually appears after FileName is returned to the default state. However, there is a FieldInfo category that lists the columns as Arrays and can be used to determine whether the data in those arrays should be imported as 'General', 'Text', 'Date', or 'Not Imported'. The Default for this category is 'General', so whenever the word "TRUE" appeared, it categorized that data as General and was recognizing it as Boolean Text and not just Plain Text. After adding the following to the code, everything worked accordingly.

Here's the New Code:
Workbooks.OpenText FileName:="\\path\filename.xls", FieldInfo:=Array(Array(2, 2), Array(3, 2))
**** The first 2 in Array(2, 2) refers to column 2. The second 2 refers 'Text'.
**** The same refers to Array(3, 2), with 3 referring to Column 3 and 2 as to import the data as 'Text'.
**** I guess I want to try to cheat or limit my code anymore, or make sure I always import text as 'Text'.

 

[MacroMania]

Just wanted to add, in order for OpenText to work corectly, FieldInfo will need an Array(#, #) for each column, so that each columns is labeled what type of data Excel is importing.

Here's the Code for using FieldInfo for all the columns:
.Workbooks.OpenText FileName:="\\path\filename.xls", FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1))