在单个工作簿上启用宏

Question

所以，我正在进行一个自动化项目，遇到了一个障碍，因为我无法对下载的Excel文件调用任何内容。

当我尝试手动打开Excel文件时，其VB编辑器被禁用。。。所有其他打开的Excel文件都启用了它。

我正在使用以下内容下载/打开上述Excel(XLSX(文件。

Sub GetLogins()
Application.ScreenUpdating = False
NavSheet.Unprotect [pw]
Dim LoginWkbk As Workbook, LoginWksht As Worksheet
Dim WinHTTPRequest As Object, ADOStream As Object
Dim URL As String
Dim FileRev As Long, LastRow As Long, x As Long
Dim ts As Double
ts = Timer
FileRev = [Revision] ' The current logins file revision
FileRev = FileRev + 1 ' Check for the next revision. Hah!
TryAgain:
If Password = "" Then AcctLoginsForm.Show ' Password not (yet?) supplied
' Second line of security.
If Username = "" Or Password = "" Then
' This checks if the user provided the complete information required.
' If they didn't we would clear the admin logins sheet of any information that was in there.
Call ClearAcctsSheet
MsgBox "Insufficient information submitted.", vbOKOnly, "Window_Title"
GoTo ExitSub
End If
' The logins file URL
URL = "https://mysecreturl" & FileRev & ".xlsx"
Set WinHTTPRequest = CreateObject("Microsoft.XMLHTTP")
With WinHTTPRequest
' "GET" command with username and password
.Open "GET", URL, False, Username, Password
.Send
Select Case .Status
Case 401
' Incorrect credentials.
If MsgBox("Incorrect Username/Password supplied. Try again?", vbYesNo, "Window_Title") = vbYes Then
Call ClearAcctsSheet
Password = ""
GoTo TryAgain
Else
GoTo ExitSub
End If
Case 404
' The next revision is not yet uploaded, so we set to download the previous revision
FileRev = FileRev - 1
GoTo TryAgain
Case 200
' Set the "Revision" named range to the current file revision
[Revision] = FileRev
End Select
Set ADOStream = CreateObject("ADODB.Stream")
ADOStream.Open
ADOStream.Type = 1
ADOStream.Write .ResponseBody
ADOStream.SaveToFile Environ$("temp") & "logins.xlsx", 2 ' Save the file in the temp file overwriting if the file exists
ADOStream.Close
End With
' Need to clear out the Accounts Sheet fields before populating it with the new credentials
AcctsSheet.Range("A:C").ClearContents
Set LoginWkbk = Workbooks.Open(Environ$("temp") & "logins.xlsx")
Set LoginWksht = LoginWkbk.Sheets(1)
LastRow = LoginWksht.Cells(Rows.Count, 1).End(xlUp).Row ' Last row. Duh.
For x = 1 To LastRow
' Copy-pasting the information from the logins file crashes Excel, hence this for-loop.
AcctsSheet.Range("A" & x).Value = LoginWksht.Range("A" & x).Value
AcctsSheet.Range("B" & x).Value = LoginWksht.Range("G" & x).Value
AcctsSheet.Range("C" & x).Value = LoginWksht.Range("H" & x).Value
Application.StatusBar = "Extraction complete. Time elapsed: " & Round(Timer - ts, 2)
If LoginWksht.Range("A" & x).Value = "" Then
Exit For
End If
Next x
LoginWkbk.Close False ' Close the logins file
Kill Environ$("temp") & "logins.xlsx" ' Delete the logins file
[DateToday] = Format(Now, "m/d/yyyy") ' Set the "DateToday" named range to the current day.
ExitSub:
NavSheet.Protect [pw]
NavSheet.Activate
ThisWorkbook.Save
SetToNothing WinHTTPRequest, ADOStream, LoginWkbk, LoginWksht
Application.ScreenUpdating = True
End Sub

我可以用Workbooks.Open打开Excel文件，但打开的XLSX文件没有列在VBAProject窗口中，所以我不能调用工作表上的任何内容。

有人在这里遇到过这种情况吗？我们可以在单个工作簿上强制启用宏设置吗？

Answer 1

.xlsx文件不能有宏。在我的测试中，VB编辑器没有被禁用，只是文件中没有要显示的宏。如果在Excel设置中启用了宏，则工作簿可能仍需要位于Excel的受信任位置才能运行宏。