Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: how to execute SQL query in excel file

Discussion in 'Information Technology' started by Junior728, Oct 28, 2005.

  1. Junior728

    Junior728
    Expand Collapse
    Guest

    Hi Brendan,

    yah...indeed sound kind of weird..i thought qns are answered by
    programmers.=)by the way.thanks for your script. it sort of work a bit but
    when i try to run it, there is a error message that says no value is given
    for one or more parameters. i suspect it is my [currdate] field that causes
    this error. This field is not taken from the table, rather it is an input
    prompt to the user. the user will then enter the date and the query will run.


    How can i improve my script to incorporate that? See my e.g.

    Private Sub Worksheet_Activate()

    Dim connection As ADODB.connection
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim intRow As Integer

    strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
    "Persist Security Info=False"
    Set connection = New ADODB.connection
    connection.Open strSQL
    Set rst = New ADODB.Recordset
    With rst
    ..ActiveConnection = connection
    strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    "[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
    "ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
    "WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
    "ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE] DESC;"
    rst.Source = strSQL
    ..Open
    Do Until .EOF
    intRow = intRow + 1
    Sheet1.Cells(intRow, 1) = .Fields("Mfg")
    ..MoveNext
    Loop
    ..Close
    End With
    connection.Close

    End Sub

    Junior728

    "Brendan Reynolds" wrote:

    > I'm not sure that I understand the question. The fact that your query
    > includes an expression does not, as far as I can see, change anything. You
    > just need to replace the "SELECT * FROM Employees" in my example with your
    > SQL string. Of course you have to put the SQL string into a properly
    > formatted VBA string - enclose it within quotes, and either place it all on
    > one line, or use the line continuation characters, a space followed by an
    > underscore at the end of continued lines. For example ...
    >
    > Dim strSQL As String
    > Dim rst As ADODB.Recordset
    >
    > strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    > "[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
    > "FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
    > "[CURRDATE]-14 DESC, [CURRDATE] DESC;"
    > rst.Source = strSQL
    >
    > BTW: A couple of things about that query look a little odd. You have
    > "ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
    > typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
    > actually produce a different result than sorting by CURRDATE alone? I don't
    > see how it could, and sorting on CURDATE-14 is a lot less efficient than
    > sorting on CURRDATE alone, as the database engine will not be able to use
    > any index that might exist on the CURRDATE field. This can make a big
    > difference to performance, especially when working with large databases
    > across a network.
    >
    > Another BTW: It doesn't really bother me, I'm not offended or anything, but
    > 'awaiting your prompt reply' isn't really very appropriate in a peer-to-peer
    > setting. We're not Microsoft employees here, we're volunteers helping each
    > other out. It's a bit like asking your neighbour for a favour, then telling
    > them 'and be prompt about it!' :)
    >
    > --
    > Brendan Reynolds
    >
    > "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    > news:B8CE3FC3-CC4F-427C-ADD2-F75978409DDC@microsoft.com...
    > > hi Brendan,
    > >
    > > Thanks for your script! however, my query has some expression calculation
    > > done within the query and i dont know how it can be written in vba terms.
    > > e.g. i want field "part ad" which is the result of any date between the
    > > adddate and final date.
    > >
    > > this "part ad" is not a date field from the table but rather the result of
    > > comparison. And i want to execute other fields in the database table that
    > > falls in between this criteria.
    > >
    > > How can this be done?
    > >
    > > Example from SQL view:
    > >
    > > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
    > > [FINALDATE]
    > > And [CURRDATE] AS PARTAD
    > > 'FROM ADC_Parts
    > > 'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
    > > DESC;
    > >
    > > awaiting yourprompt reply,
    > > Junior 728
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > >> Here's an example that fills a column in an Excel worksheet with a list
    > >> of
    > >> names from the Employees table in the Northwind sample database. You'll
    > >> need
    > >> to add a reference to the Microsoft ActiveX Data Objects 2.x Library. (If
    > >> you're more familiar with DAO, the code is easily adapted to use DAO
    > >> instead.) To add a reference, open the Excel VBA editor, and select
    > >> References from the Tools menu.
    > >>
    > >> Private Sub Worksheet_Activate()
    > >>
    > >> Dim connectionString As String
    > >> Dim connection As ADODB.connection
    > >> Dim rst As ADODB.Recordset
    > >> Dim intRow As Integer
    > >>
    > >> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > >> "Data Source=C:\DSDATA\Northwind.mdb;" & _
    > >> "Persist Security Info=False"
    > >> Set connection = New ADODB.connection
    > >> connection.Open connectionString
    > >> Set rst = New ADODB.Recordset
    > >> With rst
    > >> .ActiveConnection = connection
    > >> .Source = "SELECT * FROM Employees"
    > >> .Open
    > >> Do Until .EOF
    > >> intRow = intRow + 1
    > >> Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
    > >> ..Fields("FirstName")
    > >> .MoveNext
    > >> Loop
    > >> .Close
    > >> End With
    > >> connection.Close
    > >>
    > >> End Sub
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>
    > >>
    > >> "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    > >> news:7009E52B-F6D3-4676-B30F-F21EF9A9769E@microsoft.com...
    > >> > Hi Sir,
    > >> >
    > >> > i have used microsoft access to come up with the Query and this is the
    > >> > SQL
    > >> > line:
    > >> >
    > >> > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
    > >> > DateAdd("d",-14,[CURRDATE]) AS FINALDATE
    > >> > FROM ADC_Parts;
    > >> >
    > >> > But i wish to combine this in my existing excel macro that i have? how
    > >> > can
    > >> > i
    > >> > do that? can SQL and VBA language cross each other in a module?
    > >> >
    > >> > Awaiting ur prompt reply,
    > >> > 10/24/05
    > >>
    > >>
    > >>

    >
    >
    >
     
  2. Loading...

    Similar Threads Forum Date
    S Asia Saudi Arabia executes woman convicted of 'sorcery' Breaking News Dec 12, 2011
    S Asia Taliban Execute Pregnant Woman Breaking News Aug 10, 2010
    Controversial Heads of Sikhs Executed in Pakistan Delivered to Gurdwara (Filmlore Blog) Hard Talk Mar 4, 2010
    Sikh News Singapore hangman hopes to execute Australian (Reuters via Yahoo! News) Breaking News Nov 28, 2005
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    That automatic, built-in prompt for parameters works only within Access. To
    use the query in Excel, your Excel application will need to get the value
    for the parameter from the user before executing the query. You could do
    that by having the user enter the value in a cell in the Excel worksheet, by
    using an Excel user-form, or by using the InputBox function. Once you have
    the value, you can either build up the query in code as before or you can
    use a pre-saved parameter query. Here's an example I posted recently in
    response to a similar question ...

    Below is the SQL for a saved parameter query in Access ...

    PARAMETERS [Last Name?] Text ( 50 );
    SELECT Employees.*
    FROM Employees
    WHERE (((Employees.LastName)=[Last Name?]));

    .... and here is some VBA code to pass a variable as the parameter value, and
    retrieve the result. This code would work identically from Access, Excel,
    Word, or any other VBA-enabled application ...

    Public Function GetFullName(LastName As String) As String

    Dim strConnection As String
    Dim objConnection As ADODB.connection
    Dim objCommand As ADODB.Command
    Dim objParam As ADODB.Parameter
    Dim rst As ADODB.Recordset

    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\USENET\Test.mdb;" & _
    "Persist Security Info=False"
    Set objConnection = New ADODB.Connection
    objConnection.Open strConnection
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandText = "qryTest"
    objCommand.CommandType = adCmdStoredProc
    Set objParam = objCommand.CreateParameter("[Last Name?]", _
    adVarChar, adParamInput, 50, LastName)
    objCommand.Parameters.Append objParam
    Set rst = objCommand.Execute
    GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
    objConnection.Close

    End Function

    Result in the Excel VBA Immediate window ...
    ? getfullname("Davolio")
    Davolio, Nancy

    --
    Brendan Reynolds

    "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    news:CCF7D846-EBCF-4B35-9024-388C906E8FEC@microsoft.com...
    > Hi Brendan,
    >
    > yah...indeed sound kind of weird..i thought qns are answered by
    > programmers.=)by the way.thanks for your script. it sort of work a bit but
    > when i try to run it, there is a error message that says no value is given
    > for one or more parameters. i suspect it is my [currdate] field that
    > causes
    > this error. This field is not taken from the table, rather it is an input
    > prompt to the user. the user will then enter the date and the query will
    > run.
    >
    >
    > How can i improve my script to incorporate that? See my e.g.
    >
    > Private Sub Worksheet_Activate()
    >
    > Dim connection As ADODB.connection
    > Dim strSQL As String
    > Dim rst As ADODB.Recordset
    > Dim intRow As Integer
    >
    > strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
    > "Persist Security Info=False"
    > Set connection = New ADODB.connection
    > connection.Open strSQL
    > Set rst = New ADODB.Recordset
    > With rst
    > .ActiveConnection = connection
    > strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    > "[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
    > "ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
    > "WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
    > "ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE]
    > DESC;"
    > rst.Source = strSQL
    > .Open
    > Do Until .EOF
    > intRow = intRow + 1
    > Sheet1.Cells(intRow, 1) = .Fields("Mfg")
    > .MoveNext
    > Loop
    > .Close
    > End With
    > connection.Close
    >
    > End Sub
    >
    > Junior728
    >
    > "Brendan Reynolds" wrote:
    >
    >> I'm not sure that I understand the question. The fact that your query
    >> includes an expression does not, as far as I can see, change anything.
    >> You
    >> just need to replace the "SELECT * FROM Employees" in my example with
    >> your
    >> SQL string. Of course you have to put the SQL string into a properly
    >> formatted VBA string - enclose it within quotes, and either place it all
    >> on
    >> one line, or use the line continuation characters, a space followed by an
    >> underscore at the end of continued lines. For example ...
    >>
    >> Dim strSQL As String
    >> Dim rst As ADODB.Recordset
    >>
    >> strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    >> "[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
    >> "FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
    >> "[CURRDATE]-14 DESC, [CURRDATE] DESC;"
    >> rst.Source = strSQL
    >>
    >> BTW: A couple of things about that query look a little odd. You have
    >> "ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
    >> typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
    >> actually produce a different result than sorting by CURRDATE alone? I
    >> don't
    >> see how it could, and sorting on CURDATE-14 is a lot less efficient than
    >> sorting on CURRDATE alone, as the database engine will not be able to use
    >> any index that might exist on the CURRDATE field. This can make a big
    >> difference to performance, especially when working with large databases
    >> across a network.
    >>
    >> Another BTW: It doesn't really bother me, I'm not offended or anything,
    >> but
    >> 'awaiting your prompt reply' isn't really very appropriate in a
    >> peer-to-peer
    >> setting. We're not Microsoft employees here, we're volunteers helping
    >> each
    >> other out. It's a bit like asking your neighbour for a favour, then
    >> telling
    >> them 'and be prompt about it!' :)
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    >> news:B8CE3FC3-CC4F-427C-ADD2-F75978409DDC@microsoft.com...
    >> > hi Brendan,
    >> >
    >> > Thanks for your script! however, my query has some expression
    >> > calculation
    >> > done within the query and i dont know how it can be written in vba
    >> > terms.
    >> > e.g. i want field "part ad" which is the result of any date between the
    >> > adddate and final date.
    >> >
    >> > this "part ad" is not a date field from the table but rather the result
    >> > of
    >> > comparison. And i want to execute other fields in the database table
    >> > that
    >> > falls in between this criteria.
    >> >
    >> > How can this be done?
    >> >
    >> > Example from SQL view:
    >> >
    >> > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
    >> > [FINALDATE]
    >> > And [CURRDATE] AS PARTAD
    >> > 'FROM ADC_Parts
    >> > 'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
    >> > DESC;
    >> >
    >> > awaiting yourprompt reply,
    >> > Junior 728
    >> >
    >> > "Brendan Reynolds" wrote:
    >> >
    >> >> Here's an example that fills a column in an Excel worksheet with a
    >> >> list
    >> >> of
    >> >> names from the Employees table in the Northwind sample database.
    >> >> You'll
    >> >> need
    >> >> to add a reference to the Microsoft ActiveX Data Objects 2.x Library.
    >> >> (If
    >> >> you're more familiar with DAO, the code is easily adapted to use DAO
    >> >> instead.) To add a reference, open the Excel VBA editor, and select
    >> >> References from the Tools menu.
    >> >>
    >> >> Private Sub Worksheet_Activate()
    >> >>
    >> >> Dim connectionString As String
    >> >> Dim connection As ADODB.connection
    >> >> Dim rst As ADODB.Recordset
    >> >> Dim intRow As Integer
    >> >>
    >> >> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    >> >> "Data Source=C:\DSDATA\Northwind.mdb;" & _
    >> >> "Persist Security Info=False"
    >> >> Set connection = New ADODB.connection
    >> >> connection.Open connectionString
    >> >> Set rst = New ADODB.Recordset
    >> >> With rst
    >> >> .ActiveConnection = connection
    >> >> .Source = "SELECT * FROM Employees"
    >> >> .Open
    >> >> Do Until .EOF
    >> >> intRow = intRow + 1
    >> >> Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
    >> >> ..Fields("FirstName")
    >> >> .MoveNext
    >> >> Loop
    >> >> .Close
    >> >> End With
    >> >> connection.Close
    >> >>
    >> >> End Sub
    >> >>
    >> >> --
    >> >> Brendan Reynolds
    >> >>
    >> >>
    >> >> "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    >> >> news:7009E52B-F6D3-4676-B30F-F21EF9A9769E@microsoft.com...
    >> >> > Hi Sir,
    >> >> >
    >> >> > i have used microsoft access to come up with the Query and this is
    >> >> > the
    >> >> > SQL
    >> >> > line:
    >> >> >
    >> >> > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
    >> >> > DateAdd("d",-14,[CURRDATE]) AS FINALDATE
    >> >> > FROM ADC_Parts;
    >> >> >
    >> >> > But i wish to combine this in my existing excel macro that i have?
    >> >> > how
    >> >> > can
    >> >> > i
    >> >> > do that? can SQL and VBA language cross each other in a module?
    >> >> >
    >> >> > Awaiting ur prompt reply,
    >> >> > 10/24/05
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  4. Junior728

    Junior728
    Expand Collapse
    Guest

    Hi Brendan,

    Thanks. But can i know how do i add this parameter function into my script?
    Or do i enter under another module?

    Junior728

    "Brendan Reynolds" wrote:

    > That automatic, built-in prompt for parameters works only within Access. To
    > use the query in Excel, your Excel application will need to get the value
    > for the parameter from the user before executing the query. You could do
    > that by having the user enter the value in a cell in the Excel worksheet, by
    > using an Excel user-form, or by using the InputBox function. Once you have
    > the value, you can either build up the query in code as before or you can
    > use a pre-saved parameter query. Here's an example I posted recently in
    > response to a similar question ...
    >
    > Below is the SQL for a saved parameter query in Access ...
    >
    > PARAMETERS [Last Name?] Text ( 50 );
    > SELECT Employees.*
    > FROM Employees
    > WHERE (((Employees.LastName)=[Last Name?]));
    >
    > .... and here is some VBA code to pass a variable as the parameter value, and
    > retrieve the result. This code would work identically from Access, Excel,
    > Word, or any other VBA-enabled application ...
    >
    > Public Function GetFullName(LastName As String) As String
    >
    > Dim strConnection As String
    > Dim objConnection As ADODB.connection
    > Dim objCommand As ADODB.Command
    > Dim objParam As ADODB.Parameter
    > Dim rst As ADODB.Recordset
    >
    > strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\USENET\Test.mdb;" & _
    > "Persist Security Info=False"
    > Set objConnection = New ADODB.Connection
    > objConnection.Open strConnection
    > Set objCommand = New ADODB.Command
    > Set objCommand.ActiveConnection = objConnection
    > objCommand.CommandText = "qryTest"
    > objCommand.CommandType = adCmdStoredProc
    > Set objParam = objCommand.CreateParameter("[Last Name?]", _
    > adVarChar, adParamInput, 50, LastName)
    > objCommand.Parameters.Append objParam
    > Set rst = objCommand.Execute
    > GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
    > objConnection.Close
    >
    > End Function
    >
    > Result in the Excel VBA Immediate window ...
    > ? getfullname("Davolio")
    > Davolio, Nancy
    >
    > --
    > Brendan Reynolds
    >
    > "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    > news:CCF7D846-EBCF-4B35-9024-388C906E8FEC@microsoft.com...
    > > Hi Brendan,
    > >
    > > yah...indeed sound kind of weird..i thought qns are answered by
    > > programmers.=)by the way.thanks for your script. it sort of work a bit but
    > > when i try to run it, there is a error message that says no value is given
    > > for one or more parameters. i suspect it is my [currdate] field that
    > > causes
    > > this error. This field is not taken from the table, rather it is an input
    > > prompt to the user. the user will then enter the date and the query will
    > > run.
    > >
    > >
    > > How can i improve my script to incorporate that? See my e.g.
    > >
    > > Private Sub Worksheet_Activate()
    > >
    > > Dim connection As ADODB.connection
    > > Dim strSQL As String
    > > Dim rst As ADODB.Recordset
    > > Dim intRow As Integer
    > >
    > > strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > > "Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
    > > "Persist Security Info=False"
    > > Set connection = New ADODB.connection
    > > connection.Open strSQL
    > > Set rst = New ADODB.Recordset
    > > With rst
    > > .ActiveConnection = connection
    > > strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    > > "[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
    > > "ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
    > > "WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
    > > "ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE]
    > > DESC;"
    > > rst.Source = strSQL
    > > .Open
    > > Do Until .EOF
    > > intRow = intRow + 1
    > > Sheet1.Cells(intRow, 1) = .Fields("Mfg")
    > > .MoveNext
    > > Loop
    > > .Close
    > > End With
    > > connection.Close
    > >
    > > End Sub
    > >
    > > Junior728
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > >> I'm not sure that I understand the question. The fact that your query
    > >> includes an expression does not, as far as I can see, change anything.
    > >> You
    > >> just need to replace the "SELECT * FROM Employees" in my example with
    > >> your
    > >> SQL string. Of course you have to put the SQL string into a properly
    > >> formatted VBA string - enclose it within quotes, and either place it all
    > >> on
    > >> one line, or use the line continuation characters, a space followed by an
    > >> underscore at the end of continued lines. For example ...
    > >>
    > >> Dim strSQL As String
    > >> Dim rst As ADODB.Recordset
    > >>
    > >> strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
    > >> "[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
    > >> "FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
    > >> "[CURRDATE]-14 DESC, [CURRDATE] DESC;"
    > >> rst.Source = strSQL
    > >>
    > >> BTW: A couple of things about that query look a little odd. You have
    > >> "ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
    > >> typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
    > >> actually produce a different result than sorting by CURRDATE alone? I
    > >> don't
    > >> see how it could, and sorting on CURDATE-14 is a lot less efficient than
    > >> sorting on CURRDATE alone, as the database engine will not be able to use
    > >> any index that might exist on the CURRDATE field. This can make a big
    > >> difference to performance, especially when working with large databases
    > >> across a network.
    > >>
    > >> Another BTW: It doesn't really bother me, I'm not offended or anything,
    > >> but
    > >> 'awaiting your prompt reply' isn't really very appropriate in a
    > >> peer-to-peer
    > >> setting. We're not Microsoft employees here, we're volunteers helping
    > >> each
    > >> other out. It's a bit like asking your neighbour for a favour, then
    > >> telling
    > >> them 'and be prompt about it!' :)
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>
    > >> "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    > >> news:B8CE3FC3-CC4F-427C-ADD2-F75978409DDC@microsoft.com...
    > >> > hi Brendan,
    > >> >
    > >> > Thanks for your script! however, my query has some expression
    > >> > calculation
    > >> > done within the query and i dont know how it can be written in vba
    > >> > terms.
    > >> > e.g. i want field "part ad" which is the result of any date between the
    > >> > adddate and final date.
    > >> >
    > >> > this "part ad" is not a date field from the table but rather the result
    > >> > of
    > >> > comparison. And i want to execute other fields in the database table
    > >> > that
    > >> > falls in between this criteria.
    > >> >
    > >> > How can this be done?
    > >> >
    > >> > Example from SQL view:
    > >> >
    > >> > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
    > >> > [FINALDATE]
    > >> > And [CURRDATE] AS PARTAD
    > >> > 'FROM ADC_Parts
    > >> > 'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
    > >> > DESC;
    > >> >
    > >> > awaiting yourprompt reply,
    > >> > Junior 728
    > >> >
    > >> > "Brendan Reynolds" wrote:
    > >> >
    > >> >> Here's an example that fills a column in an Excel worksheet with a
    > >> >> list
    > >> >> of
    > >> >> names from the Employees table in the Northwind sample database.
    > >> >> You'll
    > >> >> need
    > >> >> to add a reference to the Microsoft ActiveX Data Objects 2.x Library.
    > >> >> (If
    > >> >> you're more familiar with DAO, the code is easily adapted to use DAO
    > >> >> instead.) To add a reference, open the Excel VBA editor, and select
    > >> >> References from the Tools menu.
    > >> >>
    > >> >> Private Sub Worksheet_Activate()
    > >> >>
    > >> >> Dim connectionString As String
    > >> >> Dim connection As ADODB.connection
    > >> >> Dim rst As ADODB.Recordset
    > >> >> Dim intRow As Integer
    > >> >>
    > >> >> connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > >> >> "Data Source=C:\DSDATA\Northwind.mdb;" & _
    > >> >> "Persist Security Info=False"
    > >> >> Set connection = New ADODB.connection
    > >> >> connection.Open connectionString
    > >> >> Set rst = New ADODB.Recordset
    > >> >> With rst
    > >> >> .ActiveConnection = connection
    > >> >> .Source = "SELECT * FROM Employees"
    > >> >> .Open
    > >> >> Do Until .EOF
    > >> >> intRow = intRow + 1
    > >> >> Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
    > >> >> ..Fields("FirstName")
    > >> >> .MoveNext
    > >> >> Loop
    > >> >> .Close
    > >> >> End With
    > >> >> connection.Close
    > >> >>
    > >> >> End Sub
    > >> >>
    > >> >> --
    > >> >> Brendan Reynolds
    > >> >>
    > >> >>
    > >> >> "Junior728" <Junior728@discussions.microsoft.com> wrote in message
    > >> >> news:7009E52B-F6D3-4676-B30F-F21EF9A9769E@microsoft.com...
    > >> >> > Hi Sir,
    > >> >> >
    > >> >> > i have used microsoft access to come up with the Query and this is
    > >> >> > the
    > >> >> > SQL
    > >> >> > line:
    > >> >> >
    > >> >> > SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
    > >> >> > DateAdd("d",-14,[CURRDATE]) AS FINALDATE
    > >> >> > FROM ADC_Parts;
    > >> >> >
    > >> >> > But i wish to combine this in my existing excel macro that i have?
    > >> >> > how
    > >> >> > can
    > >> >> > i
    > >> >> > do that? can SQL and VBA language cross each other in a module?
    > >> >> >
    > >> >> > Awaiting ur prompt reply,
    > >> >> > 10/24/05
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page