Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If cell not empty, then display from range | Excel Discussion (Misc queries) | |||
setting a cell to 'empty' or blank or null ? | New Users to Excel | |||
Finding next empty empty cell in a range of columns | Excel Programming | |||
Range - setting a value in a cell | Excel Programming | |||
Setting a range with the last used cell? | Excel Programming |