CODEWITHSUNDEEP
vbaexcelimport-from-csv
Salik Gilani
Salik Gilani

Reputation: 37

Import variable path CSV directly into table

I am trying to import data from CSV file "User Roles Entitlements" into my current sheet (current sheet tab name is also "User Roles Entitlements"), which is working perfectly fine if it being imported to cell A1. However, if I try to a table, the code does not work. I know it is a minor tweak, however, I am unable to figure it out.

Note: My file is in the same folder and I am using a variable path import VBA Code.

My code is as follows:

Dim path As String

path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(ThisWorkbook.path)

Sheets("User Roles Entitlements").Select
       Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & path & "\User Roles Entitlements.csv", Destination:=Range("A1"))
.Name = "positions_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 857
.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

End Sub

Would appreciate if I could get help with this.

Upvotes: 1

Views: 752

Answers (1)

Hambone
Hambone

Reputation: 16407

Are you okay if the data connection is lost after you import? In other words, will you need to dynamically refresh the table from the CSV file after you bring it in?

If the answer is no, then you can simply convert the range to a ListObject (table) after the fact.

For ease (so you don't have to figure out the range later), you can capture the range from the QueryTable object before you clobber it.

Sub CsvInsert()
  Dim sh As Worksheet
  Dim qt As QueryTable
  Dim r As Range

  Set sh = Sheets("User Roles Entitlements")
  Set qt = sh.QueryTables.Add(Connection:="TEXT;" & path & _
      "\User Roles Entitlements.csv", Destination:=Range("A1"))

  With qt
    .Name = "positions_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 857
    .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

Then convert the querytable to a range and create a table over the range:

  Set r = qt.ResultRange

  sh.QueryTables("positions_1").Delete
  sh.ListObjects.Add(xlSrcRange, r).Name = "positions_1"

End Sub

Upvotes: 1

Related Questions