从工作簿中提取.value时类型不匹配

I'm trying to set up the dashboard I just created to automatically(well, as soon as it gets opened again) update itself as I make changes in the future. I've worked out the following method of doing it, but I keep getting a 'Type Mismatch' error on the line marked with **. I feel like I"m missing something pretty obvious here, but for the life of me, I can't figure it out. Any ideas?

我正在尝试设置我刚刚创建的仪表板(好吧,一旦它再次打开)更新自己,因为我将来会做出更改。我已经找到了以下方法,但我在标有**的行上遇到“类型不匹配”错误。我觉得我在这里错过了一些非常明显的东西,但对于我的生活,我无法弄清楚。有什么想法吗?

Private Sub Workbook_Open()
Dim UpDateBook As Workbook
Dim CurrVer As String
Dim AdminFile As String
Dim AdminFolder As String
Dim MyPath As String

''Change the next two according to where the admin file will be located.
AdminFile = "\\dallfile\Databases\Reports\Dashboard\Dashboard Update.xlsx"
AdminFolder = "\\dallfile\Databases\Reports\Dashboard"

MyPath = ThisWorkbook.Path
MyPath = MyPath & "\"

Application.ScreenUpdating = False

Set UpDateBook = Workbooks.Open(AdminFile, , True)

**CurrVer = Workbooks(UpDateBook).Sheets("Version_Log").Range("A5000").End(xlUp).Value
CurrVer = CurrVer & ".xlsm"

If ThisWorkbook.Name <> CurrVer Then
    MsgBox ("There is a new update for your file available. It will be loaded as soon as you press OK")
    Workbooks.Open Filename:=AdminFolder & CurrVer
    Application.EnableEvents = False
    Workbooks(CurrVer).SaveAs Filename:=MyPath & CurrVer, FileFormat:=xlNormal
    Application.EnableEvents = True
    With ThisWorkbook
        .Saved = True
        .ChangeFileAccess Mode:=xlReadOnly
        Kill pathname:=.FullName
        .Close savechanges:=False
    End With
End If

Application.ScreenUpdating = True

End Sub

2 个解决方案

#1

I think this is because you are using your workbook object incorrectly.

我认为这是因为您错误地使用了工作簿对象。

You set the workbook here..

你在这里设置工作簿..

Set UpDateBook = Workbooks.Open(AdminFile, , True)

and then you should use it like this.

然后你应该像这样使用它。

CurrVer = UpDateBook.Sheets("Version_Log").Range("A5000").End(xlUp).Value

#2

Just a matter of syntax. Once you have:

只是语法问题。一旦你有:

Set UpDateBook = Workbooks.Open(AdminFile, , True)

You should use it like:

你应该像以下一样使用它:

CurrVer = UpDateBook.Sheets("Version_Log").Range("A5000").End(xlUp).Value

#1