+ Reply to Thread
Results 1 to 15 of 15

Return SQL values from VBA to Excel file

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Return SQL values from VBA to Excel file

    I tried a follow a few other posts here to get me this far, but I am stuck right now and hoping someone can give me a hand. My goal here is to connect to a SQL server (accomplished), run a select statement against SQL that ultimately uses a parameter passed by the user through an inputbox (which is right now hard coded in my SQL) and then return the values of the select statement to my spreadsheet. I finally think I have 1 and 2 accomplished, but I am struggling on getting the data back to Excel. Based on the code below, A1 is turning out to be "prod_order" instead of the value returned from the SQL, same with B1 and C1.

    Below is the code I have so far.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Return SQL values from VBA to Excel file

    Little different but give this a try


    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    Looking great! Thank you.

    Would it be difficult to specify the exact cells of the output of the SQ to show in the Excel file then? Basically, I am going to add more fields to the select statement and scatter them around the worksheet to make a printable form. I plan on repeating certain fields (so lot may appear in A1, A10, A19, etc). Sorry I didn't bring this up earlier but I appreciate the help you've given.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Return SQL values from VBA to Excel file

    Your welcome... Something like this. Im guessing your query will only be returning 1 record..

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    It will be returning only 1 record. When I put this code in, I get a runtime error - "Object doesn't support this property or method". Any ideas?

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Return SQL values from VBA to Excel file

    Try this removing the [ ]'s and change BOF to EOF.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    We are striking out here Mike! I tried the suggestions and still get the same error. I will continue to play with it. Thanks for sticking with this.

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519
    Did it return any records the first time before you asked about placing the fields into different cells?

  9. #9
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    Yes it did. It placed the field names in row A and the 3 values below them in row B

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Return SQL values from VBA to Excel file

    Try this with and without the [ ]'s. I Added oRs.movefirst


    Please Login or Register  to view this content.

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Return SQL values from VBA to Excel file

    My bad Im blind today add oRs to the begining of

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    I added the oRs.MoveFirst and went with and without the [] and tried both Lot and Lot #...all combinations gave me the same message

  13. #13
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    Got it!!!! That last one worked - I just had to adjust it to "Lot", not "Lot #"

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519
    Good glad it worked out for you.

  15. #15
    Registered User
    Join Date
    07-24-2012
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return SQL values from VBA to Excel file

    Thanks very much for the help. I will mark this post as solved now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1