Suppress Warnings in Excel

M

Matt R

Hi

I'm having difficulty suppressing warnings when I export data to excel. The
excel file has a macro in it that i need and i would like to remove to
warning prompt i get each time i export. Below is the code i'm using.
Thanks for your help

Private Sub Export_CMD_Click()

Dim ExcelWorkbook As Object

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"Brand_Market_Main", "c:\Brand & Market Data\PivotTable_Tool", True, ""

Excel.Application.DisplayAlerts = False

Set ExcelWorkbook = GetObject("c:\Brand & Market Data\PivotTable_Tool.xls")


End Sub
 
K

Klatuu

You are trying to set a value for the DisplayAlerts before the application
object has been created. Here is a sample:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
 
M

Matt R

Hi

I've incorporated your code and it runs but i'm still getting a warning
about the macro. What am i doing wrong? Thanks

Private Sub Export_CMD_Click()

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject("c:\Brand & Market Data\PivotTable_Tool.xls")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("c:\Brand & Market Data\PivotTable_Tool.xls")
Else

End If
Err.Clear ' Clear Err object in case error occurred.

xlApp.DisplayAlerts = False

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"Brand_Market_Main", "c:\Brand & Market Data\PivotTable_Tool", True, ""

End Sub
 
K

Klatuu

Check your security settings in Excel. Sound like it is set at medium. I
don't think the DisplayAlerts covers that. To avoid the warnings, you
probably need to set macro security to low. I'm sure it can be done via VBA,
but I don't know the syntax without looking it up.

A trick I use is to open a spreadsheet, start recording a new macro, then
copy the code from the created macro and make mods as necessary to
incorporate it into my code.
 
Top