VBA - Tag of a two-dimensional variable in a SharePoint library

[Air1_16]

Hello everybody,


This is my issue below.

I created a macro that creates files. These files are uploaded to a SharePoint library. In this library, 2 variables have a mandatory required field: "Code_File" et "Codes_Countries" :
* "Code_File": one-dimensional variable with type of files values (ex: "A", "B", "C"...)
* "Codes_Countries": two-dimensional variable with couples of countries values (ex: "FR, US", "GE, IT", "ES, PT"...)

As a preamble, I work on the SharePoint with a connection to a network drive.

My 1st solution was to use custom properties of Excel. I created in a produced file a custom property named "Code_File" with the "A" value. I saved the file on my disk and I copied-pasted it on the SharePoint. The "A" value was read and displayed in the "Code_File" column as well. There's a problem with the "Code_File" variable. I didn't know how encodes 2 codes in a custom property value. So I entered "FR, US". This value is displayed as well on the SharePoint library in the "Code_File" column. But the filter shows that this value is not read correctly. "FR, US" is indeed read as an only string whereas it should be read as "FR" as country code 1 and "US" as country code 2. I tried to input values with other separators, with or without spaces ("FR-US", "FR ; US", "FR_US"...), but there is nothing to do: the value is not read correctly.

Ma 2nd solution was to save directly the produced file on the SharePoint. It's necessary to make a first save, a test save which generates an error that is handled to display "Content Type Properties" which are also the 2 required variables. Then, I enter a value for each variable, by using in VBA a table with 2 dimensions ("Tab_Codes_Countries(0)" et "Tab_Codes_Countries(1)") to enter the codes of the 2 countries. In the SharePoint library, there are defined lists for each variable, it's necessary to take care of the codes input, with codes that should belong to these lists, without that the final save will trigger an error and the file won't be saved. I also save the file twice, definitively. This time, the filter of the "Codes_Countries" column works as well. To precise, I tested copy-paste the produced file in the SharePoint library and then to open it, to avoid the firts test save that triggers an error. But it takes more time.

The 2nd solution is satisfactory, but it takes more time than the 1st: about 6-7 sec vs 1-2 sec for 1 file. I produce 900 files, so it's a very important difference.

* Is it possible, in my first solution, to tag a two-dimensional variable (which requires 2 values) with an Excel custom property?
* If it's impossible, is there any solution plus rapide et efficace avec VBA que ma 2ème solution ?


Thanks for your help.

Best regards.

 

[Air1_16]

Hello,






The solution, given HERE by nonoxp, was good to answer to my 1st question linked to my 1st announced solution! I had to encode "FS-US" in the CustomDocumentProperty like this: ;#FR;#US;#
with ;# before the first label, after the last label and between each label.


It works as well. The copied-pasted files on the SharePoint have values of "Codes_Countries" variable that are displayed as well and read by the filter as well.





On the other hand, good food of thought was given to me in an other forum with the use of a "Template" file. The idea is to create a "Template" file that is saved in the SharePoint to set ContentTypeProperties. This "Template" file is moved on my disk and all the files are created following this template, with the ContentTypeProperties inside. The 1st save which triggers an error and allows the display of the ContentTypeProperties is also useless because the ContentTypeProperties are already here.


I don't save files on my disk. I just save on the SharePoint. I tested it. Although less longer, it always takes more time to save files on the disk and then to copy-paste it on the network drive linked to the SharePoint URL. Basically: Time Save disk + Time Copy-paste SharePoint <<< Time Save SharePoint






My last idea was to save all files on my disk, with the CustomDocumentProperties, then to copy-paste the created folder in the network drive linked to the SharePoint URL rather than, for the production of each file, to copy-paste this to the network drive linked to the SharePoint URL. Finally, there's no time gain. It's even a little bit longer.






I also kept my first solution, that was modified and works as well this time. At the production of each file, I create some CustomDocumentProperties with the names of the variables names in the SharePoint library. For each CustomDocumentProperty, I put the right value, by using, for a multi-dimensional variable, the ";#" separator between each value, and before the first value and after the last value (ex: ";#FR;#US;#"). I save the file on my disk, that I copy-paste then on the network drive linked to the SharePoint URL. Files save is mandatory with this method. We could eventually delete the created folder on the disk at the macro execution end.


If a copied-pasted file is opened in the SharePoint, we can see that CustomDocumentProperties, which are always here, have allowed the creation of ContentTypeProperties which contain the input values in the CustomDocumentProperties.






Here is where is my reflection. If you have a better solution, I'm interested. Thanks for you help. Have a good day.