locked
How to open excel 2017 from Visual Studio 2017 RRS feed

  • Question

  • How can I open an existing excel 2016 file from within Visual 2017 (VB)?

    The target is to the file, read a cell, change it's value (e.g. increment it), and save the file.

    I found references in Google, but none are working within VS2017 for me.

    I would appreciate a working code example.

    Thank you


    Sunday, February 3, 2019 2:27 PM

Answers

  • Hi YigalB,

    In advance, sorry if I misunderstand your description.
    Visual Studio itself cannot open an existing Excel file. We can make an application to open an existing Excel file, with using Visual Studio. 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Hi

    I found a solution and it works for me. See bellow.

    Yigal

    Dim MyExcel As New Microsoft.Office.Interop.Excel.Application
            MyExcel.Workbooks.Open(Me.TextBox1.Text)

            ' Extract
            MyExcel.Sheets("Sheet1").activate()
            MyExcel.Range("A1").Activate()

            Dim ThisRow As New ExcelRows

            Do
                If MyExcel.ActiveCell.Value > Nothing Or MyExcel.ActiveCell.Text > Nothing Then
                    ThisRow.c1 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 2

                    ThisRow.c2 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 3

                    ThisRow.c3 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 4

                    ThisRow.c4 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 5



                    ThisRow.answer = MyExcel.ActiveCell.Value

                    ExcelRowList.Add(ThisRow)
                    MyExcel.ActiveCell.Offset(1, -4).Activate() ' gp tp next line, 1st col

                Else
                    Completed = True
                    Exit Do
                End If
            Loop



            ' Close
            MyExcel.Workbooks.close()
            MyExcel = Nothing

    • Marked as answer by YigalB Wednesday, February 13, 2019 4:41 PM
    Thursday, February 7, 2019 7:16 AM

All replies

  • Hi YigalB,

    In advance, sorry if I misunderstand your description.
    Visual Studio itself cannot open an existing Excel file. We can make an application to open an existing Excel file, with using Visual Studio. 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Wednesday, February 6, 2019 4:49 AM
  • Hi YigalB,

    In advance, sorry if I misunderstand your description.
    Visual Studio itself cannot open an existing Excel file. We can make an application to open an existing Excel file, with using Visual Studio. 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Hi

    I found a solution and it works for me. See bellow.

    Yigal

    Dim MyExcel As New Microsoft.Office.Interop.Excel.Application
            MyExcel.Workbooks.Open(Me.TextBox1.Text)

            ' Extract
            MyExcel.Sheets("Sheet1").activate()
            MyExcel.Range("A1").Activate()

            Dim ThisRow As New ExcelRows

            Do
                If MyExcel.ActiveCell.Value > Nothing Or MyExcel.ActiveCell.Text > Nothing Then
                    ThisRow.c1 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 2

                    ThisRow.c2 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 3

                    ThisRow.c3 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 4

                    ThisRow.c4 = MyExcel.ActiveCell.Value
                    MyExcel.ActiveCell.Offset(0, 1).Activate() ' move to col 5



                    ThisRow.answer = MyExcel.ActiveCell.Value

                    ExcelRowList.Add(ThisRow)
                    MyExcel.ActiveCell.Offset(1, -4).Activate() ' gp tp next line, 1st col

                Else
                    Completed = True
                    Exit Do
                End If
            Loop



            ' Close
            MyExcel.Workbooks.close()
            MyExcel = Nothing

    • Marked as answer by YigalB Wednesday, February 13, 2019 4:41 PM
    Thursday, February 7, 2019 7:16 AM
  • Hi YigalB,

    You have made an application successfully.
    Thank you for providing code, but sorry, I have no time to make a new application using your code.
    Anyway, Congratulations!

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Thursday, February 7, 2019 9:34 AM