File Chooser Needed

[rajkumar]

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

[lucas]

untested but maybe incorporate this into your code. Get rid of the direct path to resolve fName and replace with this file browse code.
Sub a()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
MsgBox .SelectedItems(1)
End If
End With
'from your code set fName
'fName = .SelectedItems(1)
End Sub

[rajkumar]

Hi Lucas,

thanks for reply, and i tried your suggestion and it is working, but i do not know how to get rid of the direct path to resolve fName and replace with this file browse code.( i am not a programmer )

If is use
Workbooks.OpenText Filename:=.selecteditems(1)

your code works but if i try to set fName to the selected item i get an error "Object Required"

here is what i have done

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
Set fName = .SelectedItems(1)
Workbooks.OpenText Filename:=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

End If
End With


I need to keep this fName since i use it to merge files in the later portion of my macro. The code is too big to post here hence i have attached a sample workbook.

Pls Help

Raj :think:

[lucas]

This seems to work but I don't have the data files to test it.

I had to comment out this procudure call as it doesn't appear to be in the workbook you provided:

Call PGMTR

see attached workbook

ps. You really should add Option Explicit to the top of each code module. You would find many variables not defined and probably other errors that you would want to know about.

[rajkumar]

Hi lucas,

thanks a lot, it is working fine now. I was using

Set fName = .selecteditems(1)


which was giving me "object required" error

i replaced with

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then

Workbooks.OpenText Filename:=.selecteditems(1), 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

End If
End With
fName = activeworkbook.name



and it did the job

once again thank you for your suggestion for declaring variables, since i know not much VBA, mostly helped by my colleague.

If you can correct the code in my attachment it will be very useful for me

The call PGMTR is a progress meter routine for model less user form (thanks to Mr.Chip Pearson and his site) and that line can be deleted

i am attaching the text file here to test. i am using the path where these text files are kept is C:\Data_Analysis\Log\

and the reference table.xls is in C:\Data_Analysis\



Raj :friends:

[GTO]

Greetings Raj,

You will need to remove the VBProject's protection, as we cannot see the code.

Mark

[rajkumar]

Greetings GTO,

i have attached my addin (protection removed) and reference table.xls (protection removed)

to install the addin it must be at c:\data_analysis\ folder and the mvbe,bat file to be executed.

Raj