VBA如何打开Excel文件并告诉其创建日期

Question

我正在尝试从某个地方打开一个文件，并获取打开的文件的创建日期。但是，下面的行给了我一个错误"运行时错误，自动化错误和未指定的错误"。我该如何解决这个问题？谢谢。

MsgBox wb2.内置文档属性("创建日期"(

Private Sub CommandButton1_Click()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2
    Dim WS As Worksheet
    Set wb1 = ActiveWorkbook
    'Clear Summary tab
    wb1.Worksheets("Summary").Cells.Clear

    'Delete existing worksheet with name "Task List Data Export"
    For Each WS In Worksheets
    If WS.Name = "Task List Data Export" Then
        Application.DisplayAlerts = False
        Sheets("Task List Data Export").Delete
        Application.DisplayAlerts = True
        End
        End If
    Next
    'Get the File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select file")
    If Ret1 = False Then Exit Sub

    'Copy file
    Set wb2 = Workbooks.Open(Ret1)
    MsgBox wb2.BuiltinDocumentProperties("Creation Date")
    wb2.Sheets(1).Select
    wb2.Sheets(1).Copy After:=wb1.Sheets(2)

    'Close opened file
    wb2.Close savechanges:=False
    Set wb2 = Nothing
    Worksheets("Cover").Activate
End Sub

Answer 1

如果 Microsoft Excel 未为某个内置文档属性定义值，则读取该文档属性的 Value 属性会导致错误。

从工作簿。内置文档属性

总是有一个文件的创建日期，因此您收到该错误非常令人惊讶。我从来没有遇到过这个错误。即使是尚未保存的新创建文件也会为您提供"文件创建日期和时间"。

这是一个替代方案。

Dim CtdDate As String
Dim fs As Object, f As Object
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
                                   , "Please select file")
If Ret1 = False Then Exit Sub
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(Ret1)
CtdDate = f.DateCreated
Set wb2 = Workbooks.Open(Ret1)
MsgBox CtdDate