First off, let me be honest in saying that this is homework. I am not asking for it to be done for me, merely some help in figuring out what I have messed up. Secondly, I am not good with VB, nor do I particularly care for it, but seeing as it is one of the languages being utilized in my Comparative Programming Languages class, I have one final assignment to complete with VB. I cannot get this fundamental portion to work correctly to finish this assignment. This is what I have for code so far:
Imports Microsoft.Office.Interop Imports System.IO Public Class Form1 'declare workbook variable Dim wb As Excel.Windows Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonListGenerator.Click Dim ex As New Excel.Application wb = ex.Workbooks.Add 'confirm/deny existance of directory If Directory.Exists(Me.textPath.Text) Then 'prompt user of where to save file Dim dialog As New SaveFileDialog Dim result As DialogResult = dialog.ShowDialog 'do this if user clicks 'cancel' If result = Windows.Forms.DialogResult.OK Then 'build folder structure Me.BuildFolder(New DirectoryInfo(Me.textPath.Text)) wb.SaveAs(dialog.FileName) 'show spreadsheet ex.Visible = True 'enable workbook wb.Activate() End If End If End Sub 'function that creates a worksheet with file names Protected Sub BuildFolder(ByVal Directory As DirectoryInfo) 'confirm/deny existance of files in directory If Directory.GetFiles.Length > 0 Then 'create new sheet and set its name in directory Dim sheet As Excel.Worksheet = wb.Worksheets.Add sheet.Name = CheckName(Directory.Name) Dim files As FileInfo() = Directory.GetFiles Dim f As FileInfo 'create int used to navigate cells & set its value to 1 Dim i As Integer = 1 For Each f In files 'write the file name to the cell sheet.Cells(i, 1) = f.Name i += 1 Next End If Dim dirs As DirectoryInfo() = Directory.GetDirectories Dim d As DirectoryInfo 'Recurse subfolders For Each d In dirs BuildFolder(d) Next End Sub 'function that confirms/denies prior existance of sheet in book Protected Function CheckName(ByVal Name As String) As String Dim s As Excel.Worksheet Dim exists As Boolean = False For Each s In wb.Worksheets If UCase(s.Name) = UCase(Name) Then 'append a 1 to name's end & recheck Name = CheckName(Name & "(1)") End If Next Return Name End Function End Class
Line 1 (
) says that it cannot be found.
Upon attempting to debug, and clicking the button, it tells me, "InvalidCastException was unhandled" for the line
wb = ex.Workbooks.Add
Someone, PLEASE help me suffer through this so that I can focus on getting better with the languages I actually enjoy. Thanks in advance for any tips you have.