Excel Connections & SQL

[JDeiley]

Good Morning,

I have an unusual problem that I'm hoping you can help me with. I'm trying to modify an existing excel connection to an sql database that the spreadsheet queries.

There is a table in the spreadsheet that I can right click on and look at the sql query that is attached to it. It is as follows:

select <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
c.New_FileNumber as [FileNumber]<o></o>
,p.new_paymentdate as [Date]<o></o>
, p.New_PaymentAmount as [Amount]<o></o>
, p.new_paytypename as [PayType]<o></o>
, p.new_typedetail as [TypeDetail]<o></o>
, ISNULL(p.New_name, '') as [Memo]<o></o>
from FilteredNew_payment p<o></o>
join FilteredContact c on c.ContactId = p.new_clientid<o></o>
where p.New_TypeDetail not in(27)<o></o>
and c.New_FileNumber = 'G105407'

What I need to do is pass a new file number to the query in the line: c.New_FileNumber = 'G105407' via a macro. That way I can change the customer data that the query finds with an input box where the user can type in a customer id number.

I admittedly am over my head in this. I know nothing about sql or excel connections to external databases and would appreciate any help you can give me on this. To me it seems to be a simple problem of passing a number to the query. However, I'm well aware that simple problems can become very complex at the drop of a hat.

Thank you for your time and any help you can give me in this regard.

JDeiley

 

[ChrisM]

This isn't unusual at all, I just posted instructions the other day for the same thing:

-----------------------------
Go to the Data section of the ribbon, and choose "From Other Sources / From MS Query". Choose the Excel file option, then navigate to your file. You should see your named range, MyData, etc. Select it, then follow the query wizard prompts. Go ahead and set a filter on employee name, yes it will only return one record but that's ok, just pick one name. Now, once you pass the filter screen it takes you to sort options, at this point hit cancel and it will prompt you to open in MS Query. Choose yes.

Now you are in an Access-like query editor. You will see your name criteria field. Replace the name you hardcoded with a bracketed dummy variable, like [myName]. Using brackets treats this field as a parameter. The query will ask you for this value every time it runs.

Hit the big X in top right corner (not the smaller x) and the query will run and ask you for a name. Manually type in one name you know will work. Now review your query output in your worksheet. Look ok?

Last step, tell Excel to use a worksheet cell for the criteria. To do this, you need to edit the connection. Go to Data / Connections, then choose the connection file you just created. Hit properties. Then the Definition tab, then click the parameters button, pretty straightforward from there.
-------------------------------------

This was all pretty straightforward in EX 2003, but with 2007+ they made connections more complicated. See if that works for you, if not post back.

By the way, you won't need a macro this way. You can pull the file name via a spreadsheet cell, or you can select a different option and the query will prompt the user with a pop-up when they refresh it.

 

[JDeiley]

Thank you for your help, Chris. I was able to get the query to work and can now move on to bigger and better things!

It is greatly appreciated.

JDeiley

 

[sarlo00]

Thank you Chris! That was so helpful to me!