i am currently working on a app that is a combination of java + vba. What i want to do: Some of the data is exported to excel with the help of the apache poi library in the form of a table. I am using this table to define a couple of sub tables that contain the same information, just not the whole table, for example my main table has columns A to F and 300 rows of info, my sub table has column A to C with 150 rows of info. What i do is declare via apache poi 2 different named ranges, one for the header of the table and a second one for the exact content that i want. My goal is to combine these 2 named ranges into one by joining the references, since i want it as a separate table that i will export to a powerpoint template containing placeholders for these tables.
What i have: I will show just the vba code, since i don't have any problems with the java part.
Public Sub test()
Dim rangeHeaderValue As Variant
Dim rangeContentValue As Variant
rangeHeaderValue = ThisWorkbook.Names("TABL1_Performance_Data_1").RefersTo
rangeContentValue = ThisWorkbook.Names("TABL2_Performance_Data_1").RefersTo
ThisWorkbook.Names.Add Name:="test", RefersTo:=Right(rangeHeaderValue, (Len(rangeHeaderValue) - 1)) &
";" & Right(rangeContentValue, (Len(rangeContentValue) - 1))
End Sub
What i receive as reference to the named range:
="Performance_Data!$A$10:$Q$14;Performance_Data!$A$14:$Q$18"
What i expect: The same, but without quotes, since it doesn't work for some reason. What may be the problem or is there any type of better solution? I am not really experienced with VBA, sorry in advance. Tried doing it with Union, but it gives me errors because it expects ranges and the ranges of the named ranges change dynamically, a.k.a on each export, since the data is different.
add an =-sign: (and the , instead of the ;)
Public Sub test()
Dim rangeHeaderValue As String
Dim rangeContentValue As String
rangeHeaderValue = ThisWorkbook.Names("TABL1_Performance_Data_1").RefersTo
rangeContentValue = ThisWorkbook.Names("TABL2_Performance_Data_1").RefersTo
ThisWorkbook.Names.Add Name:="test", RefersTo:="=" & Right(rangeHeaderValue, (Len(rangeHeaderValue) - 1)) & "," & Right(rangeContentValue, (Len(rangeContentValue) - 1))
End Sub