Solved: OpenText Method and Find Replace Syntax

[shrivallabha]

I am new to VBA and on the learning curve. I need some help and advice in this regard. The details of the activity is as here below:

1. This particular syntax I am using is for importing TXT file in delimited format (Delimiter = . This goes absolutely fine.

2. After this, the file will be edited in Excel. The file imported at step 1 has two columns (No. 3 and 4) in "text" format.

3. Due to inherent nature of Text file every imported data has spaces as added character. When I tried to remove the Spaces (CTRL + H), I noticed that as soon as spaces are removed then the fractions get converted to Date format.
e.g. '1/2' becomes '2-Jan' and '3/4' becomes '4-Mar' etc.

4. This is where the trouble began I tried following
a. Retain the formatting by using the additional option. But the spaces don't
get removed i.e. By making Replace format := True
b. Replace date formatting using additional code. And that is the hiccup as
I do not know what fractions might crop up in future.

5. Following is the code I've created using Excel Macro recording and editing.

[VBA]Private Sub CommandButton7_Click()

'This opens selected 'Text' file in specified 'Excel' format
GetOpenFile = Application.GetOpenFilename
Workbooks.OpenText Filename:= _
GetOpenFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1)), TrailingMinusNumbers:=True
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

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

Awaiting your kind suggestions.

[shrivallabha]

Funny thing I've noticed the Delimiter is ':'
which is incorrectly indicated as (Delimiter =
Thats what the syntax did

[mdmackillop]

Can you post a sample txt file? You may need to zip it.

[shrivallabha]

I am trying to create an application using VBA. Thus I came across this particular situation. I'm attaching herewith, the EXCEL-VBA application developed thus far. It is very primitive based on trial and errors so any comments and suggestions are welcome.

1. Its project password is "SHREE". There is all but one form I've created.

2. I am also attaching TEXT FILE.

And now that I tested it at home, it is giving me

Run time error '9':
Subscript out of range.

And I have zero Idea of what it is as it does not have debug option 'ON'. All I can do is 'End'.

Awaiting your response. Thank you for your kind support.

[Aussiebear]

Here's a couple of really basic hints:

1. Use Option Explicit when coding. This ensures all variables are declared.

2. Name your objects. Commandbuttons, Textboxes etc can all be defined better. I am not interested that much in your project that I'll chase down every commandbutton or textbox to actually find out which one it is. In 12 months time, when you review the code,will you remember which is what?

3. Code like this can be better written
[VBA]
Range("A2:H65536").Select
Selection.ClearContents
[/VBA]
as in
[VBA]Range("A2:H65536").ClearContents[/VBA]

This will give you a start point, by which time others will try to go through your code for further consideration

[shrivallabha]

Thanks Aussiebear for your kind help and advice. I am also reading a Book on the VBA coding by Bill Jelen (Mr. Excel). With your guidance I'll try to improve the code I've written, recorded and edited. Off I go to edit the form.

[Aussiebear]

Post back with your changes.

[shrivallabha]

Extremely sorry to have posted so late as I've been slogging with something else. I am part-timer's part-timer. I've worked on it based on the instructions. Thanks for all the help.
Regards,

[mdmackillop]

Try importing with this code (fixing the path of course). If you look at the image below, you will see you can set the import options for individual columns. That should get you round the date conversion issue.

[vba]
Sub Macro1()
Dim MyPath As String
MyPath = "C:\AA\C_H__.txt" '<=== Change to suit
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyPath, _
Destination:=Range("A1"))
.Name = "C_H__"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(1, 1, 2, 2, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Call Cleans
End Sub
Sub Cleans()
Dim Cel As Range
For Each Cel In ActiveSheet.UsedRange
Cel.Value = Trim(Cel.Value)
Next
End Sub
[/vba]

[shrivallabha]

It worked perfectly. Thank you very much.

Sorry for being naive but how do you mark it as "Solved"?.

[GTO]

Click 'Thread Tools' atop your first post. The 'Solved' option shows only to the originator of the thread. :-)