Importing Data from CSV to XLSM

Question

Following is the macro code for my application to get the data from a CSV file to an Excel file. It is located in the same location. But I am unable to import the data.

It is giving an error:

Runtime Error 1004 Method 'Range of object' _Global' failed.

Excel VBA based application code:

Sub LoadFromFile()
Dim fileName As String
Dim folder As String
Dim lastRow As Long
Dim dest As String


If IsEmpty(Range("A1").Value) = True Then
    lastRow = 1
Else: If IsEmpty(Range("A1").Value) = False Then GoTo Line1x
Line1x:
With ActiveSheet
        lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    End With
        End If
ActiveSheet.UsedRange.Clear

dest = "$A$" & lastRow
folder = "C:\xampp\htdocs\sites\repairrequest\database.csv"

With ActiveSheet.QueryTables _
    .Add(Connection:="TEXT;" & folder, Destination:=Range("dest"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

End Sub

Line 22 or .Add(Connection:="TEXT;" & folder, Destination:=Range("dest")) — Rahilkhan Pathan
You just need Range(dest) as dest is a string variable not a named range. — SJR
New err: 438 Object doesn't support this property or method. Range is always in (" ") — Rahilkhan Pathan
Quotes around a variable is a no-go; you also need to save the lastRow long as part of the string to include a quotation around it as part of dest. You also could have done: .Add(Connection:="TEXT;" & folder, Destination:=Range("$A$" & lastRow)) — Cyril

Cyril Cyril · Accepted Answer · 2018-03-28T16:50:54

Answer was solved in the comments

.Add(Connection:="TEXT;" & folder, Destination:=Range("$A$" & lastRow))

Importing Data from CSV to XLSM

1 Answers