CODEWITHSUNDEEP
vbaexcelcsv
Achak
Achak

Reputation: 1296

Issue with loading multiple comma delaminated text in excel using VBA

Good morning,

I am trying to load multiple comma delaminated casv style file with .plt extension in excel with.

What I am trying to do is to load openfolder dialogue and select the folder where my codes are saved wit the first bit of code and paste the path in TextBox1. I have managed to successfully do that. Then I am trying to run further codes with the run button to load all files in a new workbook with filenames as sheet name. But I am struggling with the following 2 things:

  1. When I try to open the file in new workbook, each file opening in new workbook but I want them to just open 1 new workbook with each files in different worksheets.

  2. The program works fine when I manually assign directory path but when I ask the program to read the folder path where the files are saved from the textbox its failing

Could someone please give me some advise on how to rectify this, many thanks. My codes are as follows:

I have added comments on possibly where I think I am doing something wrong as by replacing the commented sections manually with the file path sich as "C:\Users\Desktop\test\" the program works fine to load in same workbook all files. 

   'Code for the button on the right of textbox 1
    Private Sub FilePath_Button_Click()
    get_folder
    End Sub

    ' code for the run button
    Private Sub Run_Button_Click()
    load_file
    End Sub

    Private Sub TextBox1_Change()

    End Sub

    Private Sub UserForm_Click()

    End Sub

'code for the fild open dialouge box to locate folder where the files are saved 

    Public Sub get_folder()
      Dim FolderName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      FolderName = .SelectedItems(1)
      Err.Clear
      On Error GoTo 0
    End With
    TextBox1.Text = FolderName
    End Sub


    'codes for the run button to import the files
    Sub load_file()
    Dim strFile As String
    Dim ws As Worksheet
    strFile = Dir("TextBox1.Text*.plt") ' I think this is the bit where I doing something wrong
    Do While strFile <> vbNullString
    Set ws = Sheets.Add
    With ws.QueryTables.Add(Connection:= _
        "TEXT;" & "TextBox1.Text" & strFile, Destination:=Range("$A$1")) ' and also "TextBox1.Text" I think not right as if i replace this two section that I commented with the file path manually the program works fine
        .Name = strFile
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    strFile = Dir
    Loop
    End Sub

Upvotes: 0

Views: 383

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

Is there any reason you are using a QueryTable? Just opening the workbook and copying the sheet should suffice as the CSV file will already have the sheetname as the filename.

Try replacing the load_file() procedure with this:

Sub load_file()

Dim wb1 As Workbook, wb2 As Workbook
Dim filePath As String, strFile As String

Application.ScreenUpdating = False

Set wb1 = ActiveWorkbook

filePath = TextBox1.Text

strFile = Dir(filePath)
    While Not strFile = ""
        If LCase(Right(strFile, 4)) = ".plt" Then


Set wb2 = Workbooks.OpenText(Filepath:=fileName & "\" & strFile, Datatype:=xlDelimited, Comma:=True) '// open the workbook
        wb2.Sheets(1).Copy after:=wb1.Sheets(wb1.Sheets.Count) '// copy the page to wb1
        wb2.Close False '// close wb2
        Set wb2 = Nothing '// release from memory
    End If
    strFile = Dir()
Wend

Set wb1 = Nothing

Application.ScreenUpdating = True

MsgBox "Done"

End Sub

Upvotes: 0

barryleajo
barryleajo

Reputation: 1952

Try replacing:

strFile = Dir("TextBox1.Text*.csv") ' I think this is the bit where I doing something wrong
    Do While strFile <> vbNullString
    Set ws = Sheets.Add

with

StrFile = Dir(Me.TextBox1.Text & "\*.csv")

Do While Len(StrFile) > 0
    Set ws = ActiveWorkbook.Sheets.Add
    ws.Name = StrFile

EDIT

To add the .csv files to new workbooks

Dim wb as workbook

    Do While Len(StrFile) > 0
        Set wb = Workbooks.Add
        'added workbook becomes the activeworkbook
        Set ws = ActiveWorkbook.Sheets.Add
        ws.Name = StrFile

Upvotes: 1

Related Questions