Enable macro on a single workbook

Question

So, I'm working on an automation project and have stumbled on a roadblock because I can't call anything on a downloaded Excel file.

When I try opening the Excel file manually, its VB Editor is disabled... All other opened Excel files have it enabled.

I'm using below for downloading/opening the said Excel (XLSX) file.

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

I can open the Excel file with Workbooks.Open, but the opened XLSX file is not listed in the VBAProject window so I can't call anything on the sheet.

Has anyone encountered this here? Can we force-enable the macro settings on a single workbook?

Enable macro on a single workbook

Answers (1)

Related Questions