How can I automatically import into next empty row

[Saucer Man]

I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

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

 

[Gord Dibben]

Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\UPS_CSV_EXPORT.csv",Destination:=rng1)


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Dim DestCell as range

with activesheet
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
with .QueryTables.Add(Connection:="TEXT;c:\UPS_CSV_EXPORT.csv", _
Destination:=destcell)
.name = .....
...
end with
end with

 

[JE McGimpsey]

One way:

Dim rDest As Range
Set rDest = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

then in your QueryTables.Add call, use

Destination:=rDest

 

[Saucer Man]

Thanks for the replies. It works!