The Given Key Was Not Present In the Dictionary error...on open, but not when manually triggered

[jgottlieb]

I have a translation table that I am doing a lookup on when I open a spreadsheet. One of my underlying constraints is that the translation table could (and likely will) be moved to a different location on the network. I am working with an environment that has "System Folders" and "Applications" so ultimately, the translation table file will be in a folder like:
\\server\folder1\folder2\systemfolder\applicationfolder

Unfortunately, I am in a situation where I'm working with a management system that is organized by Domains and Projects. A Project lives in a Domain in the management system. For the environment, a Project in the management system translates to an "Application" which belongs to a "System Folder" (which doesn't necessarily map to a Domain.

I have a tab where I can control the values of what's needed:

The System Folder and the Application folder values in B2 and B3 are lookups to the translation table using B9 as the lookup value. I tried to concatenate the translation file location together, but that requires Indirect (which I don't have a great handle on) AND requires the translation file to be open. B5 and B6 are just concatenate functions of the application folder and additional folder information within the application folder structure.

I thought to have the user of the spreadsheet simply open the translation file as the first step of what this workbook ultimately is being designed to do. Since its location is off the Base Path (the folder structure I know will change), I created a macro to Open, then close the file (and attached it to a ribbon button:

Sub UpdControl()
    
    thePath = Range("BasePath").Value
    
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    Workbooks("ProjectTranslation.xlsx").Close
    
End Sub

Since this needs to be run every time someone will open the workboook, I made it automatically run on open. I put the following in ThisWorkbook:

Private Sub Workbook_Open()
    
    call Main.UpdControl
    
End Sub

UpdControl works perfectly when launched via the ribbon, everything updates as it should. When I open the workbook, I get the following error:

I'm not sure why I'm getting this error one way and not the other.

A couple things:
1. I'm going to have about 240 copies of this file (one for each "project" in the management system-we're doing extracts) so I don't want the translation table housed in this workbook. The translation table will start small and get added to as we go through future waves of exports. I suppose I *could* internalize it. I just would rather have one central and consistent location where this information is stored.
2. I could have the user use the ribbon button, but they'd have to do so every time the file is opened, not just when we're doing the extracts. So I'd really rather not go that route.
3. Am I even on the right track for this or is there a better way, given that the folder structure is being dictated to me (as complex and as variable as it is)?

I'm definitely open to ideas....even internalizing the translation table, if there's a good reason I haven't considered.

Thanks all for the help, it is much appreciated!

 

[rlv01]

1. Have you tried it using the older auto_open procedure?

Sub auto_open()
    Call Main.UpdControl
End Sub

2. You are opening then immediately closing the file

Sub UpdControl()
    
    thePath = Range("BasePath").Value
    
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    Workbooks("ProjectTranslation.xlsx").Close
    
End Sub

That takes a finite amount of time. Have you tried inserting some delay?

Sub UpdControl()
    Dim thePath
    
    thePath = Range("BasePath").Value
    
    DoEvents
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    Application.Wait Now + TimeValue("0:00:01")
    Workbooks("ProjectTranslation.xlsx").Close
    DoEvents
End Sub

 

[jgottlieb]

1. Have you tried it using the older auto_open procedure?

Sub auto_open()
    Call Main.UpdControl
End Sub

2. You are opening then immediately closing the file

Sub UpdControl()
   
    thePath = Range("BasePath").Value
   
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    Workbooks("ProjectTranslation.xlsx").Close
   
End Sub

That takes a finite amount of time. Have you tried inserting some delay?

Sub UpdControl()
    Dim thePath
   
    thePath = Range("BasePath").Value
   
    DoEvents
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    Application.Wait Now + TimeValue("0:00:01")
    Workbooks("ProjectTranslation.xlsx").Close
    DoEvents
End Sub

1. I had not tried that before your reply. I've replaced Workbook_Open with Autpen() in and it wasn't called when I opened the workbook.
2. Yes, in doing so, the System Field and Application Field cells autoupdate, that's all I need here. I have not tried that. But why does it work when I launch UpdControl via a button, but not when I open the workbook?
3. I added a wait of 1 second. I get the key error on opening the file, but not when I use the button to launch the code.

Thanks for your continued conversation.

 

[rlv01]

1. I had not tried that before your reply. I've replaced Workbook_Open with Autpen() in and it wasn't called when I opened the workbook.

Be sure you locate auto_open in a standard code module, not the Workbook code module.

2. Yes, in doing so, the System Field and Application Field cells autoupdate, that's all I need here. I have not tried that. But why does it work when I launch UpdControl via a button, but not when I open the workbook?

I'm not considering the "whys", only suggesting things to try that have worked for me in the past for somewhat similar situations.

Another suggestion. If you interrupt the operation of UpdControl as a test, does it then work via the workbook open event?

Sub UpdControl()
 
    msgbox "Start"
    thePath = Range("BasePath").Value
    msgbox "Line 1 executed"
    Workbooks.Open thePath & "Master\Utils\ProjectTranslation.xlsx"
    msgbox "Line 2 executed"
    Workbooks("ProjectTranslation.xlsx").Close
     msgbox "Line 3 executed"
End Sub

does it work then?

 

[jgottlieb]

Another suggestion. If you interrupt the operation of UpdControl as a test, does it then work via the workbook open event?

It opens. The error shows up on the close.

I just tried something else that seems to have worked:
Application.EnableEvents = False

And while this may be a sledgehammer approach... I still don't understand why I'm getting the error in the first place....

 

[rlv01]

It opens. The error shows up on the close.

If you don't need it to save, then I would recommend trying this change:

  Workbooks("ProjectTranslation.xlsx").Close SaveChanges:=False

Also, what if you were to 'harden' your worksheet open code against common things that can go wrong?

Private Sub Workbook_Open()
    Dim WB As Workbook
    Dim fname As String
    Dim thePath As String
    
    On Error Resume Next
    thePath = Range("BasePath").Value & "Master\Utils\"
    On Error GoTo 0
    
    If thePath <> "" Then
        With CreateObject("Scripting.FileSystemObject")
            If .FolderExists(thePath) Then
                fname = thePath & "ProjectTranslation.xlsx"
                
                If .FileExists(fname) Then
                    Set WB = Workbooks.Open(Filename:=fname)
                    If Not WB Is Nothing Then
                        Application.Wait Now + TimeValue("0:00:02")
                        WB.Close SaveChanges:=False
                        Application.Wait Now + TimeValue("0:00:01")
                    Else
                        MsgBox "Error - Cannot open workbook" & vbCr & vbCr & "'" & fname & "'"
                    End If
                Else
                    MsgBox "File '" & fname & "'" & vbCr & vbCr & "does not exist", , "File not found"
                End If
            Else
                MsgBox "Folder '" & thePath & "' does not exist", , "Folder not found"
            End If
        End With
    Else
        MsgBox "Invald named range 'BasePath'", , "Invalid Range"
    End If
End Sub

 

[jgottlieb]

That was a good idea and I've implemented that, thanks. I also added a check to see if the file was already opened.