Update VBA Code to Include Parameter for Delimiter, File Location and Overwrite Sheets when Re-Run

JFK_Lives

New Member
Joined
Aug 24, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Excel Version = 365
OS = Windows

Good Day,

I'm sure this question has been posted a thousand times and there are tons on this on Google, but I just can't find the right piece of code I need.
I don't know VBA at All so Apologies if I ask a lot of dumb obvious questions.

Short Description of what is needed:

I have Software that writes out Text Files to a Specific Folder on the Local HDD. These Text Files all have Unique Names and use " | " as a Delimiter.

Each Day when the User clicks the Button, These Text Files must be Imported into the Same Workbook the Code is Run from, Each Text File on it's Own Worksheet.
If the Button is Pressed Again, the Text Files are Imported Again and must Overwrite the Worksheet with the Same Name, not Create a New Worksheet, Thus Asset Text File will each time overwrite the Worksheet Named Asset etc.

I found this Code that does the Importing and Creates the Worksheets per File Name:

Sub Test()
'UpdatebyExtendoffice6/7/2016
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub

But I need the Following Add:

1. Option the Specify which Delimiter to Use.
2. Option to Specify the Folder Location so that the User doesn't have to Choose it each time, the code must automatically import the files from the Folder Location Supplied.
3. When the Code is Run Again, just Overwrite the Worksheet with the same name, not create a New Worksheet.

Any Help or Suggestions will be Greatly Appreciated.

Thanks
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Welcome to MrExcel forums.

I have Software that writes out Text Files to a Specific Folder on the Local HDD. These Text Files all have Unique Names and use " | " as a Delimiter.

Each Day when the User clicks the Button, These Text Files must be Imported into the Same Workbook the Code is Run from, Each Text File on it's Own Worksheet.
If the Button is Pressed Again, the Text Files are Imported Again and must Overwrite the Worksheet with the Same Name, not Create a New Worksheet, Thus Asset Text File will each time overwrite the Worksheet Named Asset etc.
Assign this macro to your button:
VBA Code:
Public Sub Import_Text_Files_To_Separate_Sheets()
    
    Dim delimiter As String, sourceFolder As String
    Dim fileName As String
    Dim currentSheet As Worksheet, destSheet As Worksheet
    
    'Specify the options
    
    delimiter = "|"
    sourceFolder = "C:\folder\path\"     'path to folder containing the .txt files to import
    
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    
    Set currentSheet = ActiveSheet
    fileName = Dir(sourceFolder & "*.txt")
    While fileName <> vbNullString
        Set destSheet = Nothing
        On Error Resume Next
        Set destSheet = ThisWorkbook.Worksheets(Left(fileName, InStrRev(fileName, ".") - 1))
        On Error GoTo 0
        If destSheet Is Nothing Then Set destSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        With destSheet
            .Name = Left(fileName, InStrRev(fileName, ".") - 1)
            .Cells.ClearContents
            With .QueryTables.Add(Connection:="TEXT;" & sourceFolder & fileName, Destination:=.Range("A1"))
                .TextFileParseType = xlDelimited
                .TextFileOtherDelimiter = delimiter
                .Refresh BackgroundQuery:=False
            End With
            .QueryTables(1).Delete
        End With
        fileName = Dir()
    Wend
    currentSheet.Activate
    
    MsgBox "Done"
    
End Sub
 
Upvote 0
Solution
Welcome to MrExcel forums.


Assign this macro to your button:
VBA Code:
Public Sub Import_Text_Files_To_Separate_Sheets()
   
    Dim delimiter As String, sourceFolder As String
    Dim fileName As String
    Dim currentSheet As Worksheet, destSheet As Worksheet
   
    'Specify the options
   
    delimiter = "|"
    sourceFolder = "C:\folder\path\"     'path to folder containing the .txt files to import
   
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
   
    Set currentSheet = ActiveSheet
    fileName = Dir(sourceFolder & "*.txt")
    While fileName <> vbNullString
        Set destSheet = Nothing
        On Error Resume Next
        Set destSheet = ThisWorkbook.Worksheets(Left(fileName, InStrRev(fileName, ".") - 1))
        On Error GoTo 0
        If destSheet Is Nothing Then Set destSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        With destSheet
            .Name = Left(fileName, InStrRev(fileName, ".") - 1)
            .Cells.ClearContents
            With .QueryTables.Add(Connection:="TEXT;" & sourceFolder & fileName, Destination:=.Range("A1"))
                .TextFileParseType = xlDelimited
                .TextFileOtherDelimiter = delimiter
                .Refresh BackgroundQuery:=False
            End With
            .QueryTables(1).Delete
        End With
        fileName = Dir()
    Wend
    currentSheet.Activate
   
    MsgBox "Done"
   
End Sub
Thank you so Much, had to Tweak here and there but got the Data in. I really Appreciate your time and effort!! I got more questions no regarding the Data and Formatting but will open a new thread for it.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top