CODEWITHSUNDEEP
sqlexcelvba
Ryan Hickman
Ryan Hickman

Reputation: 23

Call an excel formula from VBA

Right now I have an excel spreadsheet with a button assigned to a macro I built that pulls data from SQL server. Right now it only pulls the whole table that I told it to.

The person I am making this for wants it to go further so that they can enter a SQL command in cell B1 and when the button is pressed it will run the statement that is contained in cell b1. So, instead of only being able to run the statement contained within the macro and pull that same table over and over from SQL server the button will be able to run whatever statement is contained within cell B1 making the macro easily changeable.

For example: right now the macro is pulling all information from the employees table (select * from employees) BUT I want to change that statement to something like (call cell B1) and then it will run whatever is within cell B1 which can be changed very easily without even having to go into VBA.

Upvotes: 0

Views: 154

Answers (1)

cronos2546
cronos2546

Reputation: 1106

I would place a call to a public function in the place of the SQL command that you have right now, which I will annote as 

Private Sub CommandButton1_Click()
'dim some objects and file paths, ect
With *some database*
    DoCmd.RunSQL "SELECT * FROM Employees"
End With
End Sub

To the following

 Private Sub CommandButton1_Click()
 'dim objects and file paths again
 With *some database*
     DoCmd.RunSQL(SQLSetup)
 End with
 End Sub

 Public Function SQLSetup() as String
 SQLSetup = Application.ThisWorkBook.Cells(X,Y).Value
 End Sub

Of course, there are a ton of ways to improve this. You could break out each element of the SQL statement into a new cell, and concantenate those cell values together in the function. But as far as accessing a value in a cell and passing it into a variable which you can into your SQL statement, this is a rough estimate of what you need.

Upvotes: 2

Related Questions