Suppress Warnings in Excel

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

Guest

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
 
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.
 
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
 
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.
 
Back
Top