Hide Excel Workbook with VBA Code from Access

[mmogul]

(I started this thread a few days ago, but now have more info regarding this issue).

I create a number of excel workbooks that contain macros(xlsm) using Access vba code. I am running Office 2007. I want to hide excel during all the processing. The approach shown below worked in Office 2003.

I am using the command xlApp.visible = false (where xlApp is defined as excel.application) to hide Excel and the "copy after" function to make a copy of a "template" worksheet to another. This code works fine as long as there are no ActiveX objects (check boxes, buttons, combo box) on the "source" worksheet.

When the "source" worksheet has one of these ActiveX objects, Excel remains hidden until I use the "copy after" function to make a copy of a worksheet (code sample below). When this command is executed, the Excel workbook becomes visible for a brief time (less than a second) and then it is hidden again. I need to avoid this since it interrupts the user during this processing.

Anyone have any thoughts on how to handle this? Can you replicate this behavior? Here is the code.

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]

xlWB.ActiveSheet.Name = "newName"

xlWB.Save    
xlWB.Close    
xlApp.Quit        
Set xlWB = Nothing    
Set xlApp = Nothing        
End Sub

 

[TheAceMan1]

How are ya mmogul . . .

Using [blue]Application.ScreenUpdating[/blue] may help. However there is a danger to over come. If while [blue]Application.ScreenUpdating = False[/blue] an error occurs or you break into debug mode ... it will seem as if Excel is hung up! To circumvent this a set of hotkeys is setup to return everything to normal. So in a module copy/paste the following:

[blue]Public Function UpdatingOn()
   Application.ScreenUpdating = True
   Application.Cursor = xlDefault
   
   MsgBox "Screen Updating is On!" & vbNewLine & vbNewLine & _
          "Cursor is Default!", _
          vbExclamation + vbOKOnly, _
          "Screen Updating Enablded! ..."
End Function[/blue]

Next ... in the workbook [blue]Open[/blue] event, copy/paste the following line:

[blue]   Application.OnKey "^%u", "UpdatingOn"[/blue]

Switch to Excel and try the hotkeys [blue]Ctrl+Alt+U[/blue]. If you get the message your set to go. If things get locked-up just use the keys.

Now ... in your code, remout [green]xlApp.Visible = False[/green]. Then copy/paste the following lines in [purple]purple[/purple] where you see them:

[blue]   Dim xlApp As Excel.Application
   Dim xlWB As Excel.Workbook
   Dim sDestinationFile As String
   
   [purple][b]Application.ScreenUpdating = False[/b][/purple]
      sDestinationFile = "c:\rad\test.xlsm"
      Set xlApp = CreateObject("Excel.Application")
      [green][b]'xlApp.Visible = False[/b][/green]
      Set xlWB = xlApp.Workbooks.Open(sDestinationFile)
      
      
      xlWB.Sheets("Facility").Copy After:=xlWB.Sheets("Facility")
      
      
      xlWB.ActiveSheet.Name = "newName"
      
      xlWB.Save
      xlWB.Close
      xlApp.Quit
      Set xlWB = Nothing
      Set xlApp = Nothing
   [purple][b]Application.ScreenUpdating = True[/b][/purple][/blue]

Perform your testing.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mmogul]

thanks AceMan - i'll give this a try tomorrow!

 

[mmogul]

AceMan -- I gave this a try and it had no effect. I'm not sure why ScreenUpdating should affect the issue I am having. My understanding is that turning ScreenUpdating off just prevents changes in the spreadsheet from being shown, but it has no effect on whether or not the spreadsheet is visible.

Am I missing something here?

Thanks for your help.

Mark

 

[TheAceMan1]

mmogul . . .

Activate the [blue]'xlApp.Visible = False[/blue] line and try again.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]