Suppressing Message After Automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Access I'm opening up an Excel Spreadsheet and copying a range of cells
then pasting that information into a Word Document which then gets printed
out.
When the spreadsheet is closing, I get the following message which I would
like to suppress so that the user does not have to answer the Yes, No or
Cancel prompt.

"There is a large amount of information on the Clipboard. Do you want to be
able to paste this information into another program later?
To save it on the Clipboard so you can paste it later, click yes.
To delete it from the Clipboard and free memory, click no."

Here is part of the code I am using to perform the function. I believe the
message is being generated after the "objWkb.Application.ActiveWindow.Close
SaveChanges:=False" command.

I tried using the DoCmd.SetWarnings False command but that did not suppress
the message. What can I do and how would I do it to suppress the message?
Thanks.

Set objXL = New Excel.Application
strSpread = DLookup("[DataPath]", "tblApplicationInfo") &
"CutSheets\" & CRS3.Fields("FileName")
With objXL
.Visible = False 'True
Set objWkb = .Workbooks.Open(strSpread)
'CRS3.Fields("FileName"))
Dim objRange As Excel.Range
Set objRange = .Range(CRS3.Fields("PrintRange"))
objRange.Select
objRange.Copy
Set objRange = Nothing
End With
End If
'Copy the selection into the Word document
Forms!fmOrders.Caption = "Copying Cut Sheet info into Word document"
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="Chart"
strLocation = "Paste Excel Cells into Word"
With objWord
.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
End With

'End If
'Close the Cut Sheet
strLocation = "Quit Excel"
Forms!fmOrders.Caption = "Closing Excel"
objWkb.Application.ActiveWindow.Close SaveChanges:=False
 
DoCmd.SetWarnings False only applies to run queries. If you snoop around
the Excel Object Model (Open Excel, Go to the VBA editor and select
'EXCEL OBJECT MODEL' in the HELP index) you should find a method
(probably of the APPLICATION object) that clears the clipboard.
 
I don't know how you are opening Excel. If you are just shelling to it, I
don't think you can supress it. If you are using automation, I think this
will work with your Excel Application Object:

Application.DisplayAlerts = False
 
That command worked great! Thanks!

Klatuu said:
I don't know how you are opening Excel. If you are just shelling to it, I
don't think you can supress it. If you are using automation, I think this
will work with your Excel Application Object:

Application.DisplayAlerts = False



AccessDev said:
In Access I'm opening up an Excel Spreadsheet and copying a range of cells
then pasting that information into a Word Document which then gets printed
out.
When the spreadsheet is closing, I get the following message which I would
like to suppress so that the user does not have to answer the Yes, No or
Cancel prompt.

"There is a large amount of information on the Clipboard. Do you want to be
able to paste this information into another program later?
To save it on the Clipboard so you can paste it later, click yes.
To delete it from the Clipboard and free memory, click no."

Here is part of the code I am using to perform the function. I believe the
message is being generated after the "objWkb.Application.ActiveWindow.Close
SaveChanges:=False" command.

I tried using the DoCmd.SetWarnings False command but that did not suppress
the message. What can I do and how would I do it to suppress the message?
Thanks.

Set objXL = New Excel.Application
strSpread = DLookup("[DataPath]", "tblApplicationInfo") &
"CutSheets\" & CRS3.Fields("FileName")
With objXL
.Visible = False 'True
Set objWkb = .Workbooks.Open(strSpread)
'CRS3.Fields("FileName"))
Dim objRange As Excel.Range
Set objRange = .Range(CRS3.Fields("PrintRange"))
objRange.Select
objRange.Copy
Set objRange = Nothing
End With
End If
'Copy the selection into the Word document
Forms!fmOrders.Caption = "Copying Cut Sheet info into Word document"
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="Chart"
strLocation = "Paste Excel Cells into Word"
With objWord
.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
End With

'End If
'Close the Cut Sheet
strLocation = "Quit Excel"
Forms!fmOrders.Caption = "Closing Excel"
objWkb.Application.ActiveWindow.Close SaveChanges:=False
Click to expand...
Click to expand...
 
Back
Top