0

Apologies for any vagueness up front. I am pretty new to VBA but have a pretty decent MS SQL and Oracle SQL background. The issue I have is that I have a user form that has 10 fields. Typically the user only knows one field - a purchase order number. With that purchase order number, I could pull all of the needed attributes, which are the other 9 fields in SQL Server very easily. What I would like to do is have the excel sheet do this for me. Could anybody point me in the right direction? For example - I want to populate one column by the user and each column after would be populated by a query. thank you

JohnyL
  • 6,894
  • 3
  • 22
  • 41
user3496218
  • 185
  • 3
  • 5
  • 19
  • 3
    Why use *VBA*? Excel already has data query capabilities. In fact, PowerQuery is a pretty powerfull ETL system. You can create a PowerQuery query that accepts parameters that can come from cells (no form required) or you can execute it from VBA – Panagiotis Kanavos Aug 10 '18 at 07:30
  • A nice trick is to record a VBA macro while doing what you want to automate, eg executing a query and use the generated script as a starting point. – Panagiotis Kanavos Aug 10 '18 at 07:33
  • 1
    Possible duplicate of [Using Excel VBA to run SQL query](https://stackoverflow.com/questions/27385245/using-excel-vba-to-run-sql-query) – FunThomas Aug 10 '18 at 07:35
  • @PanagiotisKanavos I guess, ***not*** every user knows PowerQuery. – JohnyL Aug 10 '18 at 07:50
  • @JohnyL *Excel business users* do. It's how they load and process data, how they generate their marketing, financial, refund reports every day. Most developers don't know Excel though, unless they've worked in BI. – Panagiotis Kanavos Aug 10 '18 at 07:52
  • @PanagiotisKanavos I'm glad that ***your*** users are so clever, but don't put this sticker on each and everyone. – JohnyL Aug 10 '18 at 07:57
  • I have seen this a lot that the initial design will require more details and business logics etc so I think he can simply start using ADO to connect to the SQL DB and then run whatever query he wants. I you are sure you won't go further, then go with power query, otherwise you need to learn ADO, there are ton of ADO VBA examples on the internet and on SO – Ibo Aug 10 '18 at 16:44

0 Answers0