rajkumar
11-10-2009, 07:09 PM
Hello Experts,
I need help in choosing the text files to import and save as xls.
I use the below macro to import and save all the text files from the specified folder and save them as xls in that folder.
Public Const strpath As String = "C:\Data_Analysis\"
Sub PRODUCTIVITY()
fName = Dir(strpath & "LOG\LOG-*.TXT")
Dim t As Date
'set a variable equal to the starting time
t = Now()
If Not FileFolderExists(strpath & "LOG\LOG-*.TXT") Then
MsgBox "Text file for this report is not present. Hence Ending Report"
Exit Sub
Else
If fName <> "" Then
Do
Application.ScreenUpdating = False
Workbooks.OpenText FileName:=strpath & "LOG\" & fName, Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, OtherChar:="\", FieldInfo:= _
Array(Array(0, 1), Array(6, 1), Array(14, 1), Array(23, 1), Array(32, 1), Array(41, 1), _
Array(49, 1), Array(62, 1), Array(74, 1), Array(89, 1), Array(110, 1), Array(120, 1), Array _
(121, 1), Array(127, 1), Array(137, 1), Array(138, 1), Array(146, 1), Array(155, 1), Array( _
162, 1), Array(169, 1), Array(174, 1), Array(179, 1), Array(185, 1), Array(212, 1), Array( _
234, 1), Array(241, 1), Array(263, 1), Array(277, 1), Array(290, 1), Array(302, 1), Array( _
318, 1), Array(330, 1), Array(337, 1), Array(355, 1), Array(370, 1), Array(384, 1), Array( _
394, 1), Array(404, 1), Array(414, 1), Array(423, 1), Array(432, 1), Array(449, 1), Array( _
458, 1), Array(470, 1)), TrailingMinusNumbers:=True
fName = Dir()
Loop Until fName = ""
End If
End If
Application.DisplayAlerts = False
sSavename = strpath & "LOG\" & Left(fName, Len(fName) - 4) & ".xls"
ActiveWorkbook.SaveAs FileName:=sSavename, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
Dim oldName As String
Dim newName As String
oldName = strpath & "LOG\" & fName
newName = strpath & "LOG\" & "Run @ " & Format(Now, "mm-dd-yy hh-mm-ss") & ".txt"
Name oldName As newName
Windows(Replace(UCase(fName), ".TXT", ".xls")).Close
Application.ScreenUpdating = True
MsgBox ("LOG Report is Completed in ") & Format(Now() - t, "hh:mm:ss") & (" (HH:MM:SS)")
End
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function
can anyone help me with a macro to choose the required text file from the folder and convert them to xls. I always save the text files like "LOG-CHE.TXT","LOG-BLR.TXT".
can a user form list box or combox is populated with the names of the text files present in the folder? and once user chooses the particular file and clicks Proceed then the rest of the macro should run.
Pls Help :bow::help
I need help in choosing the text files to import and save as xls.
I use the below macro to import and save all the text files from the specified folder and save them as xls in that folder.
Public Const strpath As String = "C:\Data_Analysis\"
Sub PRODUCTIVITY()
fName = Dir(strpath & "LOG\LOG-*.TXT")
Dim t As Date
'set a variable equal to the starting time
t = Now()
If Not FileFolderExists(strpath & "LOG\LOG-*.TXT") Then
MsgBox "Text file for this report is not present. Hence Ending Report"
Exit Sub
Else
If fName <> "" Then
Do
Application.ScreenUpdating = False
Workbooks.OpenText FileName:=strpath & "LOG\" & fName, Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, OtherChar:="\", FieldInfo:= _
Array(Array(0, 1), Array(6, 1), Array(14, 1), Array(23, 1), Array(32, 1), Array(41, 1), _
Array(49, 1), Array(62, 1), Array(74, 1), Array(89, 1), Array(110, 1), Array(120, 1), Array _
(121, 1), Array(127, 1), Array(137, 1), Array(138, 1), Array(146, 1), Array(155, 1), Array( _
162, 1), Array(169, 1), Array(174, 1), Array(179, 1), Array(185, 1), Array(212, 1), Array( _
234, 1), Array(241, 1), Array(263, 1), Array(277, 1), Array(290, 1), Array(302, 1), Array( _
318, 1), Array(330, 1), Array(337, 1), Array(355, 1), Array(370, 1), Array(384, 1), Array( _
394, 1), Array(404, 1), Array(414, 1), Array(423, 1), Array(432, 1), Array(449, 1), Array( _
458, 1), Array(470, 1)), TrailingMinusNumbers:=True
fName = Dir()
Loop Until fName = ""
End If
End If
Application.DisplayAlerts = False
sSavename = strpath & "LOG\" & Left(fName, Len(fName) - 4) & ".xls"
ActiveWorkbook.SaveAs FileName:=sSavename, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
Dim oldName As String
Dim newName As String
oldName = strpath & "LOG\" & fName
newName = strpath & "LOG\" & "Run @ " & Format(Now, "mm-dd-yy hh-mm-ss") & ".txt"
Name oldName As newName
Windows(Replace(UCase(fName), ".TXT", ".xls")).Close
Application.ScreenUpdating = True
MsgBox ("LOG Report is Completed in ") & Format(Now() - t, "hh:mm:ss") & (" (HH:MM:SS)")
End
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function
can anyone help me with a macro to choose the required text file from the folder and convert them to xls. I always save the text files like "LOG-CHE.TXT","LOG-BLR.TXT".
can a user form list box or combox is populated with the names of the text files present in the folder? and once user chooses the particular file and clicks Proceed then the rest of the macro should run.
Pls Help :bow::help