PDA

View Full Version : [SOLVED] Put Country Codes in Excel For SharePoint



Air1_16
07-25-2017, 06:38 AM
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.

snb
07-25-2017, 08:26 AM
Use the workbook_Open event to split the customproperty tag.

Air1_16
07-25-2017, 08:39 AM
Sorry, I don't know what you mean when you say "split the customproperty tag".

SamT
07-25-2017, 12:08 PM
:dunno: Maybe
FR, 1
US, 2
GE, 3
etc

Try different separators, including a Space
FR. 1
FR.1
FR- 1
FR -1
FR 1
FR/1

As a last resort, read the Manual

Just a wild guess, but "Codes_Countries" may be the name, no ext, of a plain text file with
COUNTRY; CODE
FR; 1
US; 2
GE; 3
etc

Air1_16
07-26-2017, 01:39 AM
Sorry, I don't understant what you say for "FR, 1", "US, 2", "GE, 3".

I tried different seperators, but It doesn't work as well. I noticed that the good seperator seems to be ";", but the 2 codes are not read as well.

p45cal
07-26-2017, 02:21 AM
Crosspost http://www.vbforums.com/showthread.php?850747-VBA-Tag-of-a-two-dimensional-variable-in-a-SharePoint-library
and
https://www.excelforum.com/excel-programming-vba-macros/1194231-vba-tag-of-a-two-dimensional-variable-in-a-sharepoint-library.html
and
https://www.mrexcel.com/forum/excel-questions/1015885-vba-tag-two-dimensional-variable-sharepoint-library.html
and in French:
https://forum.excel-pratique.com/excel/vba-tag-d-une-variable-bidimensionnelle-sur-une-librairie-t96468.html
https://www.excel-downloads.com/threads/vba-tag-dune-variable-bidimensionnelle-sur-une-librairie-sharepoint-pour-un-fichier.20018877/
https://www.developpez.net/forums/d1729748/logiciels/microsoft-office/excel/macros-vba-excel/vba-tag-d-variable-bidimensionnelle-librairie-sharepoint-fichier/
https://openclassrooms.com/forum/sujet/vba-tag-d1-var-bidim-sur-1-librairie-sharepoint

Cross posting rules are pretty universal across these sites, you should be linking to all of them left, right and centre. Why? Have a read of http://www.excelguru.ca/content.php?184

Could you give us a screen shot of the problem custom property of a file where it is correct? A bit like:
19877

Air1_16
07-26-2017, 02:40 AM
This is my screenshot:

19878

https://img15.hostingpics.net/pics/636123ccccc.png

Air1_16
07-26-2017, 02:41 AM
Test

p45cal
07-26-2017, 02:48 AM
Can't see anything at all at that resolution.

Air1_16
07-26-2017, 02:55 AM
Can't see anything at all at that resolution.
-> https://img15.hostingpics.net/pics/947657cccccccccc.png

p45cal
07-26-2017, 03:33 AM
CustomDocumentproperties can't be an array. I tried doing that in VBA but it failed. I even tried setting up a Name in the workbook and putting a text array in that (that bit is easy) and then linking the CustomProperty to tthat name; it allowed me to do that but the CustomDocumentProperty just ended up as #VALUE!.
I know nothing about Sharepoint. Do the values in the bottom right of your screenshot come from the CustomProperties of the Excel file? If so, then is the name of the custom property an exact match for the column name in SharePoint ("Countries", "Countries ", etc.)?
If your screenshot is of a working file, it looks as if it is just a plain string like "CY,FC".
I think you're going to have to trial-and-error it manually until you get the format and name correct, then we can look at automating the process to save the file only once.

re:"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…"
Where is the code for this process? Can you show it? It may help us work out what to do.

SamT
07-26-2017, 06:09 AM
And http://www.ozgrid.com/forum/showthread.php?t=204976


Sorry, I don't understant what you say for "FR, 1", "US, 2", "GE, 3".

I tried different seperators, but It doesn't work as well. I noticed that the good seperator seems to be ";", but the 2 codes are not read as well.
A 2-dimensional variable has 2 values
FR is one part
1 is the other part

FR; 1 is an example of a 2-d value. I don't know if that is what SharePoint needs.

SharePoint Locale Codes: http://howididit-sharepoint.blogspot.com/2012/06/list-of-locale-codes.html

Air1_16
07-26-2017, 06:15 AM
CustomDocumentproperties can't be an array.

That's exactly my big problem, and maybe there's no direct solution. Maybe it's possible to find a reliable way.



I even tried setting up a Name in the workbook and putting a text array in that (that bit is easy) and then linking the CustomProperty to tthat name; it allowed me to do that but the CustomDocumentProperty just ended up as #VALUE!

I tried this with 2 cells ("A1" & "A2"). The value of the Custom Property was the value of "A1" cell.



I know nothing about Sharepoint. Do the values in the bottom right of your screenshot come from the CustomProperties of the Excel file? If so, then is the name of the custom property an exact match for the column name in SharePoint ("Countries", "Countries ", etc.)?

You're right. The name of the Custom Property must be the the same as the SharePoint library variable label.




If your screenshot is of a working file, it looks as if it is just a plain string like "CY,FC".
I think you're going to have to trial-and-error it manually until you get the format and name correct, then we can look at automating the process to save the file only once.

I tried many attempts with differents seperators. ";" seems to be the right seperator. But "CY; FC" is read as one country, and not as the combination of "CY" country" and "FC" country".

Air1_16
07-26-2017, 06:17 AM
re:"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…"
Where is the code for this process? Can you show it? It may help us work out what to do.

Yan can find my file below:
filedropper.com/savetagfilesonthesharepoint-v011 (https://filedropper.com/savetagfilesonthesharepoint-v011)

snb
07-26-2017, 06:19 AM
You can always upload a file here in this forum (the best option)

Air1_16
07-26-2017, 06:20 AM
OK, thanks.

The SharePoint library need 2 countries codes in "Codes_Countries" variable, for example "FR, US".

I can enter "FR, US" or "FR, 1" in the Custom Property value, this value will always read in the SharePoint library as 1 country, as 1 string.

snb
07-26-2017, 06:22 AM
Petitio ad principium.

SamT
07-26-2017, 07:00 AM
The SharePoint library need 2 countries codes in "Codes_Countries" variable,
Maybe it needs one country and one Locale Code. For example

FR; 1035

p45cal
07-26-2017, 07:07 AM
Yan can find my file below:
filedropper.com/savetagfilesonthesharepoint-v011 (https://filedropper.com/savetagfilesonthesharepoint-v011)
I'm getting warnings about this site not being secure both in FireFox and Chrome.
If you can upload/attach here, that would be better.

Air1_16
07-26-2017, 07:07 AM
For me, it's not a question of code type ("US", "2" or "1035"). The problem is that "FR; 1035" is read as only string. Same thing for "FR; US" and "FR; 2".

Air1_16
07-26-2017, 07:10 AM
If you can upload/attach here, that would be better.

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

p45cal
07-26-2017, 07:14 AM
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
07-26-2017, 07:36 AM
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
07-26-2017, 10:26 AM
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
07-27-2017, 01:43 AM
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
07-27-2017, 03:28 AM
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
07-27-2017, 06:00 AM
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
07-27-2017, 06:39 AM
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
07-27-2017, 07:32 AM
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
07-27-2017, 09:21 AM
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/article/Save-a-workbook-or-worksheet-as-a-template-58c6625a-2c0b-4446-9689-ad8baec39e1e#__toc274819700





I don't master how a "xltx" template file works.Some Googling resulted in:
http://www.makeuseof.com/tag/quickly-create-custom-excel-template-save-time/
https://support.office.com/en-us/article/Save-a-workbook-as-a-template-855d15bd-39ec-458b-8af8-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
07-27-2017, 10:16 AM
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/article/Save-a-workbook-or-worksheet-as-a-template-58c6625a-2c0b-4446-9689-ad8baec39e1e#__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
07-27-2017, 11:37 AM
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
07-27-2017, 11:58 AM
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
07-27-2017, 01:43 PM
Does that mean that the Template should be based on a Workbook that has all that?Yes.

SamT
07-27-2017, 03:37 PM
:D

Air1_16
07-28-2017, 01:39 AM
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
07-28-2017, 05:40 AM
When you are happy with the solution, please use the Thread Tools just above the Thread to mark it "Solved." Thanks

Air1_16
08-02-2017, 09:24 AM
Hello,



The solution, given HERE (https://www.developpez.net/forums/d1729758/dotnet/general-dotnet/developpement-office-system/sharepoint/developpement-sharepoint/vba-tag-d-variable-bidimensionnelle-librairie-sharepoint-fichier/#post9504185) 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.