Converting all .txt to excel in a given folder.

[wayne123]

Hello All,

Can you help me solve the following problem.

I want to change a piece of code in the below VBA which converts .txt to excel file, but I want the code to do all files in the folder. Instead of selecting individual files.

I've tried the obvious of change FilePicker to FolderPicker......

Code:

With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = True
.Show

'Store in fullpath variable
fullpath = .SelectedItems.Item(1)
End With

'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".txt") = 0 Then
Exit Sub
End If

 

[June7]

Loop through files in folder. https://exceloffthegrid.com/vba-cod...n,folder. The example we are looking... More

 

[arnelgp]

there are discussion here:

Batch convert text files to Excel .xlsx and convert text to columns using VBA

I have below program to batch convert text to excel (xlsx) Sub LoopAllFiles() Dim sPath As String, sDir As String sPath = "C:\Users\DNA\Desktop\Test Convert\" If Right(sPath, 1) &lt...

stackoverflow.com

 

[wayne123]

Thanks for your replies. All scripts are using file paths location. I need to use Folder Picker of some sort. This will all the user to select they own data location.

 

[Gasman]

Thanks for your replies. All scripts are using file paths location. I need to use Folder Picker of some sort. This will all the user to select they own data location.

So use the folder picker to select a folder then pass the path to the sub that arnelgp posted a link to?

 

[wayne123]

Appreciated that.

Maybe I'm missing something very simple.

I've made the following modifications to the code:

Dim sPath As String, sDir As String

sPath = Application.FileDialog(msoFileDialogFolderPicker)

With Application.FileDialog(msoFileDialogFolderPicker)
'Makes sure the user can select only one file
.AllowMultiSelect = True
.Show

'Store in fullpath variable
fullpath = .SelectedItems.Item(1)
End With

'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".txt") = 0 Then
Exit Sub
End If

'Open the file selected by the user
Workbooks.Open fullpath

ActiveSheet.Columns.AutoFit

 

[June7]

Please post code between CODE tags to retain indentation and readability.

If you want user to select only one folder, then AllowMultiSelect must be =False.

Dim sFolder As String
Dim fd As FileDialog
Dim strFile As String
Dim booResult As Boolean
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.AllowMultiSelect = False
fd.Title = "Select database folder"
fd.InitialFileName = "C:\Users"
While booResult = False
    If fd.Show = True Then
        'folder path was selected
        booResult = True
        sFolder = fd.SelectedItems(1)
        strFile = Dir(sFolder & "*.txt")
        Do While strFile <> ""
            'insert code here to convert file
            strFile = Dir
        Loop
    End If
Wend

 

[arnelgp]

Private Sub test()
    Dim sFolder As String
    Dim fd As FileDialog
    Dim strFile As String
    Dim booResult As Boolean
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.AllowMultiSelect = False
    fd.Title = "Select database folder"
    If fd.Show = True Then
        'folder path was selected
        booResult = True
        sFolder = fd.SelectedItems(1)
    End If
    If Len(sFolder) > 0 Then
        Call LoopAllFiles(sFolder)
    End If
End Sub


Sub LoopAllFiles(ByVal sPath As String)
    Dim sDir As String
    Dim objXl As Object
    Dim objWB As Object
    Dim objSH As Object
    Const xlDelimited As Integer = 1
    Const xlDoubleQuote As Integer = 1
    Const xlOpenXMLWorkbook As Integer = 51
    
    'sPath = "C:\work\"
    Set objXl = CreateObject("Excel.Application")
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sDir = Dir$(sPath & "*.txt", vbNormal)
    Do Until Len(sDir) = 0
        Set objWB = objXl.Workbooks.Open(sPath & sDir)
        Set objSH = objWB.sheets(1)
        With objSH
            objXl.Selection.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        End With
        objWB.SaveAs FileName:=Left(objWB.FullName, InStrRev(objWB.FullName, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        objWB.Close False
        Set objSH = Nothing
        Set objWB = Nothing
        sDir = Dir$
    Loop
    objXl.Quit
    Set objXl = Nothing
End Sub

 

[wayne123]

All works perfect > Thanks for your support.

Much Appreciated

 

[wayne123]

Sorry to be a nuisance, but can someone give me a piece of code that uses the file name has the chart title?

Much appreciated

 

[June7]

Does this help https://docs.microsoft.com/en-us/office/vba/api/excel.chart.charttitle