Execute an Excel function via macro (SQL.REQUEST)

beatdown

New Member
Joined
Mar 8, 2004
Messages
11
Hello!

I need to execute a SQL.REQUEST function in a macro, rather than from a cell. Here is what it looks like in the cell:

=SQL.REQUEST('ACCOUNT LOOKUP'!$D$1,'ACCOUNT LIST'!A1,2,'ACCOUNT LOOKUP'!$B$1:$B$18,TRUE)

using the sytax: (connection_string,output_ref,driver_prompt,query_text,col_names_logical)

so, everything is good to go, just need to know how to trigger it in a macro.

thanks.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

beatdown

New Member
Joined
Mar 8, 2004
Messages
11
Thanks, but I forgot to mention that I am not a VBA guy, so I will need a little more guidance than that :biggrin:

But I would actually like to use the Excel function, because I already got it working. Just want to know how to run this function from a macro rather than on the sheet (because it takes two seconds to re-calculate every time anything is entered in any cell).

thanks!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could put the formula in a cell like this:

Code:
Range("A1").Formula = "=SQL.REQUEST('ACCOUNT LOOKUP'!$D$1,'ACCOUNT LIST'!A1,2,'ACCOUNT LOOKUP'!$B$1:$B$18,TRUE)"

then delete it when the query is completed.

Or you could set calculation to Manual (Tools|Options|Calculation tab) and press F9 when you want to refresh the query.
 

beatdown

New Member
Joined
Mar 8, 2004
Messages
11

ADVERTISEMENT

The manual / F9 thing has been tried, but causes other sheets open at the same time to also be manual, and that can cause annoyances.

Thanks for the suggestion, and it is close! But, I would like it to be a "Push button" solution, because the query is dynamic based on cells, so I want to be able to trigger it multiple times, not at all, or whatever.

I know this formula can be used in a macro, becasue the help file says so, but just need to know how?

Thanks.
 

beatdown

New Member
Joined
Mar 8, 2004
Messages
11
Okay, I know I have to use

Application.ExecuteExcel4Macro

If anyone can help, that would be great.

Thanks
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Does the following work?

msgbox [SQL.REQUEST('ACCOUNT LOOKUP'!$D$1,'ACCOUNT LIST'!A1,2,'ACCOUNT LOOKUP'!$B$1:$B$18,TRUE)]
 

beatdown

New Member
Joined
Mar 8, 2004
Messages
11
I'll try that when I get back to work Thanks for the response...

In the meantime, I think I am interested in the SqlRequest VBA function afterall.

can someone translate this:


=SQL.REQUEST('ACCOUNT LOOKUP'!$D$1,'ACCOUNT LIST'!A1,2,'ACCOUNT LOOKUP'!$B$1:$B$18,TRUE)

into the macro that uses the SqlRequest VBA function, and returns the data to 'ACCOUNT LIST'!A1? I really do not know thing one about VBA code.

Basically, the query text is housed in cells 'ACCOUNT LOOKUP'!$B$1:$B$18,

The connection string is in cell 'ACCOUNT LOOKUP'!$D$1

Thanks for any help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top