[FONT="]I'm new to VBA and wonder if anyone could suggest a solution to what I want to do. I have recorded it and have some understanding on how to do it. But it is semi-automated and I want to make it free from dependency to the file name on the *.txt files.
What I want to do is to open *.txt files in excel from a certain folder. The *.txt files are with tab-separation replace '.' with ',' move one of the columns in the now *.xls file (E:E) to a different open sheet Joined_282.xlsm save the opened *.xls file with same name as *.xls and close the file. Open next *.txt file in the folder do the same process but move the new copied column to the next column in the sheet A and so on, for all *.txt files in the folder.[/FONT]
[FONT="]I have searched for opening *.txt file and save it as *.xls but not really find any that fulfill what I want to do. Hope you can suggest a suggestion or guide me to right place for understanding how to do it.
[/FONT][FONT="]Thank you,
The recorded code looks like this:
[/FONT] Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+n
'
ChDir _
"C:\Users\282_o\F_s"
Workbooks.OpenText Filename:= _
"C:\Users\282_o\F_s\Vpp1.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Cells.Select
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 1
.Percent = False
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Windows("Joined_282.xlsm").Activate
Range("B1").Select
ActiveSheet.Paste
Range("B34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "PC2"
Windows("Vpp1.txt").Activate
Application.CutCopyMode = False
ChDir _
"C:\Users\282_o\F_s\xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\282_o\F_s\xls\Vpp1.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
I have copied this code several time in the same macro but i need to change *.txt file manualy (vpp1.txt). I need a suggestion that can run this process but with out me need to change file name.
What I want to do is to open *.txt files in excel from a certain folder. The *.txt files are with tab-separation replace '.' with ',' move one of the columns in the now *.xls file (E:E) to a different open sheet Joined_282.xlsm save the opened *.xls file with same name as *.xls and close the file. Open next *.txt file in the folder do the same process but move the new copied column to the next column in the sheet A and so on, for all *.txt files in the folder.[/FONT]
[FONT="]I have searched for opening *.txt file and save it as *.xls but not really find any that fulfill what I want to do. Hope you can suggest a suggestion or guide me to right place for understanding how to do it.
[/FONT][FONT="]Thank you,
The recorded code looks like this:
[/FONT] Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+n
'
ChDir _
"C:\Users\282_o\F_s"
Workbooks.OpenText Filename:= _
"C:\Users\282_o\F_s\Vpp1.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Cells.Select
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 1
.Percent = False
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Windows("Joined_282.xlsm").Activate
Range("B1").Select
ActiveSheet.Paste
Range("B34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "PC2"
Windows("Vpp1.txt").Activate
Application.CutCopyMode = False
ChDir _
"C:\Users\282_o\F_s\xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\282_o\F_s\xls\Vpp1.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
I have copied this code several time in the same macro but i need to change *.txt file manualy (vpp1.txt). I need a suggestion that can run this process but with out me need to change file name.