Open an Excel Workbook Using SolidWorks API in VBA

Question

I'm trying to open an Excel workbook or if already open, grab its object.

Nearly everything online assumes you're running VBA from Excel, so it makes it easy to scan your open workbooks to see if your specific workbook is open.
An instance of Excel may not be running on my machine.

I found a way to accomplish what I need, except by default, it automatically hides my workbook. I can manually unhide, but I don't want to do that.

I found the following

xlApp.Windows(fileName).Visible = true

This works if I first manually unhide it.

If I try to run this line of code before I manually unhide it, I get

runtime error: Subscript out of range.

Private Type myType
    fileName As String
    folderPath As String
    filePath As String
    app As excel.Application
    WB As excel.Workbook
End Type

Private this As myType
    
Private Sub getWorkbook()
    
    Set this.WB = GetObject(this.filePath)
    
    If Not this.WB Is Nothing Then
        Set this.app = this.WB.Application
    End If
    
    Set this.WB = this.app.Workbooks.Open(this.filePath)
    this.app.Visible = True
    
    this.app.Windows(this.fileName).Visible = True

Answer 1

The following changes worked.

Private Sub getWorkbook()

Set this.WB = GetObject(this.filePath)

If Not this.WB Is Nothing Then
    Set this.app = this.WB.Application
End If

Set this.WB = this.app.Workbooks.Open(this.filePath)

this.app.Windows(this.fileName).Visible = True

End Sub

I removed the this.app.visible = true and it started working for me. I'm not sure why though.

Answer 2

I'm aware that some developers use the code below to open Excel files. However, this solution is not considered reliable base on my testing.

Set this.WB = GetObject("C:\test.xlsx")

Please try to get Excel Application before get workbook object.

Private Type myType
    fileName As String
    folderPath As String
    filePath As String
    app As Excel.Application
    WB As Excel.Workbook
End Type

Private this As myType


Private Sub getWorkbook()
    this.filePath = "d:\temp\"
    this.fileName = "excel_img.xlsx"
    Set this.app = Nothing
    On Error Resume Next
    Set this.app = GetObject(, "excel.application")
    On Error GoTo 0
    If this.app Is Nothing Then
        Set this.app = CreateObject("excel.application")
    End If
    this.app.Visible = True
    Set this.WB = Nothing
    On Error Resume Next
    Set this.WB = this.app.Workbooks(this.fileName)
    On Error GoTo 0
    If this.WB Is Nothing Then
        Set this.WB = this.app.Workbooks.Open(this.filePath & this.fileName)
    End If
    
    this.WB.Activate
    this.WB.Windows(1).Visible = True
End Sub