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 (
Imports Microsoft.Office.Interop
) 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.