topic Re: Reading Vault files properties inside Excel VBA in Vault Customization Forum

Reading Vault files properties inside Excel VBA

Anonymous — Fri, 27 Oct 2017 15:35:32 GMT

Good afternoon!

I hope my IT skill is not too low to ask something here... 😉

Here is my problem: I want to verify (each day) if there is nothing wrong in my Vault files (we use Vault Professional 2018).
For example, I would like to verify that there aren't 2 files with the same part number, that a 3D-model and its drawing are in the same folder, that no files are in a certain folder, count how many files there is in a certain category, make a list of the files used inside an assembly, count the number of files modified by a certain user account, ...

As a summary, I want to read a lot of information that are inside Vault, and I want to use them inside Excel thanks to Visual Basic VBA. I don't need any forms, I want something very easy and I will simply use Excel sheets and cells to write the answers.

When I was young I made some small softwares in Visual Basic 6, I also know how to make small operations, comparisons, and work with cells inside Excel VBA.

I already installed the Vault SDK.
I also installed Visual Studio Express and tested the examples that are inside the SDK folder, VaultList and VaultBrowserSample works! So my computer is well configured.

But for the moment I don't know and I request your help for:
- loading the right Autodesk DLLs inside VBA (I absolutely don't know how to do it)
- login into Vault through Excel VBA (I want to use the standard Vault window available through the API)
- read a property from a file
- make searches and get a list of the files whose title looks like "motor***15.ipt" for example
- list the files that are inside an assembly
- list the "used in" cases of a file
- ...

I am a newbie.... 🙂 Can someone help me, or give me a website link where I can find the answer?

Thank you all 🙂

Re: Reading Vault files properties inside Excel VBA

ron_m — Thu, 02 Nov 2017 19:44:29 GMT

It can be done, but not directly with the Vault API.

Excel VBA will need a COM enabled dll.

I wrote one using the free version of Visual studio for the company I work for. Most of the example code works unchanged, the trick is the COM part.

Re: Reading Vault files properties inside Excel VBA

Anonymous — Tue, 07 Nov 2017 10:41:42 GMT

Hi!

Is it easy to use?

Do you agree to send me this COM file, and an Excel file with examples on how to use it ?

For example I want to read properties of files, list the files existing inside a folder, search for the children of a file, search for the parents of a file, ...

Thank you!

Re: Reading Vault files properties inside Excel VBA

ron_m — Tue, 07 Nov 2017 15:03:08 GMT

Using it in VBA is easy but you will need to edit the project in Visual Studio. I created it for use on our server, hard coding username, password, vault name, server name. Also you will need to sign it and add your own GUID.

I am attaching spreadsheet TestFindFile.zip and Visual studio template VaultComSample.ZIP. Copy the ZIP file to your "My Exported Templates" folder for the version of Visual Studio you are using (2015 in my case). Use this template to generate a new project, then follow the instructions in the comment at the top of the Class1.cs tab.

Re: Reading Vault files properties inside Excel VBA

Anonymous — Thu, 09 Nov 2017 16:58:21 GMT

Hi!

Thank you for these files.

I tried to use them, but it doesn't work. Your Excel Sub doesn't go inside the "oVault.LoginToVault()", so it doesn't list anything.

I did everything you asked, but:

- What is my own GUID, where can I get it? (I am absolutely sorry to ask this...). I saw that there are 2 different in your code, and a 3rd one in the project properties / Application / Assembly information

- When I generated the signing key, it created a ".pfx" file and not a ".snk" file. Is it a problem?

I think I didn't do any mistake....

Re: Reading Vault files properties inside Excel VBA

ron_m — Fri, 10 Nov 2017 20:51:08 GMT

For the GUID:

In Visual Studio select

Tools/Create GUID
In the Create GUID dialog box, select format (5)
1. Click the Copy button and exit the dialog
2. Paste over the existing ones in the name space (1 each for the Interface and Class)

namespace VaultComSample
{
    // Use Tools Create GUID
    [Guid("B2569D0A-7C83-4A2D-B4BC-40760BDF2B24")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]

    public interface _VaultComInterface

    // Use Tools Create GUID
    [Guid("9D760A8D-6D9C-474A-93CC-3C1781F26681")]
    [ClassInterface(ClassInterfaceType.None)]
    public class Vault : _VaultComInterface
    {

The GUID in the project properties is generated with the project

To create the ".snk" file

In Visual Studio

Select Project\VaultComSample Properties... Alt+F7
Select Signing
Check the "Sign the Assembly" check box
From the Pull-down select <New..>
Enter the Key file name (I used VaultComT in this example)
1. UN-Check the "Protect my key file with password") check box
2. Select the "OK" button
You should see VaultComT.snk add to your project
1. Delete any other".snk" files from the project
Every time is used the proceeding process I got a .SNK file. Don't know about PFX. Did you enter a password?

You must copy the compiled files to the workstation and register the dll for Excel to use it.

What messages did you get from regasm?

Did you copy clmloader.dll to your workstation folder?

Re: Reading Vault files properties inside Excel VBA

Anonymous — Thu, 16 Nov 2017 16:39:40 GMT

Hi!

- In the "Tools" menu, I don't have the "Create GUID" choice.... I use Visual Studio Express 2015 for Desktop, is it the problem?

I also searched in the "Customize" menu, if I could add this button, but I don't find it.

- for the SNK file, I put a password, that is why I had a PFX file. I didn't know that. Now it is OK, I have a SNK file and added it in the project.

- I copied all files from "bin\debug" to the computer on which I run Excel in "C:\Vault"

- I registered the dll, and it says that it succeeded (in French: "Inscription des types réussie" "L'assembly a été exporté vers 'C:\Vault\VaultComSample.tlb' et la bibliothèque de types a été inscrite avec succès")

- I copied "clmloader.dll" in the same folder as the other files "C:\vault"

Are the 2 GUID that I can't make the problem?

I use Visual Studio and the Excel/VBA file on 2 different computers, can it be a problem?

Re: Reading Vault files properties inside Excel VBA

ron_m — Thu, 16 Nov 2017 16:57:33 GMT

Did you get it to work?

Reusing my GUID should not cause a problem on your system unless you were to register my version of the dll.

The tool is in C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\Tools\guidgen.exe on my computer.

You may need to download and add it to using External Tools.

See the attached

Re: Reading Vault files properties inside Excel VBA

Anonymous — Fri, 17 Nov 2017 15:34:06 GMT

Thanks a lot for your help.

I found the "guidgen.exe", and launched it directly from Windows Explorer. I copied/paste these 2 new GUID in the code.

But unfortunately it still doesn't work, it doesn't go inside the "if oVault.LoginToVault() then".

I also tried to put the IP address of my Vault server instead of the name, but same problem.

Why do we have 2 server names, VaultFileServer and VaultDataServer? I have both filestore and SQL database on the same server, with the default configuration. I used the SDK example "VaultList" (in VB), and for the connection it requires only 1 server name as a string:

Dim results As VDF.Vault.Results.LogInResult = VDF.Vault.Library.ConnectionManager.LogIn(ServerName, VaultName, userName, password, AuthenticationFlags.ReadOnly, Nothing)

Can you have a look to the enclosed picture? I think I didn't make any mistake on the Vault name and the Vault servers?

Re: Reading Vault files properties inside Excel VBA

ron_m — Fri, 17 Nov 2017 17:01:14 GMT

The "VaultList" example is out of date and will not compile. The 2 server names allow splitting the database server from the file storage.

In the code I used the same server for both VaultFileServer and VaultDataServer.

Can you log into thin client (http://*** Server Name ***/AutodeskTC/Landing?ReturnUrl=http%3a%2f%2fpas2%2fAutodeskTC) using the Name, Password, Sever, Vault as in the code? (see attached Capture.PNG)

I don't understand "it doesn't go inside the "if oVault.LoginToVault() then". Are you getting an error when you run the code?

Try placing a break point on Set oVault = CreateObject("VaultComSample.Vault"), and step though the code.

also add

Debug.Print oVault.LastErrorMessage() ' Returns an error number, look it up in the API help document

after all calls using oVault to show error codes coming from VaultComSample.

Re: Reading Vault files properties inside Excel VBA

Anonymous — Mon, 20 Nov 2017 09:26:03 GMT

Hi!

This morning I tried again, beginning everything again from zero.

On the same computerr I used in th past, it still doesn't work, but I tried on another computer and it worked 🙂 !

I absolutely don't know what happened, and what is the difference between these 2 computers, I did exactly the same operations on each. Well, I will keep the 2nd computer to use it 😉

Now I need to investigate deeper to understand how to get properties, "uses" cases, "where used", get properties of older versions, ...

Still a lot of work!

Thank you again for all the time you spent for me 🙂