Help Cleaning up Code
 

I recorded this macro so that i could use the code. I was wondering if
there is a more efficient way of writing this code?

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=S:\IE-Methods\ShortRange2006\5-3-06
OrderSim.XLS;Mode=Sha" _
, _
"re Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password" _
, _
"="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transac" _
, _
"tions=1;Jet OLEDB: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("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Standards")
.Name = "5-3-06 OrderSim"
.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 = "S:\IE-Methods\ShortRange2006\5-3-06 OrderSim.XLS"
.Refresh BackgroundQuery:=False
End With
End Sub

sorry about the word wrap in the first few lines

Thanks

Tim

Help Cleaning up Code
 

What is inefficient about it. It is essential one command with some options
set.

Most are probably defaults, so you could probably remove them, but I would
just leave it alone.

Of course, once the querytable is established, you don't need any of that
code except

Activesheet.QueryTables(1) .Refresh BackgroundQuery:=False


perhaps if you want to refresh it.
--
Regards,
Tom Ogilvy


"Lost and Looking for Help" wrote:

I recorded this macro so that i could use the code. I was wondering if
there is a more efficient way of writing this code?

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=S:\IE-Methods\ShortRange2006\5-3-06
OrderSim.XLS;Mode=Sha" _
, _
"re Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password" _
, _
"="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transac" _
, _
"tions=1;Jet OLEDB: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("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Standards")
.Name = "5-3-06 OrderSim"
.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 = "S:\IE-Methods\ShortRange2006\5-3-06 OrderSim.XLS"
.Refresh BackgroundQuery:=False
End With
End Sub

sorry about the word wrap in the first few lines

Thanks

Tim