Without knowing the error message I am struggling a little, but I would think
that your CommandType should be set to xlCmdSQL and NOT xlCmdTable

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Hemant_india" wrote:

hi
below is macro i have recorded
when i put .commandtext=qrystring i get error
can somebody help me?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/11/2008 by kjsb
'
Dim qrystring
qrystring = "select * from kjsb.d010009 where lbrcode=5"
'
With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=MSDAORA.1;User ID=test;Data Source=kjsb",
Destination:=Range( _
"A1"))
.CommandType = xlCmdTable
.CommandText = Array("""KJSB"".""D010009""")
'.CommandText = qrystring
.Name = "kjsb (Default) D010009"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\kjsb1\My Documents\My Data Sources\kjsb
(Default) D010009.odc"
.Refresh BackgroundQuery:=False
End With
End Sub

--
hemu