VB.Net and Excel Option Strict Disallows Late Binding

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

TeDeSmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
have you tried:

ActiveSheet.Columns("A:A").ColumnWidth = 20
0
TeDeSmAuthor Commented:
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
dlmilleCommented:
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
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

TeDeSmAuthor Commented:
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
TeDeSmAuthor Commented:
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
dlmilleCommented:
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
dlmilleCommented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TeDeSmAuthor Commented:
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
TeDeSmAuthor Commented:
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
TeDeSmAuthor Commented:
Thanks for the syntax correction, works OK now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.