PDA

View Full Version : Solved: OpenText Method and Find Replace Syntax



shrivallabha
03-09-2010, 08:35 AM
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 :devil2: 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.

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

Awaiting your kind suggestions.

shrivallabha
03-10-2010, 06:20 AM
Funny thing I've noticed the Delimiter is ':'
which is incorrectly indicated as (Delimiter = :)
Thats what the syntax did :)

mdmackillop
03-13-2010, 11:57 AM
Can you post a sample txt file? You may need to zip it.

shrivallabha
03-16-2010, 12:57 AM
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'.:help

Awaiting your response. Thank you for your kind support.

Aussiebear
03-16-2010, 02:22 AM
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

Range("A2:H65536").Select
Selection.ClearContents

as in
Range("A2:H65536").ClearContents

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

shrivallabha
03-16-2010, 08:29 AM
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
03-17-2010, 02:02 AM
Post back with your changes.

shrivallabha
03-29-2010, 10:26 AM
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
03-29-2010, 11:45 AM
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.


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

shrivallabha
03-30-2010, 08:13 AM
It worked perfectly. Thank you very much.

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

GTO
03-30-2010, 08:26 AM
Click 'Thread Tools' atop your first post. The 'Solved' option shows only to the originator of the thread. :-)