rsmith rsmith - 4 months ago 25
Vb.net Question

Error "Select method of Range class failed" when selecting range in VB.Net Excel workbook

(First time poster, so please let me know if I did anything wrong with the question formatting/placement/content. Thanks)

Brief intro: I'm working on an Excel workbook that was developed by someone else at my company in Visual Studio 2010 for Excel 2007/2010. I'm now coming back with Visual Studio 2012 to add functionality to be used in Excel 2016.

Problem: At some point in the program, the code is supposed to open up a new Excel workbook and insert some new data in it. That all works fine, but I whenever I try to select a specific cell in that new workbook (Ex. wksht.Range("A1").Select), I get the error "Select method of Range class failed". I am activating the worksheet before trying to select a range in the new workbook. I know that selecting ranges should be avoided when possible, but this is old code from the previous developer that I am trying to leave intact. The interesting thing about this is that it works perfectly when I open the workbook in Excel 2010. The issue only comes up when I try to do the exact same thing with the exact same code in Excel 2016. We just now updated to 2016, so this is why we never encountered this error before. Is there a different way for selecting a range in Excel 2016 with VB.Net than is used for previous versions?

Error Message:

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x800A03EC): Select method of Range class failed
[location of line where error occurred, edited to preserve employer's property]

************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1076.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Office.Runtime
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Office.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Office.Runtime.dll
----------------------------------------
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1075.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1067.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Security
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Security/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Security.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.Hosting
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Hosting/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Hosting.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.Runtime
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Runtime.dll
----------------------------------------
System.Deployment
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Deployment/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Deployment.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.ServerDocument
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.ServerDocument/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.ServerDocument.dll
----------------------------------------
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1068.2 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Xml.Linq
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll
----------------------------------------
Microsoft.Office.Tools
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.dll
----------------------------------------
Microsoft.Office.Tools.Excel.Implementation
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Excel.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Excel.Implementation.dll
----------------------------------------
Microsoft.Office.Tools.Common.Implementation
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.Implementation.dll
----------------------------------------
WindowsBase
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/WindowsBase/v4.0_4.0.0.0__31bf3856ad364e35/WindowsBase.dll
----------------------------------------
PresentationFramework
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1080.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/PresentationFramework/v4.0_4.0.0.0__31bf3856ad364e35/PresentationFramework.dll
----------------------------------------
PresentationCore
Assembly Version: 4.0.0.0
Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/PresentationCore/v4.0_4.0.0.0__31bf3856ad364e35/PresentationCore.dll
----------------------------------------
Microsoft.Office.Tools.Excel
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Excel/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Excel.dll
----------------------------------------
Microsoft.Office.Tools.Common
Assembly Version: 10.0.0.0
Win32 Version: 10.0.60301.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.dll
----------------------------------------
Microsoft.Office.Tools.Excel.v4.0.Utilities
Assembly Version: 10.0.0.0
Win32 Version: 10.0.30319.1
CodeBase: file:///C:/Users/[yo my name]/Documents/Test%20Bed/Microsoft.Office.Tools.Excel.v4.0.Utilities.DLL
----------------------------------------
Microsoft.VisualBasic
Assembly Version: 10.0.0.0
Win32 Version: 14.6.1055.0 built by: NETFXREL2
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll


Code w/ problem (wksht is fed as Excel.Worksheet to this function):

'AUTO SIZE ROWS
wksht.Activate()
For iRow = valRowDataStart To (valRowDataStart + iValidCount)
wksht.Range("A" & iRow).EntireRow.Select()

wksht.Range(valColLocation & iRow).WrapText = True
wksht.Range(valColLoadsControlled & iRow).WrapText = True

wksht.Range("A" & iRow).EntireRow.AutoFit()
wksht.Range("A" & iRow).RowHeight = wksht.Range("A" & iRow).RowHeight + 5
Next iRow


Like I said earlier, this is my first time posting a question, so please let me know if I did anything wrong or if you just need more information. Thank you so much for you help!

Answer

Try wksht.Application.Goto(wksht.Range("A1"), True) instead

Comments