Adding data to end of range

  • Thread starter Thread starter Gary West
  • Start date Start date
G

Gary West

I recorded the following macro that runs a query.

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\PathtoQuery\ClientTracking.dqy" _
, Destination:=Range("b3"))
.Name = "ClientTracking_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Is there some way I can make the Range portion of the macro user-
selectable? I need to be able to add rows of data to the sheet
one at a time.

I'm thinking of the way that Excel displays a listbox that lets
you return to the sheet and select a cell which is then used in the
listbox. The formulabuilding tool is one place where you see this box.

I haven't a clue what it's called in VBA nor how to use it.

I guess an alternative would be if there's a way to automatically
detect the size of the range of data (it's not named right now)
and automatically append the returned data to the end of the
range.

Thanks.

gary
 
I should have mentioned that the data from the query will always
start in column B and that field will never contain a null.

g-
 
Thanks, Tom. Typing in the code works to this point
(the VB editor pops up the possible selections)

Destination:=Cells(rows.count,"B").End(xlup).offset(1,0)
^^^^^^^^^^^^^^^^^^^^^^
The marked section causes a compiler error.

gary
 
Back
Top