You want to add a Button or another Control, like a TextBox or RadioButton to your Excel Worksheet to a specific cell.
Excel does not display the option to add a Button from the ControlBox and/or you cannot add it because the ControlBox’s Input Menu item is grayed out (disabled).
Solution
First, you have to add the menu band “Developer Tools” (in German “Entwicklertools”) to be visible. Just right click on the menu band on top of Excel, select Edit Menu band and check the “Developer Tools” option in the right list. See first screenshot.
Second, go to File | Options | Advanced | Scroll down to ~half of screen to the group “Displayed options for this workbook: (NAME OF YOU WORKBOOK)” and select “All” for “Displayed objects:” instead of “None (Hide objects)”. See second screenshot for more details.
Include following References: Microsoft ActiveX Data Objects 6.* Library
Be sure to return values as “datetime” instead of “date” or “datetime2” to get recognized as a date value in Excel.
Public Sub GetDwhData()
Dim wksTarget As Worksheet: Set wksTarget = Worksheets(1)
Dim cellAddressStart As String: cellAddressStart = "B2"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrHandling
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=tcp:localhost,1344;Initial Catalog=DWH;Integrated Security=SSPI"
objMyConn.Open
strSQL = "select TOP 1000 * from dbo.Datasource"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL
'Copy Data to Excel...
wksTarget.Range(cellAddressStart).CopyFromRecordset (objMyRecordset)
objMyRecordset.Close
objMyConn.Close
wksTarget.Calculate 'Formeln neu berechnen
ErrHandling:
Application.Calculation = xlCalculationSemiautomatic
Application.ScreenUpdating = True
End Sub
