VBA to Import CSV file by opening dialog box and let user choose file

[zardi]

Hi all,

I built a macro to
1) Pop a dialog box for user to choose a txt file (csv with tab as delimiter)
2) Import the data accordingly to a new sheet.

But something is wrong and I am not sure what, I reckon it may be the bit .Name.
Any suggestions?

Sub import()

  Dim vFileName
  vFileName = Application.GetOpenFilename("Text Files, *.txt", , "Please select CSV file")
  If vFileName = "False" Then Exit Sub
 
  With ActiveSheet.QueryTables.Add(Connection:="TEXT;" + vFileName, Destination:=Range("$A$1"))
  .Name = vFileName
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 950
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 2)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  End With
End Sub

 

[Jigar B]

Edit and try following code:

Set wb_Report = ActiveWorkbook
Set wb_CSV = Workbooks.Open(ThisWorkbook.Path & "\" & "mycsvfile.CSV")
   
Last_Row_CSV = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    wb_CSV.Sheets(1).Range("A1:AE" & Last_Row_CSV).Copy
    wb_Report.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
   
    wb_CSV.Close
    Set wb_CSV = Nothing

 

[zardi]

Hi Jigar,

your code is used to replace a certain part of my code?

 

[Jigar B]

You need to edit the following line in code with your file path:

csvPath = "C:\folder1\mycsvfile.csv"

Here is the complete code:

Sub import()
    Dim wb_CSV As Workbook
    Dim wb_Report As Workbook
    Set wb_Report = ActiveWorkbook
    Dim csvPath As String

    csvPath = "C:\folder1\mycsvfile.csv"
    Set wb_CSV = Workbooks.Open(csvPath)

    Last_Row_CSV = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    wb_CSV.Sheets(1).Range("A1:AE" & Last_Row_CSV).Copy
    wb_Report.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    wb_CSV.Close
    Set wb_CSV = Nothing
End Sub

you can also use vfilename variable you used in your code.

 

[zardi]

but i dont have a specific file path or file name, that's why have to use the Application.GetOpenFilename function

i need the dialog box to choose the file to load

 

[Chihiro]

Your code worked fine for me.

Can you upload a sample text file where you are having issue?

 

[zardi]

Sure

After selecting the txt file, I keep getting

Run-time error 5
Invalid procedure call or argument.


Ideally, is it possible to get it to import the data to a new worksheet with today's date?