none
Out of Memory exception when reading Workbook.VBProject.References RRS feed

  • Question

  • Hi,

    We have an application written in VB.NET that will merge two XLSM workbooks into a singe XLSM workbook.

    Approach is that we open both source and target workbooks and get each sheet of the source workbook and insert it into the target workbook. This is done for all the workbooks that are needed to be merged into the target workbook. 

    After the merging is done we programatically add reference of our custom add-in (.XLA) to the target workbook. 

    All goes well till we try to insert a reference of our custom addin (.XLA). To add the addin we first check whether the addin is already available in the target workbook (part of a business rule). So, we iterate through each reference and compare the name against our add-in name. At the point where we try to get the references of the excel it throws out of memory exception, this happens even if we do not have our add in reference and just out of the box Excel reference whose count is only 4. 

    I receive the exception at the below point in our code. 

    Workbook.VBProject.References

    This works fine if we have .xls (93-2007) format of both source and target workbooks and also if the source XLSM workbook doesn't contain any macro.

    Any pointers what could be the issue?

    P.S - We use late binding for all excel manipulations in the above application. 

    Friday, August 28, 2015 8:03 AM

All replies

  • Can you use a template file that has all the required references already, then add the sheets from both workbooks to that workbook?
    Friday, August 28, 2015 2:22 PM
  • Hi

    Thanks for the reply. The code to fetch the references throws out of memory exception before actually adding the reference. I think the file type should not matter but i will give it a try. Another observation i made is that it works fine if i use Interops than late binding. Any idea what could be the issue in late binding?

    Monday, August 31, 2015 6:18 AM
  • I take "93-2007" is a typo and you mean source and target workbooks are both 97-2003 format, except you start by saying both workbooks are XLSM's which means both are 2007+ or OpenXML format, could you clarify.

    If you are checking for the XLA reference and if missing adding it, it sounds like the workbook you've copied from might have VBA code that needs the XLA reference. It also sounds like the now main workbook may or may not contain code that references the XLA. How are you managing all this and avoiding copying potential duplicate procedures.

    Once you can refer to the References collection might be an idea to add the XLA reference as necessary first, before adding code that depends on it to avoid temporary compile errors.



    Monday, August 31, 2015 12:17 PM
    Moderator
  • Hi,

    sorry for the confusion, yea all the files are open xml macro enabled formats. 

    if any of the source xlsm files has a macro or VBA code then i receive that error. The source file that i'm trying to merge contains the below VBA code. 

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lobj As Names
    Set lobj = ActiveSheet.Names
    
    End Sub
    

    I think the above VBA code will work if we have the default excel object library and this reference will be available by default, correct me if i'm wrong. 

    why does excel throw error when a simple call to VBProject.References is made? this works if we are using interops.

    Monday, August 31, 2015 12:51 PM