Hide Excel Workbook with VBA code

[mmogul]

I am creating a number of excel workbooks (xlsm) using Access vba code - using Office 2007. I want to hide excel during all the processing.

I am using xlApp.visible = false (where xlApp is defined as excel.application. This works until I use the "copy after" function to make a copy of a worksheet. This makes the excel workbook visible - which I am trying to avoid.

I believe my approach was working in Office 2003. Anyone know how to address this? Here is the code that has the problem.

Private Sub cmdTest_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim sDestinationFile As String

sDestinationFile = "c:\rad\test.xlsm"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWB = xlApp.Workbooks.Open(sDestinationFile)
[COLOR=red]xlWB.Sheets("Facility").Copy After:=xlWB.Sheets("Facility")[/color]

xlApp.Visible = False
    
End Sub

 

[lameid]

You are more likely to get an answer by posting in the more appropriate forum....
forum707

The forum is a gray area but the people who know Excel well are there.

 

[SkipVought]

hmmmmm???

It works for me.

I added a Save, Close and Quit among other housekeeping chores.

Private Sub cmdTest_Click()
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim sDestinationFile As String
    
    sDestinationFile = "c:\All_Data.xls"
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set xlWB = xlApp.Workbooks.Open(sDestinationFile)
    xlWB.Sheets("Facility").Copy After:=xlWB.Sheets("Facility")
    
    xlWB.Save
    xlWB.Close
    xlApp.Quit
    
    Set xlWB = Nothing
    Set xlApp = Nothing
        
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mmogul]

Skip - thanks.

We recently upgraded to 2007 here plus I have a new laptop (though I don't think this is hardware related). Your response confirms one of my hypotheses that I may have an unpatched version of 2007. I don't have admin rights at this client so I have to wait until I get support to apply any windows updates.

Thanks again.

Mark

 

[mmogul]

I further investigated this issue and found that the cause is not a hardware or windows update issue. Instead I determined that this behavior occurs when the source sheet that is to be copied has an ActiveX object on it -- like a button, checkbox, or combo box.

If I remove these objects from the target, excel remains hidden during the copy command. When I place the objects back, excel becomes visible during the copy command and then is hidden again.

Has anyone run into this? Any suggestions?

(I'm going to post this in the excel forum as well)