Making Connections.Add2 Dynamic Trouble

[sxhall]

Hi,

Thanks for reading.

I am trying to connect a table using connections.add2 after a running a macro to create a table for a user to input data on to. On another hidden and locked tab i am attempting to then link it to the first table created.

My code is naming the table that is originally created based upon a user input so I can reference it easily as well, call it "User_Table" for this example.

The code below is what I have recorded but I have tried several ways to use Application.ActiveWorkbook.FullName & Application.ActiveWorkbook.Name along with the name of the table to get the code to look at the newly created table but I have given up now!

    Workbooks("Userform for Employee Entry.xlsm").Connections.Add2 _
        "WorksheetConnection_Employee Entry.xlsm!Table30", "", _
        "WORKSHEET;C:\Desktop\Documents Local\NIT\Employee Entry.xlsm" _
        , "Employee Entry.xlsm!Table30", 7, True, False
        
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("WorksheetConnection_Userform for Employee Entry.xlsm!Table30"), _
        Destination:=Range("$C$6")).TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Table30_132"
        .Refresh
    End With

My question is how do make the above usable so it looks at the current file path, file name and newly created table!

One of my attempts went along the lines of...

Dim FPath As String
Dim FName As String
Dim TName As String

FPath = Application.ActiveWorkbook.FullName
FName = Application.ActiveWorkbook.Name
TName = User_Table


    Workbooks(FName).Connections.Add2 _
        "WorksheetConnection_" & FName & TName, "", _
        "WORKSHEET;" & FPath _
        , FName & TName, 7, True, False
        
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("WorksheetConnection_" & FName & TName), _
        Destination:=Range("$C$6")).TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = TName
        .Refresh
    End With

In theory the above should allow the end users to store the file and name it what they want and then name the tables whatever they want as they create them and it will still reference it corrcetly.


Any help is greatly appreciated

Please note that I am calling a day for the weekend now so may not reply back to any assistance until Monday. Enjoy your weekend too

Steven

 

[sxhall]

A shameless bump!

 

[sxhall]

Have now updated my code to be as follows...

    With ActiveWorkbook
    
     .Connections.Add2 ShName, "", FPath, ShNameTrim, 7, True, False

    End With

Where...

FName = Application.ActiveWorkbook.Name
ShName = Sheet name
ShNameTrim = The name of the table

Still cannot get this work having tried numerous variations now.

Just want to be able to add a Table to one tab and then link to it using data connection.add2 on another sheet in the same workbook.

I will know the name of my worksheet and table but they are named via a userform.

Any assistance will be greatly appreciated

Steven

 

[Munozz]

Hello, did you ever figure out how to do this; this is literally the same issue I am dealing with. Thank you.

 

[sxhall]

Hello, did you ever figure out how to do this; this is literally the same issue I am dealing with. Thank you.

No, never got to the bottom of this. The project I was doing this for was scrapped so I never had a need to finish investigating to find a resolve and never went back to it either.

Could start a new thread and see if anyone has an answer now.

 

[Miroslav314]

I think your source code is almost correct. I used it as an inspiration and it works.
Please just replace in all places the code & FName & TName
by the code & FName & "!" & TName
After this adjustement my code made according to your code works like a heaven.
Thank you
Miroslav