Open file in a specific directory

Question

I am brand new to Visual Basic and am a little lost. I was trying to import what I thought was correct to be able to use classes that seem to pop up when using VBA macro functions. Right now my code is not recognizing Workbooks.

My main goal is to simply use a Visual Studio Windows Form in Visual Basic to open an excel file in a specific directory using a name that is typed in the text box, and save-as into a .txt file. It seems like I am missing a major import or COM reference. Any help?

Imports Microsoft.Office.Interop
Imports Microsoft.VisualBasic


Public Class Form1

Dim path As String = "C:\Users\Dustin\Desktop\"
Dim filename1 As String

Private Sub txtBoxExcelFileNameString_TextChanged(sender As Object, e As EventArgs) Handles txtBoxExcelFileNameString.TextChanged
    filename1 = txtBoxExcelFileNameString.Text

End Sub

Private Sub btnExcelSaveAs_Click(sender As Object, e As EventArgs) Handles btnExcelSaveAs.Click

    Workbooks.Open Filename:=path & filename1

End Sub
End Class

You could add `Imports Microsoft.Office.Interop.Excel`, but that's just the very beginning. Follow the sequence [shown here](https://stackoverflow.com/questions/50389311/excel-still-running-after-close-vb-net-app?answertab=active#tab-top) to open a WorkBook, access a Sheet and release the objects you have created when you're done with them. — Jimi, May 21 '18 at 01:43
Possible duplicate of [Open Excel workbook](https://stackoverflow.com/questions/18516790/open-workbook-and-select-worksheet-from-winform) — Trevor, May 21 '18 at 02:08
@Codexer, I disagree with you. `Open file in a specific directory` doesn't reflect the problem in the question. Using tag name in the question title is not _inherently_ bad. Actually, some questions can't be asked without a tag word in their title, like [the most upvoted VB.NET question](https://stackoverflow.com/q/4660142/4934172) or [Jon Skeet's most upvoted question](https://stackoverflow.com/q/247621/4934172). — 41686d6564 stands w. Palestine, May 21 '18 at 03:00
@AhmedAbdelhameed The most upvoted VB.NET question you refer to was [created](https://stackoverflow.com/posts/4660142/revisions?page=2) (Jan 11 '11) *before* the [tag](https://stackoverflow.com/posts/7778799/revisions) (Oct 15 '11). — Andrew Morton, May 21 '18 at 09:37
@AndrewMorton Um, and Jon Skeet's question was created before the C# tag? That's not the point, these are just examples. The point is as I said above, using tags in the question titles isn't _inherently_ bad. It's bad when it's unnecessary and only used to "tag" the question. My intention wasn't to turn this into a debate, I was just surprised by the edit summary about something _I thought_ was obvious. — 41686d6564 stands w. Palestine, May 21 '18 at 09:49
@AhmedAbdelhameed I did check Jon Skeet's question, just in case :) Anyway, I suspect the question is asking for a translation from VBA-style (`Workbooks.Open Filename:=path & filename1`) to VB.NET, not how to open a file. — Andrew Morton, May 21 '18 at 10:00

score 1 · Accepted Answer · answered May 21 '18 at 10:34

There is a little bit of code needed in addition to what you might hope would be a simple operation of opening an Excel file using a .NET program, which is explained at The proper way to dispose Excel com object using VB.NET? I've used that in the code below.

The Office Interop stuff can be tedious to get the correct variable types for - you can get Visual Studio to help you by using Option Strict On and Option Infer On. (I have them set as defaults for new projects, so I wouldn't normally have them showing in the code.)

When you're putting filenames and paths together, it is easy to get the \s in a muddle, so Path.Combine can be used so that you don't have to worry about leaving, or not, a trailing directory separator in.

There's no need to hard-code the desktop directory path, as there is a function to get it for you and it will automatically change for each user on the computer.

Onto actually saving the file, there are many parameters for the WorkBook.Save method, and so I took the easy route of using a named parameter to tell it the format to save in. It will automatically add the correct filename extension for the type of file being saved.

So...

Option Strict On
Option Infer On

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports System.IO

Public Class Form1

    Private Sub SaveExcelAsTextOperation(src As String, dest As String)
        Dim app As New Excel.Application
        Dim book = app.Workbooks.Open(src)
        book.SaveAs(dest, FileFormat:=XlFileFormat.xlCSV)
        book.Close()
        app.Quit()

    End Sub

    Private Sub SaveExcelAsText(src As String, dest As String)
        ' from: The proper way to dispose Excel com object using VB.NET? https://stackoverflow.com/a/38111107
        SaveExcelAsTextOperation(src, dest)
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

    Private Sub btnExcelSaveAs_Click(sender As Object, e As EventArgs) Handles btnExcelSaveAs.Click
        Dim filename1 = txtBoxExcelFileNameString.Text
        Dim workDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
        Dim source = Path.Combine(workDirectory, filename1 & ".xlsx")
        Dim destination = Path.Combine(workDirectory, filename1)
        SaveExcelAsText(source, destination)

    End Sub

End Class

does what I think you wanted. There is an Excel save dialog which appears; I am sure you can find an option to not show that.

Thanks for this detailed answer. I can hopefully try this later and confirm your answer works for me. — Busta, May 21 '18 at 12:22

Open file in a specific directory

1 Answers1

Linked