Solved: OpenText Method: Excel remembers Last Import!

[shrivallabha]

Following is the code I've used for importing a Text File:
1. It imports Text File (keeping data in one column).
2. Changes the font to Courier (Fixed Pitch font) so that print looks similar to Text File.
3. Adds page breaks to separate Data Chunks for easier read (Hard Copy)

Here it is:
[VBA]'This opens selected 'Text' file in specified 'Excel' format
GetOpenFile = Application.GetOpenFilename
Workbooks.OpenText Filename:= _
GetOpenFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, TrailingMinusNumbers:=True

'This copies and saves data in MTO file.
ActiveSheet.Select
MTOfileLoc = Workbooks("SETPAGE").Sheets.Count
ActiveSheet.Copy After:=Workbooks("SETPAGE.xls").Sheets(MTOfileLoc)
SheetNo = ActiveWorkbook.Sheets.Count
Worksheets(SheetNo).Select
SheetName = Workbooks("SETPAGE").Sheets(SheetNo).Name
Windows(SheetName).Activate
ActiveWindow.Close

Sheets(SheetName).Columns("A:A").Select
With Selection.Font
.Name = "Courier"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Sheets(SheetName).Columns("A:A").EntireColumn.AutoFit

'VBAX: Thank you.

Dim c As Range
Dim FirstAddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String

Prompt = "What do you want to search for?"
Title = "Search Term Input"
Search = InputBox(Prompt, Title)
If Search = "" Then
Exit Sub
End If
With ActiveSheet.UsedRange
Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Address = FirstAddress Then
Set c = .FindNext(c)
Else
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
'VBAX: Thank you

Sheets(SheetName).Range("A1").Select[/VBA]

This code runs absolutely fine if:
1. Excel instance is fresh and no other Text File is imported.
2. If a Text File is imported (especially using ':' as delimiter) then this code goes haywire.
3. It imitates importing i.e. delimits ':' which I do not want.
4. Closing and restarting is helping me but that is not good solution.

I want "Excel" to forget its previous import and I've reached a point where I just can't think of an idea Please

[p45cal]

Excel also remembers settings with .Find.
The solution is to be explicit each time. There are loads of arguments for OpenText:
expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
and you've only been explicit about 4 of them. You don't necessarily need to specify all of them, just those that mess up. Help is your friend.
It's worth scanning the whole Help item as OpenText is full of Gotchas, as you've already found.

[shrivallabha]

Thank you p44cal! The reason for those only four cropping up was I had used Macro Recorder (hardly the programmer's way ) which I use frequently to find out a syntax and then cleaned the code by removing each item step by step.

Found one. This is more of duping Excel.

At post #1:
[VBA]GetOpenFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, TrailingMinusNumbers:=True[/VBA]

Now:
[VBA]GetOpenFile, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, TrailingMinusNumbers:=True[/VBA]

This I did because, we rarely use Fixed Width parse-ing. But now, the importing method robs all the spaces from the textfiles. Tried a few combinations but no success . Any advice will be greatly appreciated.

Of course, I am having Plan C for this, if this method fails me. That plan is to read in the text file by using:

[VBA]Open Filename For Input As #1[/VBA]

But I am trying to figure out with the first method that I've taken up! Thank you in advance.

[p45cal]

You should keep:
DataType:=xlDelimited

What is the delimiter? if it's not a Tab, semicolon, comma or space then (for example let's say it's a Z) include in the line:

Other:=True, OtherChar:="Z"

What is the Delimiter?

I just get this info from the Help file, it really tells you all you need to know.

[shrivallabha]

1. I do not want the data to be delimited in multiple columns.

2. The whole data should come in one column.

3. The code at post #1 runs fine as long as there has been no text file import for the specific Excel instance.

4. If there is some delimiting action done (i.e. on the same Excel instance)then the above code goes haywire.

And that is the route of the problem.

By the way normal delimiter is ':'

I am attaching a Sample Text File. At the moment, I do not have the latest copy of the updated work in Excel. I'll upload that file tomorrow!

Thank you for your support.

[p45cal]

I am attaching a Sample Text File.

No you haven't..

[p45cal]

OK, so you were right about datatype, try including:
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

[shrivallabha]

It is giving some errors while updating so here's a sample

This material Take Off is for Area -A
For Project - 6250_MBPP
$L:P-3340001 :01:A22A : *

$S:- : : : : *

:P :2 : :5.6 : : : *

:E :2 : :2 : : : *

:VA :2 : :1 : : : *

:G :2 : :2 : : : *

:B :2 : :2 : : : *

:F :2 : :2 : : : *

$L:P-3340002 :01:A2A : *

$S:- : : : : *

:P :2 : :5.6 : : : *

:P :4 : :10.5 : : : *

:E :4 : :2 : : : *

:E :4 : :2 : : : *

$L:P-3340003 :01:A3A : *

$S:- : : : : *

:P :8 : :8.6 : : : *

:E :8 : :2 : : : *

:VA :8 : :1 : : : *

:VC :8 : :1 : : : *

:VB :8 : :1 : : : *

:F :8 : :2 : : : *

:G :8 : :2 : : : *

:B :8 : :2 : : : *

:ZFS:8 : :1 : : : *

:F :8 : :2 : : : *

:G :8 : :2 : : : *

:BJ :8 : :1 : : : *

:T :8 : :1 : : : *

I will get back with the results tomorrow. Thank you for sticking out .

[p45cal]

OK. Try suggestion im msg#7, try to attach a file and I'll look out for your response tomorrow.

[shrivallabha]

The problem is half solved. Situation is pretty similar to post #3. If the outcome is not as it is desired then I will bite the bullet with Plan C. But I do appreciate your kind support and invaluable time.

I am attaching the Excel File with macro. There are two cases when the application uses text files:

Both buttons reside on the first page of form namely
1. Import Text File 2. Create Check File

1. To edit file, since editing in text file is very cumbersome. Its sub routine is:
[VBA]Private Sub ImportTextFile_Click()[/VBA]

2. The second (which is the bone of this post) - first generates a text (for check) file of the selected worksheet and then adds page-breaks at specified criteria. Such file then can be printed in page wise manner. Its sub routine is:
[VBA]Private Sub ReviewFile_Click()[/VBA]

The file is in Excel 2003.

Then there is a sample text file.

[p45cal]

I've taken a look at the text file and I think you may have a problem.. the text file doesn't contain spaces! Instead of spaces (ascii code 32), it seems to have a lot of ascii code 00 characters.
How has this txt file been created?
I'd guess it was from another operating system?
Have you changed its extension from something else to .txt?

[shrivallabha]

The macro can generate the file on its own and in fact, is used as basic input to another software. Even you will be able to generate an output using the menu at top (first page).

What I've used is 'Chr(0)' which is ASCII 32 I guess in formula like below.
[VBA]BindString = BindString & .Cells(r, 2).Value & String(24 - Len(.Cells(r, 2).Value), Chr(0)) & ":"[/VBA]

Does it mean trouble ?

[p45cal]

Chr(0) is not ASCII 32. Chr(32) is ASCII 32.
I suggest replacing this if it will still work as input for the other software.
You should be able to .opentext your files properly then.

[shrivallabha]

That solves it! If there's an error which I do not think will happen as it had more to do with formatting than characters for the other program. I do this on my own time so there will be no "official" burden as it is

Thanks a lot. That saves me from biting the bullet. And 'bite' reminds me that I do owe you one. Any day, you chance a trip to Mumbai :

PS: Even after several days on this forum: when I click on thread tools, it guides me to bottom but I am yet to figure out "Mark as Solved" option. Probably, dumb word is not limited to blondes I guess there are a few assorted Indians like me

[mdmackillop]

Even after several days on this forum: when I click on thread tools, it guides me to bottom but I am yet to figure out "Mark as Solved" option. Probably, dumb word is not limited to blondes I guess there are a few assorted Indians like me

What browser are you using? Not all work well here.

[shrivallabha]

I am using Google "Chrome". Does it mean problem?

[mdmackillop]

I am using Google "Chrome". Does it mean problem?

Yes. Chrome does not show all the controls.

[shrivallabha]

Its chrome that causes me the problem!
IE 8.0 has it.
Thanks MD, you are always a good help.