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))
                'show spreadsheet
                ex.Visible = True
                'enable workbook
            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
        End If

        Dim dirs As DirectoryInfo() = Directory.GetDirectories
        Dim d As DirectoryInfo

        'Recurse subfolders
        For Each d In dirs
    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
        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.

8 Years
Discussion Span
Last Post by TomW

Is this being run on a different computer then what it was created? Reference problems like this are usually a case of having a different version of Excel (or no version) then which it was created on.

With that said, it seems you are simply listing file directories into excel. You could instead have this info fill a dataset/datatable and output it as an Excel file using OleDb without the need of automating Excel or even requiring the user computers to have Excel.


"You could instead have this info fill a dataset/datatable and output it as an Excel file using OleDb without the need of automating Excel or even requiring the user computers to have Excel."

That's really what I was originally planning on doing, but couldn't ever figure out how to.


Its pretty easy, your basically just making a different connection string that attaches to the file rather then to a database. Plenty of examples online showing how to do it. If your unable to figure it out, let me know and I can provide more details

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.