hey guys, i have the following macro:


Sub sales()
'
' sales Macro
' Macro recorded 1/06/2004 by s4055130
'

'
Sheets("Q1").Select
Range("G6").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;Use
ID=Admin;Dat
Source=\\belserver\s4055130\system\Desktop\old.mdb ;Mode=Share De" _
, _
"ny Write;Extended Properties="""";Jet OLEDB:Syste
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas
Password="""";Jet OLEDB" _
, _
":Engine Type=5;Jet OLEDB:Database Locking Mode=0;Je
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul
Transactions=1;Jet OL" _
, _
"EDB:New Database Password="""";Jet OLEDB:Create Syste
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Cop
Locale" _
, _
" on Compact=False;Jet OLEDB:Compact Without Replic
Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("G6"))
.CommandType = xlCmdTable
.CommandText = Array("Q1 Sum of Sales")
.Name = "old_4"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile
"\\belserver\s4055130\system\Desktop\old.mdb"
.Refresh BackgroundQuery:=False
End With
Range("H6").Select

ActiveCell.FormulaR1C1 = "Units Sold"
Range("I6").Select

ActiveCell.FormulaR1C1 = "Sales Revenue"
Range("G9").Select

ActiveCell.FormulaR1C1 = "Total"
Range("H9").Select

ActiveCell.FormulaR1C1 = "='Q1'!R[-2]C+'Q1'!R[-1]C"
Range("H9").Select

Selection.AutoFill Destination:=Range("H9:I9")
Type:=xlFillDefault
Range("H9:I9").Select

Range("I7:I9").Select

Selection.Style = "Currency"
Columns("I:I").ColumnWidth = 14.71
Columns("I:I").ColumnWidth = 16.57
Range("E18").Select

ActiveCell.FormulaR1C1 = "='Q1'!R[-9]C[4]"
Range("E19").Select
End Sub



HOwever, it is meant overwirte the cells if they allready exist, as i
is it just inserts in front of the data already there...any ideas

--
Message posted from http://www.ExcelForum.com

Change
.RefreshStyle = xlInsertDeleteCells
to
.RefreshStyle = xlOverwriteCell

--
Message posted from http://www.ExcelForum.com

Try changing .RefreshStyle = xlInsertDeleteCells
to .RefreshStyle = xlOverwriteCells

zoola wrote in message ...
hey guys, i have the following macro:


Sub sales()
Sheets("Q1").Select
Range("G6").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data
Source=\\belserver\s4055130\system\Desktop\old.mdb ;Mode=Share De" _
, _
"ny Write;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 OL" _
, _
"EDB:New Database Password="""";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy
Locale" _
, _
" on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("G6"))
.CommandType = xlCmdTable
.CommandText = Array("Q1 Sum of Sales")
.Name = "old_4"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile =
"\\belserver\s4055130\system\Desktop\old.mdb"
.Refresh BackgroundQuery:=False
End With
Range("H6").Select

ActiveCell.FormulaR1C1 = "Units Sold"
Range("I6").Select

ActiveCell.FormulaR1C1 = "Sales Revenue"
Range("G9").Select

ActiveCell.FormulaR1C1 = "Total"
Range("H9").Select

ActiveCell.FormulaR1C1 = "='Q1'!R[-2]C+'Q1'!R[-1]C"
Range("H9").Select

Selection.AutoFill Destination:=Range("H9:I9"),
Type:=xlFillDefault
Range("H9:I9").Select

Range("I7:I9").Select

Selection.Style = "Currency"
Columns("I:I").ColumnWidth = 14.71
Columns("I:I").ColumnWidth = 16.57
Range("E18").Select

ActiveCell.FormulaR1C1 = "='Q1'!R[-9]C[4]"
Range("E19").Select
End Sub



HOwever, it is meant overwirte the cells if they allready exist, as it
is it just inserts in front of the data already there...any ideas?


---
Message posted from http://www.ExcelForum.com/