How to Print result from select query onto worksheet

[Tardisgx]

Below is macro that just searches by the field _name and is looking for TEST1 is it possible to return all the fields in the table relating to that entry on the excel sheet. I know this is kind of a redundant thing so ask about since there are simpler ways to return sql data.

`Sub Searcher_Macro()

' 'Connect to Database Dim MyDatabase As SQLDatabase Set MyDatabase = Create_SQLDatabase() With MyDatabase .DatabaseName = "databasename" .UserName = "username" .Password = "password" .ServerName = "servername" .DBType = "mssql" End With ' ' Dim MySelect As SQLSelect Set MySelect = Create_SQLSelect With MySelect .Fields = Array("b_Name") .Table = "new_import"

'Need to escape the string
.AddWhere "_Name", str("TEST1")

End With

ID = MyDatabase.Execute(MySelect, "_Name") End Sub`

[Beakerboy]

In VBA, a “Sub” is a subroutine and can only execute commands. You want a “Function”. Change Sub Searcher_macro() to Function Searcher_macro() and End Sub to End Function. In order to specify what needs to be returned, assign the value to the name of the function. So the last line should be Searcher_macro = ID.

Then you can go into your spreadsheet and type =Searcher_macro() and this will execute, with the result ending up in the cell just like any other function.

[Tardisgx]

Cool I did not know I could do that. Thanks :)

[Beakerboy]

If you assign an array as the return “object”, you can press ctrl+shift+enter to return all the values.