CODEWITHSUNDEEP
vbaexceldelimitercsv
Clauric
Clauric

Reputation: 1886

Import text files with commas into Excel

I've got a large number of files that I need to import into Excel. I found, and partly modified this code (I can't remember where, so I can't credit), that works 90&%+ of the time.

Sub Extractions()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False

Dim FilesToOpen
Dim x As Integer

    On Error GoTo ErrHandler

    FilesToOpen = Application.GetOpenFilename _
      (fileFilter:="Microsoft Excel Files (*.*?), *.*?", MultiSelect:=True, Title:="Files to Import")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    While x <= UBound(FilesToOpen)
        Workbooks.Open fileName:=FilesToOpen(x)
        Sheets().Move before:=ThisWorkbook.Sheets _
          (ThisWorkbook.Sheets.Count)
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = False
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    Resume

End Sub

Most of the files I import are fine, as they are either .csv, .txt. or .xlsx files. However, a number of the .csv and .txt files contain commas in different rows. When I go to import them, they split the rows into two, which causes issues with later parts of the macro.

When I go to import the files normally, the issue of the commas does not occur and the file imports to the first column, which is where it needs to be.

Is there any way to modify the above code to simply import the files to the first column, and ignore the commas?

Upvotes: 1

Views: 1109

Answers (1)

Clauric
Clauric

Reputation: 1886

So the answer, with the help of @R3uK is to put

, Format:= 5

after

Workbooks.Open fileName:=FilesToOpen(x)

Upvotes: 1

Related Questions