I want to create power pivot tables using the distinct count function - which is only available when you "Add this data to the Data Model". It seems I need to create a connection to the worksheet first and I am stuck on the syntax. I will need to create many pivot tables for many workbook/worksheets - therefore I would like to call the sub using parameters. Below is what I have tried. I keep getting subscript out of range errors. I think it is from the commandtext assignment. I want to use the pdata sheet in workbook C:~swap\providers.xlsx. The data range in sheet pdata is A1:BG1817.

Sub tst()

' this worksheet is already open
Call createConn("C:\~swap\providers.xlsx", "pdata")

End Sub

Sub createConn(wb As String, wks As String)

'wb is full workbook name
'wks is sheet name

'trying to dynamically find the active range on the selected worksheet
'lastrow = Workbooks(wb).Sheets(wks).Cells(Rows.Count, 1).End(xlUp).Row
'lastcol = Workbooks(wb).Sheets(wks).Cells(1, Columns.Count).End(xlToLeft).Column

'lastrow = Workbooks("C:\~swap\providers.xlsx").Sheets("pdata").Cells(Rows.Count, 1).End(xlUp).Row
'lastcol = Workbooks("C:\~swap\providers.xlsx").Sheets("pdata").Cells(1, Columns.Count).End(xlToLeft).Column

'this one works but I want to reference the workbook name also because there may 
'be more than workbook open with the same sheet name - I tried in the above 
'examples
lastrow = Sheets("pdata").Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Sheets("pdata").Cells(1, Columns.Count).End(xlToLeft).Column

Debug.Print "lastrow="; lastrow; "lastcol="; lastcol

Workbooks(wb).Connections.Add2 _
    Name:="ProvData", _
    Description:="", _
    ConnectionString:="WORKSHEET;" & wb, _
    CommandText:=??????
    lcmdtype:=XlCmdType.xlCmdExcel, _
    createmodelconnection:=True, _
    importrelationships:=False

'these are some of the commandtext variations I have tried
'CommandText:=pdata!A1: BG , _
'CommandText:=wks & "!" & wks.Range("a1:bg1817"), _
'CommandText:=wsPdata & "!" & wsPdata.Range("a1").CurrentRegion, _
'CommandText:=wsPdata & "!" & wsPdata.Range("a1:bg1819"), _
    
End Sub

I want to create power pivot tables using the distinct count function - which is only available when you "Add this data to the Data Model". It seems I need to create a connection to the worksheet first and I am stuck on the syntax. I will need to create many pivot tables for many workbook/worksheets - therefore I would like to call the sub using parameters. Below is what I have tried. I keep getting subscript out of range errors. I think it is from the commandtext assignment. I want to use the pdata sheet in workbook C:~swap\providers.xlsx. The data range in sheet pdata is A1:BG1817.

Sub tst()

' this worksheet is already open
Call createConn("C:\~swap\providers.xlsx", "pdata")

End Sub

Sub createConn(wb As String, wks As String)

'wb is full workbook name
'wks is sheet name

'trying to dynamically find the active range on the selected worksheet
'lastrow = Workbooks(wb).Sheets(wks).Cells(Rows.Count, 1).End(xlUp).Row
'lastcol = Workbooks(wb).Sheets(wks).Cells(1, Columns.Count).End(xlToLeft).Column

'lastrow = Workbooks("C:\~swap\providers.xlsx").Sheets("pdata").Cells(Rows.Count, 1).End(xlUp).Row
'lastcol = Workbooks("C:\~swap\providers.xlsx").Sheets("pdata").Cells(1, Columns.Count).End(xlToLeft).Column

'this one works but I want to reference the workbook name also because there may 
'be more than workbook open with the same sheet name - I tried in the above 
'examples
lastrow = Sheets("pdata").Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Sheets("pdata").Cells(1, Columns.Count).End(xlToLeft).Column

Debug.Print "lastrow="; lastrow; "lastcol="; lastcol

Workbooks(wb).Connections.Add2 _
    Name:="ProvData", _
    Description:="", _
    ConnectionString:="WORKSHEET;" & wb, _
    CommandText:=??????
    lcmdtype:=XlCmdType.xlCmdExcel, _
    createmodelconnection:=True, _
    importrelationships:=False

'these are some of the commandtext variations I have tried
'CommandText:=pdata!A1: BG , _
'CommandText:=wks & "!" & wks.Range("a1:bg1817"), _
'CommandText:=wsPdata & "!" & wsPdata.Range("a1").CurrentRegion, _
'CommandText:=wsPdata & "!" & wsPdata.Range("a1:bg1819"), _
    
End Sub

• excel
• vba
• powerpivot

Share Improve this question Follow asked Mar 11 at 16:12 HealingJellyHealingJelly 15299 bronze badges 2

• Is "C:\~swap\providers.xlsx" an opened workbook in Excel? – taller Commented Mar 11 at 18:41
• Yes - the "C:\~swap\providers.xlsx" workbook is open when the macro is run. – HealingJelly Commented Mar 11 at 23:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Here are the updates to the script:

• Added an argument for the data connection name.
• Retrieved the connection parameter from the range address.
• If the source range is A1:BG1817 on the pdata sheet, the value of sRef will be [providers.xlsx]pdata!A1:BG1817. Extract the relevant parts of the reference as arguments for ConnectionString and CommandText.

Option Explicit
Sub test()
    Call createConn("ProvData", "providers.xlsx", "pdata") ' change sheet name as needed
End Sub

Sub createConn(sConName As String, Wb As String, Sht As String)
    With Workbooks(Wb)
        Dim sRef As String
        sRef = .Sheets(Sht).Range("a1").CurrentRegion.Address(False, False, , True)
        
        .Connections.Add2 Name:=sConName, Description:="", _
            ConnectionString:="WORKSHEET;" & Split(sRef, "!")(0), _
            CommandText:=Split(sRef, "]")(1), _
            lcmdtype:=XlCmdType.xlCmdExcel, _
            createmodelconnection:=True, importrelationships:=False
    End With
End Sub

Microsoft documentation:

Connections.Add method (Excel)

Range.Address property (Excel)