Solved

VB.Net and Excel Option Strict Disallows Late Binding

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

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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now