Visual Studio 2015 - Manipulating Excel?
I have 750 Excel files that I want
- clear by deleting data columns that have a header with an asterisk,
- then take some of that data and put it in a new worksheet, and other data in one sheet of the workbook and some other data in a second new workbook.
I created a WPF project in Visual Studio 2015 with a small dialog box with two radio buttons for
- clean data,
- create new files.
This is my VB code:
Class MainWindow
Dim wb As Microsoft.Office.Interop.Excel._Workbook
Dim ws As Microsoft.Office.Interop.Excel._Worksheet
Dim iCol As Integer
Dim strName As String
Dim iIndex As Integer
Dim strPath As String
Dim strFile As String
Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
If cleanRadioButton.IsChecked = True Then
strPath = "c:\test\old\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
wb = wb.Open(Filename:=strPath & strFile)
'Loop through the sheets.
For iIndex = 1 To Application.Worksheets.Count
ws = Application.Worksheets(iIndex)
'Loop through the columns.
For iCol = 1 To ws.UsedRange.Columns.Count
'Check row 1 of this column for the char of *
If InStr(ws.Cells(10, iCol).Value, "*") > 0 Then
'We have found a column with the char of *
ws.Columns(iCol).EntireColumn.Delete
ws.Columns(iCol + 1).EntireColumn.Delete
ws.Columns(iCol + 2).EntireColumn.Delete
End If
Next iCol
Next iIndex
wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=xlOpenXMLWorkbook)
wb.Close(SaveChanges:=False)
strFile = Dir()
Loop
MessageBox.Show("The csv files have now been cleaned. Congrats.")
Else inputRadioButton.IsChecked = True
MessageBox.Show("The data has now been split into Trajectory and ForcePlate input files. High 5.")
End If
End Sub
End Class
I am getting 3 errors but cannot figure out how to solve them .
a) Worksheets are not members of the application [line 19]
b) Worksheets are not members of the annex [line 20]
c) 'xlOpenXMLWorkbook' is not declared. It may be unavailable due to its security level.
source to share
For a) and b) the template looks like:
Application.Workbooks.Worksheets
For c) the simplest way out is:
Go to VBE from Excel (Alt + F11)
Press F2 to display the Object Browser
Look for xlOpenXMLWorkbook
Result: Const xlOpenXMLWorkbook = 51 (&H33)
So, just replace it with 51!
Here is your modified code:
Class MainWindow
Dim wb As Microsoft.Office.Interop.Excel._Workbook
Dim ws As Microsoft.Office.Interop.Excel._Worksheet
Dim iCol As Integer
Dim strName As String
Dim iIndex As Integer
Dim wbIndex As Integer
Dim strPath As String
Dim strFile As String
Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
If cleanRadioButton.IsChecked = True Then
strPath = "c:\test\old\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
wb = wb.Open(Filename:=strPath & strFile)
'Loop through the sheets.
For wbIndex = 1 To Application.Workbooks.Count
For iIndex = 1 To Application.Workbooks(wbIndex).Worksheets.Count
Ws = Application.Workbooks(wbIndex).Worksheets(iIndex)
'Loop through the columns.
For iCol = 1 To Ws.UsedRange.Columns.Count
'Check row 1 of this column for the char of *
If InStr(Ws.Cells(10, iCol).Value, "*") > 0 Then
'We have found a column with the char of *
Ws.Columns(iCol).EntireColumn.Delete
Ws.Columns(iCol + 1).EntireColumn.Delete
Ws.Columns(iCol + 2).EntireColumn.Delete
End If
Next iCol
Next iIndex
Next wbIndex
'Const xlOpenXMLWorkbook = 51 (&H33)
wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=51)
wb.Close(SaveChanges:=False)
strFile = Dir()
Loop
MessageBox.Show ("The csv files have now been cleaned. Congrats.")
Else: inputRadioButton.IsChecked = True
MessageBox.Show ("The data has now been split into Trajectory and ForcePlate input files. High 5.")
End If
End Sub
End Class
source to share
For reference worksheet yau can use either ws = wb.Worksheets(1)
or ws = wb.Worksheets("Sheet1")
or ws = excelApp.ActiveWorkbook.Worksheets(1)
and used xlOpenXMLWorkbook
as the name of the Enum XlFileFormat
: XlFileFormat.xlOpenXMLWorkbook
.
This simplified example opens the Test.xlsx workbook, writes the text to cell A1, and saves it in a new folder.
Imports System.IO
Imports Microsoft.Office.Interop.Excel
Public Class MainWindow
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim excelApp As Application
Dim wb As _Workbook
Dim ws As _Worksheet
Dim rng As Range
Dim strPathOld = "c:\temp\old"
Dim strPathNew = "c:\temp\new"
' get excel application reference
excelApp = New Application
excelApp.Visible = True
excelApp.ScreenUpdating = True
' open the workbook
wb = excelApp.Workbooks.Open(Path.Combine(strPathOld, "Test.xlsx"))
' set reference to the sheet with index 1
ws = wb.Worksheets(1)
' or use sheet name
' ws = wb.Worksheets("Sheet1")
' or use ActiveWorkbook if it exists
' ws = excelApp.ActiveWorkbook.Worksheets(1)
' write text in cell A1
rng = ws.Range("A1")
rng.Formula = "Test123"
' save the workbook in new location
wb.SaveAs(Filename:=Path.Combine(strPathNew, wb.Name), _
FileFormat:=XlFileFormat.xlOpenXMLWorkbook)
excelApp.Quit()
End Sub
End Class
Note: add a link to MS Office Interop for your version of Excel (here is an example for Excel 2007).
source to share