Vba Import CSV files to Excel

Question

I'm trying to import Csv file to excel using VBA code, I want it to work with all Csv files but it didn't. Here is my code:

Sub Input_CSV()

Dim Wb As String
Dim Arr

    Set Wb = GetObject(Application.GetOpenFilename("csv file,*.csv", , "please choose a csv file", , False))

    Dim blnImportData As Boolean
    blnImportData = ImportCSV(Wb, "Sheet1", "A1")
    If blnImportData Then MsgBox "Import CSV process complete", vbInformation, ThisWorkbook.Name _
    Else MsgBox "Import CSV process failed", vbCritical, ThisWorkbook.Name

End Sub

And here is the function code

Function ImportCSV(ByVal Filename As String, _
                   ByVal Worksheet As String, _
                   ByVal StartCell As String) As Boolean
 On Error GoTo Catch

    Dim strConnectionName As String
    strConnectionName = "TEXT;" + Filename

    With Worksheets(Worksheet).QueryTables.Add(Connection:=strConnectionName, _
                                               Destination:=Worksheets(Worksheet).Range(StartCell))
        .Name = Filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True 'False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True 'False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With

        ImportCSV = True
        Exit Function

Catch:
        ImportCSV = False

End Function

How could I correct it? Could someone help me plz!

It is difficult to offer solutions when the problem statement is simply, "it doesn't work". Please edit your question to give a more complete description of what you expected to happen and how that differs from the actual results. See How to Ask for hints on what makes a good explanation. — Toby Speight
– Toby Speight, Commented Jun 13, 2016 at 8:55
I couldn't edit so I'm trying to answer here.I have a bug in this line: "blnImportData = ImportCSV(Wb, "Sheet1", "A1")", error 438: object doesn't support this property or method — shinpencil
– shinpencil, Commented Jun 13, 2016 at 9:23
You only need to use Set where you define an object reference. Since Wb is a string, you don't use Set — Dave
– Dave, Commented Jun 13, 2016 at 9:54

Mathias711 · Accepted Answer · 2024-02-12 14:59:04Z

You have a good explanation here:

https://sitestory.dk/excel_vba/csv-file-import.htm

with the code I applied in my case:

 Sub OpenCSV()

 Dim sPath As String
 sPath = ThisWorkbook.Path & "\Site_survey_form2.csv"
 Workbooks.OpenText Filename:= _
 sPath, DataType:=xlDelimited, Semicolon:=True, Local:=True
 End Sub

As a result, the .csv file will be opened in the separate workbook.

If you want to import the .csv content straight away to your workbook, then I would advise referring to the thread here:

Is there a way to import data from .csv to active excel sheet?

from where I prepared the following code:

 Sub CSV_Import()
 Dim ws As Worksheet, strFile As String, sPath As String

 Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name

 sPath = ThisWorkbook.Path & "\Site_survey_form2.csv"  '"\ your file name

  With ws.QueryTables.Add(Connection:="TEXT;" & sPath, 
      Destination:=ws.Range("A1"))  
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With
End Sub

Dave · Accepted Answer · 2016-06-13 08:58:34Z

1

You could simplify and use the following to open your file:

Public Sub OpenCsvFile(byVal filepath)
    Workbooks.OpenText filename:=filepath, dataType:=xlDelimited, semicolon:=True
End Sub

More details on this method here (MSDN link)

edited Jun 13, 2016 at 8:58

answered Jun 13, 2016 at 8:50

Dave

4,3562 gold badges26 silver badges33 bronze badges

2 Comments

shinpencil Over a year ago

Because I want the code to work with multiple Csv files in different names so your code wouldn't work for me..

Dave Over a year ago

So wrap it in a Sub or function? See the edited answer for an example

Collectives™ on Stack Overflow

Vba Import CSV files to Excel

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related