CODEWITHSUNDEEP
vbams-access
CryingOverVBA
CryingOverVBA

Reputation: 35

Window not visible despite running in background

I have a macro that I'm trying to run through Access that will open up an excel sheet, do some actions on it, and then leave the sheet open.

I have most of it working, with the exception of not being able to get my excel document to open visibly. If I check the task manager, an excel process is running in the background so something does happen, just nothing that I can physically see.

I've attempted to sample some code found through stackoverflow and other resources, which I'm sure you'll see some of that in my current code. But I tried for about an hour with no avail. 

Private Sub Command1_Click()

Dim fd As FileDialog
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

On Error GoTo ErrorHandler
'allowing selection of the time
Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        MySheetPath = fd.SelectedItems(1)
    End If
Else
    End
End If

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

ShowaWindow (MySheetPath)

Set XlSheet = XlBook.Worksheets(1)

XlSheet.Rows(2).EntireRow.Insert
XlSheet.Range("D2") = "ABC"

Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

Exit Sub

ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)

End Sub

Sub ShowaWindow(sFileName As String)
Dim oWb As Workbook

Set oWb = GetObject(sFileName)

For Each oWb In Workbooks
    If LCase(oWb.Name) <> LCase(sFileName) Then
        oWb.Windows(1).Visible = True
        Exit For
    End If
Next

End Sub

Ideally I would like to be able to see the worksheet appear.

Upvotes: 0

Views: 181

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Set XlBook = GetObject(MySheetPath)

This is wrong. Don't use GetObject to open the workbook, use the Excel.Application instance you just created:

Set XlBook = Xl.Workbooks.Open(MySheetPath)

Later you iterate all opened workbooks:

 For Each oWb In Workbooks

But that's not the Workbooks collection from the Xl application instance, it's the Workbooks collection from the instance that's currently running your code - you need to qualify it with the Xl object:

Private Sub ShowaWindow(ByVal app As Excel.Application, ByVal sFileName As String)
    '...
    For Each oWb In app.Workbooks

Also, make the app instance visible after you created it, and don't forget to invoke XlBook.Close and Xl.Quit to properly tear down that EXCEL.EXE process when you're done.

Upvotes: 0

user11509084
user11509084

Reputation: 

Set Xl = CreateObject("Excel.Application")
Xl.Visible=True

You don't need to put it immediately after creating the object, just before you set the object to Nothing.

Upvotes: 1

Related Questions