Execute an Excel function via macro (SQL.REQUEST)

[beatdown]

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.

 

[Andrew Poulsom]

If you establish a reference to XLODBC.XLA you can use the VBA SQLRequest function.

 

[beatdown]

Thanks, but I forgot to mention that I am not a VBA guy, so I will need a little more guidance than that

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]

You could put the formula in a cell like this:

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]

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]

Okay, I know I have to use

Application.ExecuteExcel4Macro

If anyone can help, that would be great.

Thanks

 

[NateO]

Does the following work?

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

 

[beatdown]

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.