CODEWITHSUNDEEP
svnexcelvba
krizajb
krizajb

Reputation: 1814

Repository info (SVN) integration with Excel

i'm new to this kind of things. Kinda the first post ever regarding programming, since till now i managed to google everything that i needed. Thanks for your patience:) .

About my problem/goal: On our project we will have to have some kind of documentation management control. We will have around 160 .docx files (all files are already known), for version control we will use SVN. Those files will be saved in different folders (further on i will make repository structure), folders will have the function of a phase we are currently on in our project (we will have around 12 folders). When a phase is done, tag aka. official release will be made. Every file will have it's place in MS Excel WorkSheet Column with data like: Phase, Name, Revision, Tag, Hyperlink ..

What i have to do is: Macro that will search for the file on repository, if found read it's revision,tag (latest) .. write it in excel sheet and also make a hyperlink to that file in another column.

Repository structure:

I already made a macro that does the exact thing, but the problem is it's very slow. How and why?

I was using ShellAndWait("cmd.exe /c svn list --verbose http:\\localhost\trunk > text.txt"), parse that text file to get folders in trunk, then checked which files are in those folders (with another ShellAndWait for each folder), if they were found get revision number and so on ..

As you see in the end, in my code svn command was executed 13 times so file in repository was found. With svn list --verbose i also got revision number. For svn command to execute it lasts around 0.15-0.20 of a second, so my revision number gathering lasts around 2 seconds. Which is not a problem.

Getting tag is a problem, as i have folder in folder, so in the end we could have around 40 or even more svn executes, which would make my tag function very slow.

I have no clue how to access repository in any other way then with svn.exe. I am thinking about accessing repository remotely and querying database or what ever, still researching that part.

Hope you understand my problem, for any more informations i am avail whole day! Thanks for your help!

Upvotes: 3

Views: 5512

Answers (4)

nico
nico

Reputation: 127

If you use a client like Tortoise svn that provide an object usable in vba, you can query your repository without using the temporary text file to parse.

Instead of that you can have informations directly in your vba object:

example :

Dim oSvn As Object
Set oSvn = CreateObject("SubWCRev.object.1")

workingCopy = path & "\" & filename

oSvn.GetWCInfo workingCopy, 1, 1

If Not oSvn.IsSvnItem Then
    ErrorCode = -1
End If

This does not exactly do what you want, it is just an example of directly querying repository from vba code. it is quick and easy to write

There are lots of other functionnalities that comes with SubWCRev. Here is the official page.

https://tortoisesvn.net/docs/release/TortoiseSVN_en/tsvn-subwcrev-com-interface.html

Upvotes: 0

Frank
Frank

Reputation: 2648

svn list has an option -R or --recursive to include all subfolders. So, organizing your folder structure in a way that all required folders are subfolders of a main folder would reduce the number of calls needed. Alternatively, you can use the --depth option to control the depth of subfolders to descend.

Extract from svn help list output:

  -R [--recursive]         : descend recursively, same as --depth=infinity
  --depth ARG              : limit operation by depth ARG ('empty', 'files',
                            'immediates', or 'infinity')

I. e. organize you project in a way that the trunk and branches are relatively high in the folder structure, and the different phases are below that.

Upvotes: 1

renick
renick

Reputation: 3881

You can access the repository using the API for .NET. See this answer

You will probably need to use VSTO instead of VBA though.

Upvotes: 0

khmarbaise
khmarbaise

Reputation: 97349

You can write an Java thing which can access the SVN repository via SVNKit directly without calling svn.exe and extract the information from there and write into Excel files via PIO framework. That could be build as a web application and create the information on the fly...

Upvotes: 0

Related Questions