Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.Net and Excel Option Strict Disallows Late Binding

Posted on 2012-03-23
10
Medium Priority
?
1,861 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

971 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