+ Reply to Thread
Results 1 to 16 of 16

Using cell reference as a value in a Excel VBA SQL Query

  1. 02-05-2013, 02:39 PM #1
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Using cell reference as a value in a Excel VBA SQL Query

    How can I replace on the following query the statement WHERE PARTID = 5? I want to replace the 5 using a cell value lets say that on Sheet A1 I have the value 7. The below query is working fine, I just need to replace the number 5, in the SELECT statement.

    Please Login or Register  to view this content.
    Last edited by trizzo; 02-05-2013 at 03:03 PM.
    Reply With QuoteRegister To Reply
  2. 02-05-2013, 02:47 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,313

    Re: Using cell reference as a value in a Excel VBA SQL Query 

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-05-2013, 02:51 PM #3
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Thanks TMShucks. I mean lets say the cell value is on Sheet2 at A1. I tried to run the above but not working. I get arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. I think that is because I am using Sheet1..... so I would like to use Sheet2 at A1 as cell value.
    Reply With QuoteRegister To Reply
  4. 02-05-2013, 03:09 PM #4
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    When I receive the error message it gets highlghted here: 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-05-2013, 03:12 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,313

    Re: Using cell reference as a value in a Excel VBA SQL Query 

    Please Login or Register  to view this content.

    Regards, TMS
    Reply With QuoteRegister To Reply
  6. 02-05-2013, 03:17 PM #6
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    I tried to use:

    Please Login or Register  to view this content.
    At Sheet2 cell A1 I put 70.

    I got the following error:

    "Run-time error '3001'
    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

    After that I click on Debug and it is highlighted the following:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 02-05-2013, 03:22 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,313

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Try stepping through the code using F8 and see what the sSQL string looks like before you try to execute it.
    Reply With QuoteRegister To Reply
  8. 02-05-2013, 03:25 PM #8
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    I printed with MsgBox the string sSQL before executing it is empty, and I also printed after executing it, and is showing the correct value in PARTID which is 70. Everything looks fine but still getting the mentioned error...
    Reply With QuoteRegister To Reply
  9. 02-05-2013, 03:26 PM #9
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Looks like the issue is on this parameter, not sure, because it highlights this when the error is issued:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 02-05-2013, 03:33 PM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,313

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Try putting 5 in the cell. That way, it should generate exactly the same string as you originally had. If that works, it's more to do with the data than the string.


    Regards, TMS
    Reply With QuoteRegister To Reply
  11. 02-05-2013, 03:34 PM #11
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    With 5 is not working as well, same issue.
    Reply With QuoteRegister To Reply
  12. 02-05-2013, 04:35 PM #12
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Any other verification I should perform?
    Reply With QuoteRegister To Reply
  13. 02-05-2013, 04:50 PM #13
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,313

    Re: Using cell reference as a value in a Excel VBA SQL Query

    I can't think of anything. I've asked for support but the troops haven't turned up yet.

    TMS
    Reply With QuoteRegister To Reply
  14. 02-05-2013, 05:41 PM #14
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Can you use select * from instead of select partid from?
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  15. 02-05-2013, 05:43 PM #15
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Also, looking at it a bit closer, give this a try:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 02-11-2013, 01:10 PM #16
    trizzo
    trizzo is offline
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Brazil
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    268

    Re: Using cell reference as a value in a Excel VBA SQL Query

    Thanks everyone for the tips and effort! Really appreciate it! It is working now.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1