setting first empty cell as range
 

Hi everyone,

I have a Macro that imports data from an access table. What i want to
do is add the data from the table into the first empty cell after the
data that is already on the worksheet.

i tried setting a marco to select the first empty cell when the
worksheet loads, but the import macro keeps going to a specific cell
anyway.

here is the marco i use:


Code:
--------------------
Sub importtable()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=H:\Monthly Stat Project\STATTEST.mdb;Mode=Share Deny Writ" _
, _
"e;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine" _
, _
" Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New" _
, _
" Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Com" _
, _
"pact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A3496"))
.CommandType = xlCmdTable
.CommandText = Array("CurrentMonth")
.Name = "STATTEST CurrentMonth_6"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = "H:\Data\My Data Sources\STATTEST CurrentMonth.odc"
.SourceDataFile = "H:\Monthly Stat Project\STATTEST.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub
--------------------



i need to get the "destination range" to be the first empty cell after
the exsiting data on the spreadsheet.

Any help would be appreciated!


--
captainwicked
------------------------------------------------------------------------
captainwicked's Profile: http://www.excelforum.com/member.php...o&userid=36369
View this thread: http://www.excelforum.com/showthread...hreadid=561534

setting first empty cell as range
 

You want code something like this... It creates a range object at the first
empty cell in column A of the active sheet.

dim rngDestination as Range

Set rngDestination = cells(rows.count, "A").end(xlUp).offset(1,0)

--
HTH...

Jim Thomlinson


"captainwicked" wrote:


Hi everyone,

I have a Macro that imports data from an access table. What i want to
do is add the data from the table into the first empty cell after the
data that is already on the worksheet.

i tried setting a marco to select the first empty cell when the
worksheet loads, but the import macro keeps going to a specific cell
anyway.

here is the marco i use:


Code:
--------------------
Sub importtable()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=H:\Monthly Stat Project\STATTEST.mdb;Mode=Share Deny Writ" _
, _
"e;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine" _
, _
" Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New" _
, _
" Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Com" _
, _
"pact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A3496"))
.CommandType = xlCmdTable
.CommandText = Array("CurrentMonth")
.Name = "STATTEST CurrentMonth_6"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = "H:\Data\My Data Sources\STATTEST CurrentMonth.odc"
.SourceDataFile = "H:\Monthly Stat Project\STATTEST.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub
--------------------



i need to get the "destination range" to be the first empty cell after
the exsiting data on the spreadsheet.

Any help would be appreciated!


--
captainwicked
------------------------------------------------------------------------
captainwicked's Profile: http://www.excelforum.com/member.php...o&userid=36369
View this thread: http://www.excelforum.com/showthread...hreadid=561534