Put Country Codes in Excel For SharePoint

[Air1_16]

If you can upload/attach here, that would be better.

Sorry, it's impossible: "Upload of file failed"...

[p45cal]

Sorry, it's impossible: "Upload of file failed"...

Does it say why? Is it an Excel file? If so try saving it as an xlsb, they're always smaller.
And if it's a big file, point us where to look, especially if there's lots of code.

[Air1_16]

Does it say why? Is it an Excel file? If so try saving it as an xlsb, they're always smaller.
And if it's a big file, point us where to look, especially if there's lots of code.

I try with "xlsb", but it doesn't work. The size file is 3,5 Mo. And I simplified the VBA code.

This is an other link:
https://www.file-upload.com/m5uqg82kjwog

[p45cal]

I have no access to any SharePoint, however I've been searching the net for information and I want to examine a file that you've successfully added to that sharepoint. A simple a file as possible, maybe even an empty workbook. Could you download it from that sharepoint and attach it here. No code either if possible, even an .xlsx file.
I'm hoping to find its metadata, .contenttypeproperties and customdocument properties. I'd like you to avoid adding your own customdocumentproperties and let the process of uploading a virgin file to SharePoint and adding the necessary tags while doing so, take place. Then download that file and attach it here.

[Air1_16]

Ok. As you want, these are my two saved files on the SharePoint, with the two methods I described:


* "Fic_Test_1stOption.xlsx" : with CustomDocumentProperties, file saved on my disk and then copied-pasted in the network drive linked to the SharePoint URL

* "Fic_Test_2ndOption.xlsx" : with ContentTypeProperties, file saved twice directly in the network drive linked to the SharePoint URL (first save to reveal the ContentTypeProperties that don't exist on a virgin file)

[p45cal]

From my research (I'm not an authority on this) it looks very much like you're dealing only with .ContentTypeProperties and not .CustomDocumentProperties (unless you can tell me different!).
Further, it seems you cannot add .ContentTypeProperties to an Excel file with vba, but you can set/assign values to existing ones.
So in order only to have to save once to SharePoint I' suggest using a template Excel file (.xltx or .xltm) which has them in (like your Fic_Test_2ndOption.xlsx has) and in your code the likes of

Dim zz(0 To 2) As String
zz(0) = "AE"
zz(1) = "GB"
zz(2) = "CZ"
ActiveWorkbook.ContentTypeProperties("Countries") = zz
'ss = xx("Countries").Validate 'I *think* ss should be an empty string if valid, anything else and it's not valid.

works.

[Air1_16]

The use of ContentTypeProperties is the normal way. The use of CustomDocumentProperties is the convoluted way.

CustomDocumentProperties is the faster way how I work (2 sec per file to go to the SharePoint vs 7-8 sec with ContTypeProperties).

If I understand you as well, I need to have a template in the SharePoint, right? And then I need to open this template for each produced file?

Last, I don't understand your last line in comment. What is ".Validate" ?

[p45cal]

I think you'll be able to store the template (.xltx or .xltm) locally, then if (before you save to SharePoint) you set the ContentTypeProperties in code (ActiveWorkbook.ContentTypeProperties("Countries") = zz,ActiveWorkbook.ContentTypeProperties("Source") ="HR" etc. (How you get those values in there is up to you - standard stuff except for the Countries array)) you won't have to save twice/fill in values. It should be the fastest way.

.Validate is something I suspect you can use to help stop the file being rejected by SharePoint; I think it could allow you to check that the ContentTypeProperties are valid values before trying to save to SharePoint

Along the lines of:
If ActiveWorkbook.ContentTypeProperties("Countries").Validate <> "" then msgbox "Stop, you need to correct the Countries properties!"

It looks like you might also be able to validate them all at once:
If ActiveWorkbook.ContentTypeProperties.Validate <> "" then msgbox "Stop, you need to correct the document properties!"

[Air1_16]

I have to save my template file on the SharePoint. If I saved it locally, how could I set the ContentTypeProperties? The only way I found is to force a first save. If the template file is on the SharePoint, I can open it with these ContentTypeProperties.

I tried to use a ".xltx" and a ".xlsx" as template to use ContentTypeProperties already existed in the template file. But I can't do what I want. That is indeed with the opened template file, to create several files with different contents by keeping the template file opened. But I can't and I have to open the template file for each produced file.

I don't master how a "xltx" template file works.

I tested "Validate". If there are no "Countries" ContentTypeProperty in the file, there is an error in the VBA code. If there is a "Countries" ContentTypeProperty with the value, "Validates" returns nothing (""). But if there's no value, "Validate" returns sevaral "C": "CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC".

[p45cal]

I have to save my template file on the SharePoint. If I saved it locally, how could I set the ContentTypeProperties? The only way I found is to force a first save. If the template file is on the SharePoint, I can open it with these ContentTypeProperties.

The ContentTypeProperties are saved with the file; they were present in the files you attached earlier. They save with a template too, even saved locally.

I tried to use a ".xltx" and a ".xlsx" as template to use ContentTypeProperties already existed in the template file. But I can't do what I want. That is indeed with the opened template file, to create several files with different contents by keeping the template file opened. But I can't and I have to open the template file for each produced file.

You'll be able to open a (local) template file (using New in Excel - you're not editing the xltx/xltm file itself when you do this, you're using the template to create an entirely new .xlsx/.xlsm file), then save as different variations of the same file and retain/adjust ContentTypeProperties.
See https://support.office.com/en-gb/art...__toc274819700

I don't master how a "xltx" template file works.

Some Googling resulted in:
http://www.makeuseof.com/tag/quickly...ate-save-time/
https://support.office.com/en-us/art...8-456517a661d7

I tested "Validate". If there are no "Countries" ContentTypeProperty in the file, there is an error in the VBA code. If there is a "Countries" ContentTypeProperty with the value, "Validates" returns nothing (""). But if there's no value, "Validate" returns sevaral "C": "CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC".

That's right; you can test for an empty string which would mean all is well. I gave you some snippets at the end of msg#29. If there's no "Countries" ContentTypeProperty in the file you'll have to error trap that - but it means you're using the wrong file anyway.


What version of Excel are you doing all this in?

[Air1_16]

The ContentTypeProperties are saved with the file; they were present in the files you attached earlier. They save with a template too, even saved locally.

I don't understand your answer. First, I produce the file locally. So I don't know how I can set ContentTypeProperties, because this properties are created when the file is in the SharePoint.

You'll be able to open a (local) template file (using New in Excel - you're not editing the xltx/xltm file itself when you do this, you're using the template to create an entirely new .xlsx/.xlsm file), then save as different variations of the same file and retain/adjust ContentTypeProperties.
See https://support.office.com/en-gb/art...__toc274819700

OK, I understand as well. I tested it and it worked. I we agree, I have to put in the created file the content of my produced file.
Just a question. Is it possible to save a file according to a template?

What version of Excel are you doing all this in?

Excel 2016

[p45cal]

I don't understand your answer. First, I produce the file locally.

So far, yes, from a blank .xlsx file created the normal way presumably from New in Excel, but in future create it from the template (held locally) which already has the ContentTypeProperties in it. I can already do that here, with the file you attached in msg#26, so there's no need to download from SharePoint each time. Any new file you create using that template already has the ContentTypeProperties. You can even arrange for that template to be the default template when you create a new Workbook in Excel.

I have to put in the created file the content of my produced file.

Whatever it is that you do to 'produce' a file, 'produce' it starting with a new file based on the locally saved template - you shouldn't have to go about copying sheets from one workbook to another.

Just a question. Is it possible to save a file according to a template?

That would be nice wouldn't it, but I don't think so. All you can do is open a new file using the template, and move all the sheets across, then delete the original blank sheet(s) that existed when you opened the new file using the template. That wouldn't move macros in the Thisworkbook module, nor in any Standard or Class modules, nor any Styles etc. etc.

[SamT]

That would be nice wouldn't it, but I don't think so. All you can do is open a new file using the template, and move all the sheets across, then delete the original blank sheet(s) that existed when you opened the new file using the template. That wouldn't move macros in the Thisworkbook module, nor in any Standard or Class modules, nor any Styles etc.

Does that mean that the Template should be based on a Workbook that has all that?

[p45cal]

Does that mean that the Template should be based on a Workbook that has all that?

Yes.

[SamT]

[Air1_16]

So far, yes, from a blank .xlsx file created the normal way presumably from New in Excel, but in future create it from the template (held locally) which already has the ContentTypeProperties in it. I can already do that here, with the file you attached in msg#26, so there's no need to download from SharePoint each time. Any new file you create using that template already has the ContentTypeProperties. You can even arrange for that template to be the default template when you create a new Workbook in Excel.

Whatever it is that you do to 'produce' a file, 'produce' it starting with a new file based on the locally saved template - you shouldn't have to go about copying sheets from one workbook to another.

That would be nice wouldn't it, but I don't think so. All you can do is open a new file using the template, and move all the sheets across, then delete the original blank sheet(s) that existed when you opened the new file using the template. That wouldn't move macros in the Thisworkbook module, nor in any Standard or Class modules, nor any Styles etc. etc.

OK. I understand. I agree with you.

[SamT]

When you are happy with the solution, please use the Thread Tools just above the Thread to mark it "Solved." Thanks

[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 here 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.