0

I currently have 2 functions:

CreateExcelWorksheet()
GetExcelWorksheet()

My goal is to create Excel Workbooks/Worksheets using VB.NET code. Functions seems to work fine, Excel sheets being created and opened in right order but I have this NEWEST worksheet reading issue. My CreateExcelWorksheet() functions creates new instances but GetExcelWorksheet() functions reads the very first I created. Example: I create Book1, Book2 and Book3. My goal is to get the latest, Book3, I get Book1... If I close Book1, I get Book2... Not sure what I'm missing in my code, please see both functions below.

    Private Function CreateExcelWorksheet() As Excel.Worksheet
    ' Function to CREATE New Excel Worksheet
    Dim excel As Excel.Application

    ' Create an Excel Application Object and make it visible
    excel = CreateObject("Excel.Application")
    excel.Visible = True

    ' Create a New Excel Workbook
    Dim activeWorkook As Excel.Workbook = excel.ActiveWorkbook
    activeWorkook = excel.Workbooks.Add

    ' Create a New Worksheet
    Dim activeSheet As Object = excel.ActiveSheet
    activeSheet = activeWorkook.Worksheets(1)

    Return activeSheet
    End Function`


    Private Function GetExcelWorksheet() As Excel.Worksheet
    '  Function to READ Existing Excel Worksheet
    Dim excel As Excel.Application
    Dim activeWorksheet As Excel.Worksheet = Nothing

    Try
        excel = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    Catch ex As Exception
        MsgBox("No EXCEL Instance Found.")
        Return Nothing
    End Try

    Dim activeWorkook As Excel.Workbook = excel.ActiveWorkbook
    Dim activeSheet As Object = excel.ActiveSheet
    activeWorksheet = TryCast(activeSheet, Excel.Worksheet)

    Return activeWorksheet
    End Function
Justas
  • 101
  • 2
  • 8
  • The following may be helpful: https://stackoverflow.com/a/72872634/10024425 , https://stackoverflow.com/a/72794540/10024425 , and https://stackoverflow.com/a/72759270/10024425 . Also, enable [Option Strict](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement). – Tu deschizi eu inchid Jul 18 '22 at 19:24
  • Just to be clear, in the GetExcelWorksheet, you want the last worksheet in a Workbook? So the Last element in the activeWorkook.Worksheets() collection. Also seems a little unnecessary to declare activesheet as Object to immediately cast it. – Hursey Jul 18 '22 at 20:21

0 Answers0