none
How do i Run a excel file from Visual Studio 2010 RRS feed

  • Question

  • I have made my first Visual Studio program trying to just check to see if a file exists. If the file exists display someone is running the program right now please try again.  If the file does not exists I would like to run or start up the excel file that has some VBA macros that automatically run when it is opened.  Once I have the excel executed I want to close this program.

    Here is the code that I have don't know how to call the excel program.

    Public Class Form1

     

        Dim Application As Object

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'MsgBox("This runs first?")

            Dim FoundFile As String

            Dim CurrentDir As String

     

            CurrentDir = CurDir()

            ChDir(CurrentDir)

            '        MsgBox(CurrentDir)

     

     

            'Check to see if Locked file is here.

            ' This is use to see if someone else is running the code.

            FoundFile = FExists("LockFile.txt")

            If FoundFile = "False" Then

                LabelFeedback.Text = "Run TCRAFT_LOG Program"

                ' Call or Run TCRAFT_LOG.xlsm

     

     

     

            Else

                LabelFeedback.Text = "Sorry, Can't run flight log system.  Being used by another user. Please try again."

            End If

     

        End Sub

     

        Public Function FExists(ByVal OrigFile As String)

            Dim fs

            fs = CreateObject("Scripting.FileSystemObject")

            FExists = fs.fileexists(OrigFile)

        End Function

        'Returns a boolean - True if the file exists

     

     

        Private Sub ButtonClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClose.Click

            Close()

        End Sub

    End Class

     

    Friday, April 1, 2011 8:13 PM

Answers

  • Hi Bruce,

     

    You’ll need to add a Reference to Excel in your project.

    Project > Add Reference > .Net then find Microsoft.Office.Intero.Excel for your version number.

     

    Add this code above your Public Class statement:

         Option Explicit On

         Imports xApp = Microsoft.Office.Interop.Excel.Application

         Imports System.Runtime.InteropServices

     

    For the Class add this as a variable:

         Dim xObj As Object

     

    At the appropriate spot in your code’s logic, add this:

            Dim MyXL As xApp

            Dim newXl As Boolean = False

            If GetObj("Excel.Application") = False Then

                MyXL = New xApp

                MyXL.Visible = True

                newXl = True

            Else

                MyXL = xObj

            End If

     

            MyXL.Visible = True

            MyXL.Workbooks.Open(“Your Work Book Name”)

     

    Add this function to your code.

        Function GetObj(ByVal appName As [String]) As Boolean

            Dim obj As Object = Nothing

     

            Try

                obj = Marshal.GetActiveObject(appName)

            Catch e As Exception

                Return False

            End Try

     

            If obj IsNot Nothing Then

                xObj = obj

                Return True

            End If

        End Function

     

    At the appropriate spot in your code, when you are done with the Wordbook add this:

            If newXl = True Or MyXL.Workbooks.Count = 0 Then MyXL.Quit()

     

    There might be more elegant ways, but this works.

     

    Hope it helps


    Regards
    • Marked as answer by Bessie Zhao Friday, April 8, 2011 10:07 AM
    Saturday, April 2, 2011 6:52 PM
  • Hello Rich,

     

    I got it played with it again.  I moved xObj inside the Class Form outside of the Form1_Load and everything works great.  Thanks so much for you help.  It does exactly what I wanted it to do.  I need to give it the full path to the file with I was able to do with my CurrentDir variable. 

     

    Thanks again.

    Bruce

     

     

    Option Explicit On

    Imports xApp = Microsoft.Office.Interop.Excel.Application

    Imports System.Runtime.InteropServices

     

     

    Public Class Form1

        Dim xObj As Object

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'MsgBox("This runs first?")

            Dim FoundFile As String

            Dim CurrentDir As String

     

            Dim MyXL As xApp

            Dim newXl As Boolean = False

     

            CurrentDir = CurDir()

            ChDir(CurrentDir)

            'MsgBox(CurrentDir)

     

            'Check to see if Locked file is here.

            FoundFile = FExists("LockFile.txt")

            If FoundFile = "False" Then

                LabelFeedback.Text = "Run TCRAFT_LOG Program"

                If GetObj("Excel.Application") = False Then

                    MyXL = New xApp

                    MyXL.Visible = True

                    newXl = True

                Else

                    MyXL = xObj

                End If

     

                ' Open and TCRAFT_LOG.xlsm

                MyXL.Visible = True

                MyXL.Workbooks.Open(CurrentDir & "\TCRAFT_LOG.xlsm")

     

                'Clean up for Quit

                If newXl = True Or MyXL.Workbooks.Count = 0 Then MyXL.Quit()

                Close()

     

     

            Else

                LabelFeedback.Text = "Sorry, Can't run flight log system.  Being used by another user. Please try again."

            End If

     

        End Sub

     

        Public Function FExists(ByVal OrigFile As String)

            Dim fs

            fs = CreateObject("Scripting.FileSystemObject")

            FExists = fs.fileexists(OrigFile)

        End Function

        'Returns a boolean - True if the file exists

     

        Function GetObj(ByVal appName As [String]) As Boolean

            Dim obj As Object = Nothing

     

            Try

                obj = Marshal.GetActiveObject(appName)

            Catch e As Exception

                Return False

            End Try

     

            If obj IsNot Nothing Then

                xObj = obj

                Return True

            End If

        End Function

     

     

     

        Private Sub ButtonClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClose.Click

            Close()

        End Sub

     

     

    End Class

    • Marked as answer by Bessie Zhao Friday, April 8, 2011 10:08 AM
    Saturday, April 2, 2011 9:00 PM

All replies

  • Hi Bruce,

     

    You’ll need to add a Reference to Excel in your project.

    Project > Add Reference > .Net then find Microsoft.Office.Intero.Excel for your version number.

     

    Add this code above your Public Class statement:

         Option Explicit On

         Imports xApp = Microsoft.Office.Interop.Excel.Application

         Imports System.Runtime.InteropServices

     

    For the Class add this as a variable:

         Dim xObj As Object

     

    At the appropriate spot in your code’s logic, add this:

            Dim MyXL As xApp

            Dim newXl As Boolean = False

            If GetObj("Excel.Application") = False Then

                MyXL = New xApp

                MyXL.Visible = True

                newXl = True

            Else

                MyXL = xObj

            End If

     

            MyXL.Visible = True

            MyXL.Workbooks.Open(“Your Work Book Name”)

     

    Add this function to your code.

        Function GetObj(ByVal appName As [String]) As Boolean

            Dim obj As Object = Nothing

     

            Try

                obj = Marshal.GetActiveObject(appName)

            Catch e As Exception

                Return False

            End Try

     

            If obj IsNot Nothing Then

                xObj = obj

                Return True

            End If

        End Function

     

    At the appropriate spot in your code, when you are done with the Wordbook add this:

            If newXl = True Or MyXL.Workbooks.Count = 0 Then MyXL.Quit()

     

    There might be more elegant ways, but this works.

     

    Hope it helps


    Regards
    • Marked as answer by Bessie Zhao Friday, April 8, 2011 10:07 AM
    Saturday, April 2, 2011 6:52 PM
  • Thanks RichMichaels,

    Thanks so much for you input.  I am trying to add your code having  a couple problems.

    1.       Variable  xObj is used before it has been assigned.  I am marking this with <== Problem 1.

    2.       XObj is not declared.  I am marking this with <== Problem 2.

    Thanks for your help Rich we are close.

    Thanks,

    Bruce

    Here is the new code!

    Option Explicit On

    Imports xApp = Microsoft.Office.Interop.Excel.Application

    Imports System.Runtime.InteropServices

     

     

    Public Class Form1

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'MsgBox("This runs first?")

            Dim FoundFile As String

            Dim CurrentDir As String

     

            Dim xObj As Object

            Dim MyXL As xApp

            Dim newXl As Boolean = False

     

            CurrentDir = CurDir()

            ChDir(CurrentDir)

            'MsgBox(CurrentDir)

     

            'Check to see if Locked file is here.

            FoundFile = FExists("LockFile.txt")

            If FoundFile = "False" Then

                LabelFeedback.Text = "Run TCRAFT_LOG Program"

                If GetObj("Excel.Application") = False Then

                    MyXL = New xApp

                    MyXL.Visible = True

                    newXl = True

                Else

                    MyXL = xObj  <== Problem 1

                End If

     

                ' Open and TCRAFT_LOG.xlsm

                MyXL.Visible = True

                MyXL.Workbooks.Open("TCRAFT_LOG.xlsm")

     

                'Clean up for Quit

                If newXl = True Or MyXL.Workbooks.Count = 0 Then MyXL.Quit()

     

     

            Else

                LabelFeedback.Text = "Sorry, Can't run flight log system.  Being used by another user. Please try again."

            End If

     

        End Sub

     

        Public Function FExists(ByVal OrigFile As String)

            Dim fs

            fs = CreateObject("Scripting.FileSystemObject")

            FExists = fs.fileexists(OrigFile)

        End Function

        'Returns a boolean - True if the file exists

     

        Function GetObj(ByVal appName As [String]) As Boolean

            Dim obj As Object = Nothing

     

            Try

                obj = Marshal.GetActiveObject(appName)

            Catch e As Exception

                Return False

            End Try

     

            If obj IsNot Nothing Then

                xObj = obj <== Problem 2

                Return True

            End If

        End Function

     

     

     

        Private Sub ButtonClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClose.Click

            Close()

        End Sub

     

     

    End Class

    Saturday, April 2, 2011 8:06 PM
  • Hello Rich,

     

    I got it played with it again.  I moved xObj inside the Class Form outside of the Form1_Load and everything works great.  Thanks so much for you help.  It does exactly what I wanted it to do.  I need to give it the full path to the file with I was able to do with my CurrentDir variable. 

     

    Thanks again.

    Bruce

     

     

    Option Explicit On

    Imports xApp = Microsoft.Office.Interop.Excel.Application

    Imports System.Runtime.InteropServices

     

     

    Public Class Form1

        Dim xObj As Object

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'MsgBox("This runs first?")

            Dim FoundFile As String

            Dim CurrentDir As String

     

            Dim MyXL As xApp

            Dim newXl As Boolean = False

     

            CurrentDir = CurDir()

            ChDir(CurrentDir)

            'MsgBox(CurrentDir)

     

            'Check to see if Locked file is here.

            FoundFile = FExists("LockFile.txt")

            If FoundFile = "False" Then

                LabelFeedback.Text = "Run TCRAFT_LOG Program"

                If GetObj("Excel.Application") = False Then

                    MyXL = New xApp

                    MyXL.Visible = True

                    newXl = True

                Else

                    MyXL = xObj

                End If

     

                ' Open and TCRAFT_LOG.xlsm

                MyXL.Visible = True

                MyXL.Workbooks.Open(CurrentDir & "\TCRAFT_LOG.xlsm")

     

                'Clean up for Quit

                If newXl = True Or MyXL.Workbooks.Count = 0 Then MyXL.Quit()

                Close()

     

     

            Else

                LabelFeedback.Text = "Sorry, Can't run flight log system.  Being used by another user. Please try again."

            End If

     

        End Sub

     

        Public Function FExists(ByVal OrigFile As String)

            Dim fs

            fs = CreateObject("Scripting.FileSystemObject")

            FExists = fs.fileexists(OrigFile)

        End Function

        'Returns a boolean - True if the file exists

     

        Function GetObj(ByVal appName As [String]) As Boolean

            Dim obj As Object = Nothing

     

            Try

                obj = Marshal.GetActiveObject(appName)

            Catch e As Exception

                Return False

            End Try

     

            If obj IsNot Nothing Then

                xObj = obj

                Return True

            End If

        End Function

     

     

     

        Private Sub ButtonClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClose.Click

            Close()

        End Sub

     

     

    End Class

    • Marked as answer by Bessie Zhao Friday, April 8, 2011 10:08 AM
    Saturday, April 2, 2011 9:00 PM
  • Hi Bruce,

    You're welcome.

    Happy to hear it all worked well for you.


    Regards
    Sunday, April 3, 2011 1:31 AM