Solved

VB.Net and Excel Option Strict Disallows Late Binding

Posted on 2012-03-23
10
1,752 Views
Last Modified: 2012-03-28
I have the following to interop with Excel and get the late binding message at .Columns.ColumnWidth("A:A") = 20. (Option Strict is ON)

What is the correct syntax to work with columns and cells.

					Dim objXL As Excel.Application
					objXL = New Excel.Application
					objXL.Visible = True
					Dim objBooks As Excel.Workbooks()
					Dim objBook As Excel.Workbook
					Dim objSheets As Excel.Sheets()
					Dim objSheet As Excel.Worksheet
					Dim objrange As Excel.Range

					' Get a new workbook.
					With objXL

						.Workbooks.Add()
						objrange = .Range("A1")
						objSheet = CType(.Sheets("sheet1"), Excel.Worksheet)

					End With

					With objSheet

						.Name = "Period Turnover"
						.Columns.ColumnWidth("A:A") = 20

					End With

Open in new window

0
Comment
Question by:TeDeSm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37756965
have you tried:

ActiveSheet.Columns("A:A").ColumnWidth = 20
0
 

Author Comment

by:TeDeSm
ID: 37757179
Tried something like that already without much success. However this appears to work which may give me enough to work with.

					' Get a new workbook.
					With objXL

						.Workbooks.Add()
						With .Range("A1")
							.ColumnWidth = intColWidthProdGroup
						End With
						With .Range("A2")
							.ColumnWidth = intColWidthTotalQty
						End With
						'With .Range("A1")
						'.ColumnWidth = 12
						'End With
						'With .Range("A1")
						'.ColumnWidth = 12
						'End With
						objrange = .Range("A1")
						objrange = objrange.Resize(1, 5)
						objSheet = CType(.Sheets("sheet1"), Excel.Worksheet)
						objrange.Activate()

					End With

					With objSheet

						.Name = "Period Turnover"
						.Cells(1, 1) = "Product Code"
						.Cells(1, 2) = "Total Qty"

					End With

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 37760090
You just have a syntax error.

Note the syntax should be:

.Columns("A").ColumnWidth = 20

I reprised your code in a quick VB.Net exercise and this works:

Module Module1
    Sub doExcelStuff()
        Dim objXL As Object

        Dim objBooks As Object
        Dim objBook As Object
        Dim objSheets As Object
        Dim objSheet As Object
        Dim objrange As Object

        objXL = CreateObject("Excel.Application")
        objXL.Visible = True
        ' Get a new workbook.
        With objXL

            .Workbooks.Add()
            objrange = .Range("A1")
            objSheet = .sheets("Sheet1")

        End With

        With objSheet

            .Name = "Period Turnover"
            .Columns("A").columnwidth = 20

        End With
    End Sub
End Module

Open in new window


Once you get to the object level - workbook, sheet, or range/cell, the property/method syntax for the object is pretty much the same as VBA as far as the object properties/methods are concerned, because you are working with the same object.

So if you're finding something is not working, try it in VBA first (at least that's what I do, lol as that's where I came from).

Cheers,

Dave
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:TeDeSm
ID: 37765671
Thanks Dave,

Still get the 'Option Explicit disallows late binding'. Life would be much easier with Option Strict off, however early binding should allow full functionality, it does not. I guess the question is; How to code Excel Early Binding correctly??

I come from VBA as well, did a heck of a lot more with automation from Access to Excel.
0
 

Author Comment

by:TeDeSm
ID: 37766647
It would appear that all Excel objects should be explicitly defined in the forms class before use. I have not tested the below yet but I am not getting that dreaded 'Option Explicit disallows...' message. Looking forward to testing tomorrow and will post back with results.

Public Class frmName

	' Variables to interop with Excel, Early Binding employed
	Dim objExcel As New Excel.Application
	Dim objBooks As Excel.Workbooks = CType(objExcel.Workbooks.Add, Excel.Workbooks)
	Dim objBook As New Excel.Workbook

	Dim objSheets As Excel.Sheets = CType(objExcel.Workbooks, Excel.Sheets)
	Dim objSheet As Excel.Worksheet = CType(objBook.Worksheets(1), Excel.Worksheet)
	Dim objSheet2 As Excel.Worksheet = CType(objBook.Worksheets(2), Excel.Worksheet)

	Dim objRange1 As Excel.Range = CType(objBook.Worksheets(1), Excel.Range)
	Dim objRange2 As Excel.Range = CType(objBook.Worksheets(2), Excel.Range)
	Dim objChart As Excel.Chart = CType(objBook.Worksheets(2), Excel.Chart)

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 37766658
Sorry - my original response was dealing with the syntax error you had in originally line 22.

I'm taking a look at your declarations now.

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37766867
>>Still get the 'Option Explicit disallows late binding'. Life would be much easier with Option Strict off, however early binding should allow full functionality, it does not. I guess the question is; How to code Excel Early Binding correctly??

With Option Explicit ON and Option Strict On, the code should be as follows (ensure you add a reference to the MS Excel Object Library to your project).

Note, you have to cast the columns property of the sheet to an Excel.Range

Imports Microsoft.Office.Interop
Module Module1
    Sub doExcelStuff()
        Dim objXL As Excel.Application
        Dim objBooks As Excel.Workbooks
        Dim objBook As Excel.Workbook
        Dim objSheets As Excel.Worksheets
        Dim objSheet As Excel.Worksheet
        Dim objrange As Excel.Range

        objXL = CType(CreateObject("Excel.Application"), Excel.Application)
        objXL.Visible = True
        ' Get a new workbook.
        With objXL

            .Workbooks.Add()
            objrange = .Range("A1")
            objSheet = CType(.Sheets("Sheet1"), Excel.Worksheet)

        End With

        With objSheet

            .Name = "Period Turnover"
            CType(.Columns("A"), Excel.Range).ColumnWidth = 20
        End With

    End Sub
End Module

Open in new window



PS See my initial declarations for early binding and there's no need to do any additional casting.  This works just fine with Option Explicit/Option Strict On settings.

Cheers,

Dave
0
 

Author Comment

by:TeDeSm
ID: 37771088
Thanks Dave,

Just the ticket. I have some queries regarding the casting syntax for:
            Dim objBooks As Excel.Workbooks
            Dim objBook As Excel.Workbook
            Dim objSheets As Excel.Worksheets
            Dim objChart As Excel.Chart
The chart will need to go into the second sheet named 'Chart' which I have already added whilst the source data is in a range on the first sheet in objRange1. I'm guessing the code is a bit like the below:

							
objChart = CType(objExcel.Charts.Add, Excel.Chart)
objRange1 = .Range("A1:A10")
With objChart
   .Name = "Turnover Chart"
   .SetSourceData(Source:=objSheet1.Range("A1:A10"))
   .ChartType = Excel.XlChartType.xlLine
   .Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Chart")
End With

Open in new window

0
 

Author Comment

by:TeDeSm
ID: 37771465
Got the following for the chart which is working:
With objSheet2
  .Name = "Chart"
End With

objChart = CType(objExcel.Charts.Add, Excel.Chart)
objRange1 = .Range("E2:E4")

With objChart

  .Name = "Turnover Chart"
  .SetSourceData(Source:=objRange1, PlotBy:=Excel.XlRowCol.xlColumns)
  .ChartType = Excel.XlChartType.xlCylinderColStacked
  .Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Chart")

End With

Open in new window

0
 

Author Closing Comment

by:TeDeSm
ID: 37776880
Thanks for the syntax correction, works OK now.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question