Converting all .txt to excel in a given folder. (1 Viewer)

wayne123

New member
Local time
Today, 20:31
Joined
Aug 4, 2020
Messages
7
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
 

wayne123

New member
Local time
Today, 20:31
Joined
Aug 4, 2020
Messages
7
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

Enthusiastic Amateur
Local time
Today, 20:31
Joined
Sep 21, 2011
Messages
14,047
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

New member
Local time
Today, 20:31
Joined
Aug 4, 2020
Messages
7
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

AWF VIP
Local time
Today, 12:31
Joined
Mar 9, 2014
Messages
5,423
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.
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:31
Joined
May 7, 2009
Messages
19,169
Code:
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

New member
Local time
Today, 20:31
Joined
Aug 4, 2020
Messages
7
All works perfect > Thanks for your support.

Much Appreciated :)
 

wayne123

New member
Local time
Today, 20:31
Joined
Aug 4, 2020
Messages
7
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
 

Users who are viewing this thread

Top Bottom