Excel cannot access 'ExcelVB'

[Sebastian H]

What does Excel mean by 'ExcelVB'?

Background: I had split up an existing .xlam add-in, and I did that similar to the way described at https://trumpexcel.com/excel-add-in/. But now, whenever I open Excel, it gives me two consecutive “Exclamation” messages:

Excel cannot access 'ExcelVB'. The document may be read-only or encrypted.
101070

'ExcelVB' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only.
100101

Also, when I open a spreadsheet that contains a function defined in that module and recalculate the cell that contains it, it displays "#NAME?", while the function name is prepended with "'<absolute path to xlam>'!", where the absolute path is C:\Users\<my_name>\AppData\Roaming\Microsoft\AddIns. I had added a reference to that path to the spreadsheet (in VBA editor) and saved the file explicitly (from the VBA editor), but it is not displayed anymore on reopening.

[SamT]

"ExcelVB" sounds like the name of a Procedure, Module or Workbook.

From the error messages, Excel thinks it is the name of a Workbook.


When creating the code for the Add-In, first create it in a regular workbook similar to the books that will be using the Add-in, Then test the code thoroughly by using Functions in Formulas and running Subs from the Macros Menu. After you are certain that the code works as desired, Drag the Module to an empty Workbook and save that Book as an XLSM

[Sebastian H]

Thank you for your valuable replies.

"ExcelVB" sounds like the name of a Procedure, Module or Workbook. From the error messages, Excel thinks it is the name of a Workbook.

I agree. But it's not a name I ever used; nor could I find that name in any recent "*.xl*" files. Is there any way to see where in the world Excel sees that name?

... test the code thoroughly ...

You're right; I didn't do that just now. It was thoroughly tested before, and I planned to test it after the split. I didn't see a need to test it during the split, as I didn't foresee any such problems. Well, hindsight is 20/20.

But now my problem is how to get rid of those error messages that appear before I do anything with the code. If I want to follow your advice, I first need a clean system again.

[Paul_Hossler]

Background: I had split up an existing .xlam add-in, and I did that similar to the way described at https://trumpexcel.com/excel-add-in/. But now, whenever I open Excel, it gives me two consecutive “Exclamation” messages:

How did you 'split' the .xlam? The link has nothing about spliting, only making, etc.

What I would do is

1. Take the XLSM that I created the XLAM from
2. Divide the code into 2 XLSM's, including any required worksheets, etc
3. Check out everything throughly
4. Save each XLSM as a XLAM
5. Install the XLAM's using Options, Addins

[Sebastian H]

Paul: I created the XLAM years ago, and it has changed a lot since, so your step #1 is not an option. Since there are no worksheets in an XLAM, step 2 is not applicable. Re your step 3, see my reply to SamT
(nice name, BTW: in German, that means “velvet”.)

Your step 5 is particularly helpful, now that I did that, at least I got rid of the problems described in the last paragraph of my OP. Thank you for that.

So, what I did was:

1. Export the module (let me call it “M1” here) that I wanted to split out of the original .xlam.
2. From the original .xlam's autoexec functionality (i.e. basically “Auto_Open()”), remove parts relating to M1.
3. Start a new file, and with it do:
   
   1. Import M1
   2. Add the text I removed in step 3 in a new module (“M0”)
   3. Save as described in the link.
   
   
4. Remove M1 from the old .xlam. ⍝ In hindsight, it might have been safer to do that right in step 1.
5. create a test file that uses some of M1's functions.
6. Add the XLAM to the test file using the IDE's Tools → References.

[Paul_Hossler]

FWIW, you can get a XLSM from a XLAM easily

Capture.JPG

In the VBE properties, select the ThisWorkbook module for the addin, and change IsAddin to False

You might have to make the WB visible, but them File, SaveAs XLSM

I (just my choice) keep a XLSM in my AddIns\Source folder and make all change to that file. Then save as a XLAM

I'm just not comfortable making changed directly in the XLAM

[Sebastian H]

FWIW, you can get a XLSM from a XLAM easily

Oh, thanks, that's good to know.

I (just my choice) keep a XLSM in my AddIns\Source folder and make all change to that file. Then save as a XLAM. I'm just not comfortable making changed directly in the XLAM

Why is it that you're not comfortable with changing XLAM? Because of issues like this? I've changed the XLAM directly for years now, and until now I've never regretted it.

Addition: There's one problem which repeatedly bit me, and which may not occur when working with XLSM: It has no “dirty” flag, which means if you don't explicitly save it, your changes will be lost.

[Paul_Hossler]

Why is it that you're not comfortable with changing XLAM? Because of issues like this? I've changed the XLAM directly for years now, and until now I've never regretted it.

Addition: There's one problem which repeatedly bit me, and which may not occur when working with XLSM: It has no “dirty” flag, which means if you don't explicitly save it, your changes will be lost.

You just answered your own question

[SamT]

I've never worked with an xlam, so this is just some cut and pastes from the help file, your post, and my own Personal.xls. It probably needs a reference to the MS VBA Extensibility dll.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Application.VBE.VBProjects(1).Saved Then _
    Me.SaveCopyAs ("C:\Users\SebastionH\AppData\Roaming\Microsoft\AddIns\Archives\" & Me.Name & " - " & CStr(CDbl(Now)) & ".xlam")
End Sub

The CStr(CDbl(Now)) just puts a unique (to the millisecond) TimeStamp String in the saved File Name

If you manually save the file, that code will not run. In my Personal, I test and Save BeforeClose and SaveCopyAs when Saveing

[Sebastian H]

Thanks, Sam, this should fix the side issue Paul and I were discussing in the last two posts. It should also be a useful element in a fix for the silly problem that an ordinary file can get dirty when you just look at it. But that's off course OT here.

[Sebastian H]

Update: It turns out, “ExcelVB” is a name I used, but not for a “document” or “file”, as the error messages say: It's the name of a folder, namely the parent folder of the folder into which I exported the code of M1 per step 1 in my list of yesterday, 08:53 AM.

Still no indication where Excel gets that reference from, and what it tries to do with that folder that's so important that it warrants two error messages. The folder should be none of Excel's business; it doesn't contain anything that it would need on startup.

I renamed the folder and Excel updates both its error messages. Rebooting didn't help. The only thing I can think of that I haven't tried is reinstalling Excel, but I'd rather understand this mystery.

[Paul_Hossler]

1. Did you check References to see if anything was referencing the folder?

Capture.JPG



or 2. Go back to the beginning and try again

Delete ExcelVB

Take the original XLAM (or XLSM) and export the module and then delete from source

[Sebastian H]

1. Did you check References to see if anything was referencing the folder?

Yes, and I also checked the Add-Ins section in the Options. (To be honest, I didn't check the unselected references until I read your message, since I saw no reason why it would need any of them at startup. But it's not too cumbersome to check them, too, and it's worth a try.)

or 2. Go back to the beginning and try again ... Delete ExcelVB ...

Thanks for the recommendation; that's at least one step less perilous than all-out reinstallation.

[snb]

Check 'connections' and 'Edit Links' (Reiter 'Data')

[SamT]

Microsoft says the 100107 is an Ownership error and you should give yourself full rights to the folder ExcelVB

https://answers.microsoft.com/en-us/...7-f74197208e76

[Sebastian H]

Check 'connections' and 'Edit Links' (Reiter 'Data')

Thank you for the tip. However, it's not working in my case: The first has no connections listed, the second is disabled.

[Sebastian H]

2. Go back to the beginning and try again ... Delete ExcelVB ... Take the original XLAM (or XLSM) and export the module and then delete from source

Thank you – that worked like a charm: I even didn't have to do the XLAM deletion. After deleting the folder, Excel gave me a very nice message to the effect ⸉The item "ExcelVB" has been deleted, however, it can be restored from the recycle bin. Do you want to restore it?⸊, and after that, the problem was gone.

[Micromegas]

Update: This happened again; this time with error messages like

Excel cannot access 'Microsoft'. The document may be read-only or encrypted.

I was afraid that following Paul's advice i would have to delete whole big folders like %appdata%\Microsoft. But it turns out, in this case the culprit was a link (to some other folder named ‘Microsoft’) in %appdata%\Microsoft\Excel. So just removing that link fixed the problem.

BTW, this gives me the idea that it may be possible to place links to whole folders with add-ins in the %appdata%\Microsoft\Excel folder. Might experiment with that later.