Using ALL to return a table

juliengirard · Jul 17, 2019

[FONT=&quot]Hello,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have a Fact table like this:[/FONT]

[TABLE="class: MsoNormalTable, width: 326"]
<tbody>[TR]
[TD="width: 114"] [FONT=&quot]Product[/FONT]
[/TD]
[TD="width: 114"] [FONT=&quot]Customer[/FONT]
[/TD]
[TD="width: 114"] [FONT=&quot]Region[/FONT]

[/TD]
[TD="width: 64"] [FONT=&quot]Date[/FONT]
[/TD]
[TD="width: 29"] [FONT=&quot]Sales[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
In the middle of a calculation, I would like to put that table in a variable with the following filter:
[/FONT]

[TABLE="class: MsoNormalTable, width: 492"]
<tbody>[TR]
[TD="width: 135"] [FONT=&quot]Based on context[/FONT]
[/TD]
[TD="width: 140"] [FONT=&quot]Based on context[/FONT]
[/TD]
[TD="width: 145"] [FONT=&quot]Based on context[/FONT]
[/TD]
[TD="width: 84"] [FONT=&quot]Any date[/FONT]
[/TD]
[TD="width: 152"] [FONT=&quot]Based on context[/FONT]
[/TD]
[/TR]
[TR]
[TD="width: 135"] [FONT=&quot]Product[/FONT]
[/TD]
[TD="width: 140"] [FONT=&quot]Customer[/FONT]
[/TD]
[TD="width: 145"] [FONT=&quot]Region[/FONT]
[/TD]
[TD="width: 84"] [FONT=&quot]Date[/FONT]
[/TD]
[TD="width: 152"] [FONT=&quot]Sales[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]

ALL seems to be the right function but I only manage to use it in a calculation with formula like this:[/FONT]
[FONT=&quot]CALCULATE(
SUM(Fact[Sales] ,
All(Fact[Date]))

[/FONT]

[FONT=&quot]How could I use it to return the table above?[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]Thanks for your help![/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]

pbornemeier · Jul 30, 2019

You can use SQL to return a portion of your table. You will have to modify the SQL string to meet your requirements. If there are any spaces in your header cells or worksheet name, replace their spaces with underscores in the SQL string. The name of the worksheet that holds the table (in the FROM part) must be followed by a dollar sign.

Code:

Option Explicit

Sub CreateKEAsOf(Optional dteData As Date)

    Dim sSQLString As String
    Dim aryReturn As Variant
    Dim sDBPath As String
    Dim sConnect As String
    
    'Dim Conn As New ADODB.Connection
    'Dim rs As New ADODB.Recordset
    'Above 2 lines replaced by next 4 to allow late binding
    Dim conn As Object  ' As ADODB.Connection
    Dim rs As Object  ' As ADODB.Recordset
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    'Your SQL Statement (Table Name=Sheet Name, coded as: [Sheet1$])
    'Don't forget ending space for all-but-last row in multi-line statements
              
     sSQLString = _
        "SELECT * " & _
        "FROM [WorksheetName$] "
              
    sDBPath = ThisWorkbook.FullName
    'You can provide the full path of your external file as shown below
    'sDBPath ="C:\InputData.xlsx"
    sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & sDBPath & ";HDR=Yes';"
    'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
        'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    conn.Open sConnect
    rs.Open sSQLString, conn
    '=>Load the Data into an array
    aryReturn = rs.GetRows

End Sub

Using ALL to return a table

juliengirard

New Member

Excel Facts

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

Using ALL to return a table

juliengirard

New Member

Excel Facts

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock