Solved

VB.Net and Excel Option Strict Disallows Late Binding

Posted on 2012-03-23
10
1,672 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 41

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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 41

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 41

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

776 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