Hello,
i have an excel-file whith 2 sheets.
There are much columns, but I need only a few of them.
I want to delete some columns by name.
I found this code but, I don´t know why it doesn´t work!?!?
It tells me ".UsedRange" is wrong.
Can you help me, please?
(Reference "Microsoft Excel 16.0 Object Library" is already added in my project)
Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim Excel As Microsoft.Office.Interop.Excel.Application
Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
Dim xlWSTabelle1 As Excel.Worksheet = CType(CType(xlWB.Sheets("Tabelle1"), Excel.Worksheet), Excel.Worksheet)
Dim xlWSTabelle2 As Excel.Worksheet = CType(CType(xlWB.Sheets("Tabelle2"), Excel.Worksheet), Excel.Worksheet)
Dim Path = "D:\Test.xlsx"
Excel = New Microsoft.Office.Interop.Excel.Application
xlWB = Excel.Workbooks.Open(Path)
Dim xlSheets As Object
Dim xlSheetsArray(0 To 1) As Excel.Worksheet
Dim k As Long
Dim i As Long
xlSheetsArray(0) = xlWSTabelle1
xlSheetsArray(1) = xlWSTabelle2
For Each xlSheets In xlSheetsArray
With xlSheets
k = .UsedRange.Columns.Count
For i = k To 1 Step -1
Select Case LCase(.UsedRange.Cells(1, i).Value)
'Keep these columns
Case "#Num", "Product A", "Number 1" '...
Case Else
'Delete all others not listed above
.UsedRange.Columns(i).Delete()
End Select
Next i
End With
Next xlSheets
End Sub
End Class
