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

[paulgu]

Hi,
Your problem is that with a query of command type Table (xlCmdTable), you need to supply the table name only in the command text, so qrystring = "kjsb.d010009" would work fine. If you want to use SQL then you need to change the command type to xlCmdSql.
HTH,
Paul

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

eroor is table not found
however if run the code as it is it works
--
hemu


"Alan Moseley" wrote:

> 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

Your command text is 'SELECT * FROM .....etc', and your CommandType is
xlCmdTable. You do not have a table called 'SELECT * FROM ....etc'. Change
your commandtype to xlCmdSQL.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

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


"Hemant_india" wrote:

> eroor is table not found
> however if run the code as it is it works
> --
> hemu
>
>
> "Alan Moseley" wrote:
>
> > 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

ok
but if i keep commandtype as xlCmdTable and use syntax as
..commandtext=array(my sqlstring) ... then it should run ... i guess
i think i am missing something while writing the query
here i am accessing the table from oracle
--
hemu


"Alan Moseley" wrote:

> Your command text is 'SELECT * FROM .....etc', and your CommandType is
> xlCmdTable. You do not have a table called 'SELECT * FROM ....etc'. Change
> your commandtype to xlCmdSQL.
> --
> Alan Moseley IT Consultancy
> http://www.amitc.co.uk
>
> If I have solved your problem, please click Yes below. Thanks.
>
>
> "Hemant_india" wrote:
>
> > eroor is table not found
> > however if run the code as it is it works
> > --
> > hemu
> >
> >
> > "Alan Moseley" wrote:
> >
> > > 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