如何获取Excel工作簿的版本号? [英] How to get the version number of an Excel workbook?

问题描述

我有一本在Sharepoint文档库中进行版本控制的Excel图书，因此我可以转到文件"选项卡并查看类似的版本:

I have an Excel book that's versioned in a Sharepoint document library, so that I can go to the File tab and see versions like:

19.0: 11/10/2014 1:15 PM by xyz\tkl2
17.0: 10/12/2014 3:54 PM by xyz\tkl2
14.0: 10/11/2014 2:23 PM by xyz\92jf

我想获取最新的版本号，在这种情况下为 19.0 .我尝试使用以下代码:

I want to retrieve the most recent version number, in this case 19.0. I've tried using the following code:

Sub getVersions()

Dim DocVersions As DocumentLibraryVersions
Dim DVersion As DocumentLibraryVersion

Set DocVersions = ThisWorkbook.DocumentLibraryVersions

For Each DVersion In DocVersions
    Debug.Print DVersion.Index
    Debug.Print DVersion.Comments
    Debug.Print DVersion.Creator
    Debug.Print DVersion.Modified
    Debug.Print DVersion.ModifiedBy
    Debug.Print DVersion.Application
Next

End Sub

对于特定的文档版本，这似乎是可以获得的每个属性.但是这些属性都无法检索实际的版本号.例如，对于这些版本， .Index 只会检索 1 ， 2 和 3 .有没有办法获取实际的版本号?

This is every property that seems possible to get regarding a particular document version. But none of these properties retrieve the actual version number; e.g., .Index would only retrieve 1, 2, and 3 for these versions. Is there a way to get to the actual version number?

推荐答案

您可以通过打开文件的历史版本来获取此信息，其文件名将为 文件名 .xlsx，version xx.yy :修改日期 ，因此 xx.yy 将是 major中的版本号.次要格式.

You can get this information by opening the historical version of the file, and its filename will be filename.xlsx, versionxx.yy:modified date, so that xx.yy will be the version number in the major.minor format.

我在下面放置了我使用的代码.它将版本名称放在打开的工作表的H列中.检入时有一些错误，但不足以直接使用.最重要的是，它假定粘贴的电子表格是唯一打开的电子表格.您还需要关闭文件的版本号.

I've put the code I used below. It puts the version name in column H of the sheet that it opens. It's got a little error checking in, but not enough to use straight off. Most importantly, it assumes that the spreadsheet it's pasted in is the only spreadsheet open. You'll want to have the file you want the version numbers of closed, too.

Function fCheckVersions(stFilename As String) As Boolean
'
' stFilename is the full URL to a document in a Document Library.
'
Dim wb As Excel.Workbook
Dim VersionWorksheet As Excel.Worksheet
Dim dlvVersions As Office.DocumentLibraryVersions
Dim dlvVersion As Office.DocumentLibraryVersion
Dim OldVersion As Excel.Workbook
Dim stExtension As String
Dim iPosExt As Long

viRow = 3
ThisWorkbook.Worksheets("Index").Cells(viRow, 1) = stFilename

If Workbooks.CanCheckOut(stFilename) = True Then
    Set wb = Workbooks.Open(stFilename, , True)
    Set dlvVersions = wb.DocumentLibraryVersions
    If dlvVersions.IsVersioningEnabled = True Then
        ThisWorkbook.Windows(1).Visible = False
        ThisWorkbook.Worksheets("Index").Cells(viRow, 3) = "Num"
        Versions = " & dlvVersions.Count"
        On Error GoTo VersionFailed:
        For Each dlvVersion In dlvVersions
            ThisWorkbook.Worksheets("Index").Cells(viRow, 4) = "Version: " & dlvVersion.Index
            ThisWorkbook.Worksheets("Index").Cells(viRow, 5) = "Modified Date: " & dlvVersion.Modified
            ThisWorkbook.Worksheets("Index").Cells(viRow, 6) = "Modified by: " & dlvVersion.ModifiedBy
            ThisWorkbook.Worksheets("Index").Cells(viRow, 7) = "Comments: " & dlvVersion.Comments
                Set OldVersion = dlvVersion.Open()
                ThisWorkbook.Worksheets("Index").Cells(viRow, 8) = "FileName: " & OldVersion.Name
                If Workbooks.Count > 2 Then
                    Workbooks(3).Close SaveChanges:=False
                End If
            viRow = viRow + 1
        GoTo NextVersion:
VersionFailed:
        ThisWorkbook.Windows(1).Visible = True
        MsgBox "Fail"
NextVersion:
        Next dlvVersion
    End If
    wb.Close False
End If
Set wb = Nothing
DoEvents
End Function

这篇关于如何获取Excel工作簿的版本号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！