Using Excel 2007-
So I have a code written to execute a data table (pulling from an external database, which is an add in to excel). The below code works fine and pulls the data but here's the adjustment I need to make. In the 2nd "break", that starts with result=, this is where it chooses where to pull the table and how to pull it by. Currently it is pulling Database "Reporting", It is selecting Year "2011", By Row "Company" and By Column "Year". And it is showing Invoice Extended Price, which are sales. What I need it to do is where the "Company" is, I need it to reference a cell in place of that. So ultimately it would reference cell A1 for example, and whatever is typed in that cell (whether it's Company, or State, or Product), that will get pulled into the chart. Right now it works but it is only showing the table by Company, whereas if I could have it reference a cell, it would make it much easier for someone to select how they want to see the report.
Sub ExecuteScript()
Dim addin As Office.COMAddIn
Dim automationObject As Object
Set addin = Application.COMAddIns("BIReporting")
Set automationObject = addin.Object
Dim result() As Variant
result = automationObject.ExecuteScript("Database ""Reporting""" & vbCrLf & "Select" & vbCrLf & """Year"" ""2011""" & vbCrLf & "End Select" & vbCrLf & "Table ShowHeaders ShowHorizontalGrid ShowVerticalGrid Indent 10" & vbCrLf & "ByRow ""Company"" HideEmptyRows" & vbCrLf & "ByColumn ""Year""" & vbCrLf & "Show ""Invoice Extended Price"" & vbCrLf & "End Table")
Dim size
size = UBound(result, 1)
Dim size2
size2 = UBound(result, 2)
With ActiveSheet
Range(("A3"), .Cells(size + 3, size2 + 1)).Value2 = result
End With
End Sub
Any help is appreciated. Thanks
So I have a code written to execute a data table (pulling from an external database, which is an add in to excel). The below code works fine and pulls the data but here's the adjustment I need to make. In the 2nd "break", that starts with result=, this is where it chooses where to pull the table and how to pull it by. Currently it is pulling Database "Reporting", It is selecting Year "2011", By Row "Company" and By Column "Year". And it is showing Invoice Extended Price, which are sales. What I need it to do is where the "Company" is, I need it to reference a cell in place of that. So ultimately it would reference cell A1 for example, and whatever is typed in that cell (whether it's Company, or State, or Product), that will get pulled into the chart. Right now it works but it is only showing the table by Company, whereas if I could have it reference a cell, it would make it much easier for someone to select how they want to see the report.
Sub ExecuteScript()
Dim addin As Office.COMAddIn
Dim automationObject As Object
Set addin = Application.COMAddIns("BIReporting")
Set automationObject = addin.Object
Dim result() As Variant
result = automationObject.ExecuteScript("Database ""Reporting""" & vbCrLf & "Select" & vbCrLf & """Year"" ""2011""" & vbCrLf & "End Select" & vbCrLf & "Table ShowHeaders ShowHorizontalGrid ShowVerticalGrid Indent 10" & vbCrLf & "ByRow ""Company"" HideEmptyRows" & vbCrLf & "ByColumn ""Year""" & vbCrLf & "Show ""Invoice Extended Price"" & vbCrLf & "End Table")
Dim size
size = UBound(result, 1)
Dim size2
size2 = UBound(result, 2)
With ActiveSheet
Range(("A3"), .Cells(size + 3, size2 + 1)).Value2 = result
End With
End Sub
Any help is appreciated. Thanks
