3

Following code works well.

Option Strict Off
Imports Microsoft.Office.Interop

Public Class Form1

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    Dim xlApp As Excel.Application = Nothing
    xlApp = CType(CreateObject("Excel.Application"), Excel.Application)

    xlApp.WindowState = Excel.XlWindowState.xlMinimized

    Dim wb1 As Excel.Workbook
    wb1 = xlApp.Workbooks.Open("C:\MyFolder\Book1.xlsx")

    With CType(wb1.Sheets(1), Excel.Worksheet)
        .Columns(4).Cut()
        .Columns(2).Insert()
    End With

End Sub

End Class

When I turn Option Strict on then error happens. Thanks in advance. Have a nice day.

  • The answer provided will resolve the error. Just a slight note (not major) but rather than using `CreateObject` you can use `Dim xlApp As New Excel.Application` – Bugs Jan 11 '17 at 12:20
  • Is there any differences? –  Jan 11 '17 at 12:23
  • To be honest, I don't actually think there is with how you have coded. Excel is a _COM_ object and so `CreateObject` will work however you can reference to it directly using `Excel.Application` so thinking it's cleaner and can all be on one line. – Bugs Jan 11 '17 at 12:26
  • http://stackoverflow.com/questions/170070/what-are-the-differences-between-using-the-new-keyword-and-calling-createobject (in VB.NET i'd also prevfer `New`) – Tim Schmelter Jan 11 '17 at 12:28
  • @TimSchmelter just found that myself (along with [this](http://stackoverflow.com/questions/25951997/equivalent-code-of-createobject-in-vb-net)). Do you know of any actual differences? Or is it just the binding? – Bugs Jan 11 '17 at 12:29
  • 1
    @Jinx88909: No, i don't know the differences but i'm not an expert since i haven't used office-interop much. I prefer EPPlus to create excel files – Tim Schmelter Jan 11 '17 at 12:31
  • What is EPPlus? –  Jan 11 '17 at 12:34
  • @TimSchmelter only thing I can think of is backwards compatibility since VB.NET allows to reference `Excel.Application` whereas VB6 reference to `Object` and then used `CreateObject("Excel.Application")`. – Bugs Jan 11 '17 at 12:35
  • 1
    @MichaelScofield: an excel library that doesn't require an office license, so you can generate or process excel files on a server without having a license – Tim Schmelter Jan 11 '17 at 12:35

1 Answers1

2

Since you're using Office-Interop all objects are of type Object, so you need to cast them:

Dim workSheet = CType(wb1.Sheets(1), Excel.Worksheet)
Dim column4Range = CType(workSheet.Columns(4), Excel.Range)
Dim column2Range = CType(workSheet.Columns(2), Excel.Range)
column4Range.Cut()
column2Range.Insert()

(you notice that i prefer variables over the With statement)

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939