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.
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.