• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicated worksheet objects causing Automation error

R

ravikiran

Member
Hi Excel Gurus,

I have a weird problem (I never faced it until recent) with one of my excel workbook. Actually occurred multiple times when I worked on copies from history. I am working on some updates to an Excel Workbook with lots of Worksheets, tables and VBA code. The changes involved are pretty small. Add few additional rows and amend or add new Named ranges.

Whenever I save mid of these changes, close and re-open the workbook, I get the following error:
upload_2018-2-8_8-29-6.png

The code is unable to point to a specific named range from the worksheet "aDATA". When I try to investigate what happened, I noticed that all the worksheets in the VBA Project > Microsoft Excel Objects are duplicated with "1" amended at the end. Screenshot below:
upload_2018-2-8_8-24-0.png

If I try to stop the code the following compile error is displayed:
upload_2018-2-8_8-33-28.png

  • Does this mean that all the existing worksheet objects are changed into a different excel object?
  • Is this caused due to a repair of the Excel file? The file definitely didn't crash.
  • If so how do I stop this?
  • If not, what might be the source of this problem?
  • Did this happen due to any new Excel service pack updates or lack of new updates?
  • And how do I stop this from occurring?
I have a fear that this might occur with other excel files working for the same application.

Your help and inputs with this problem will be greatly appreciated.

Cheers,
Ravi.
 
It's hard to pinpoint cause without full project scope along with sample workbook.

But from what I see in your screenshot, those sheets that show only sheet code name (Sheet4 etc), are not worksheet object. Worksheet object will always have sheet code name (SheetName) pair.

I assume there's some operation within your code that caused this.
 
After much searching, the easiest solution I could find was at:

https://www.experts-exchange.com/qu...rroneous-worksheets-code-from-VBA-Editor.html
(you have to wait 20 second for solution to appear on this site unless you buy a membership)
​
The solution is:

  1. Export all VBA Modules from the Workbook
    • In the VBA editorRight click on each module and select ‘Export File’
    • Save module locally
    • Repeat for each module
  2. Save corrupt excel file as .xlsx
  3. Close workbook/Excel
  4. Re-open new .xlsx file
  5. In the VBA Editor, Import each VBA module back to the file
    • Right click on ‘Microsoft Excel Objects’ and select ‘Import File’
  6. Save file as .xlsm/.xlsb
  7. Save/Close/Reopen to verify

Does anyone know why this occurs? I have had it happen to multiple workbooks that get saved/re-versioned over time and/or have a lot of VBA code.
 
You must log in or register to reply here.
Back
Top