asked on

How to apply Warning off message to my code

I have a code below. It works, but when I try to 1) check if csv file has appropriate column title and 2) if not, update the titles, and 3) save it into xls file, the warning message pops up, saying "A file named S:\db\invoice.xls exists. Do you want to replace it? I click "Yes" anyway, so how can I turn off the warning? Thank you very much in advance. -- hb

My code is below:

Private Sub ImportCSV_Click()

Dim xl As Object, strFilePath As String
strFilePath = "S:\Db\invoice.csv"
Set xl = CreateObject("Excel.Application")
' make the excel application visible
xl.Visible = True
' open the workbook

xl.Workbooks.Open strFilePath
If xl.Range("A1").Value = "InvoiceNo" Then
'do nothing
Else
xl.Range("1:1").insert
xl.ActiveSheet.Range("A1").Value = "InvoiceNo"
xl.ActiveSheet.Range("B1").Value = "Amount"
xl.ActiveSheet.Range("C1").Value = "DueDate"

' save and close the file
If Val(xl.Version) < 12 Then
xl.Workbooks(1).SaveAs "S:\Db\invoice.xls"
Else
' to use XLSX format:
'xl.Workbooks(1).SaveAs "S:\Db\invoice.xlsx", 51
' or to use XLSM format:
'xl.Workbooks(1).SaveAs "S:\Db\invoice.xlsm", 52
' or to use XLSB format:
'xl.Workbooks(1).SaveAs "S:\Db\invoice.xlsb", 50
' or to use good old XLS format:
xl.Workbooks(1).SaveAs "S:\Db\invoice.xls", 56
End If

xl.Workbooks(1).Close SaveChanges:=True
' close Excel

End If
xl.Quit
'clean up
Set xl = Nothing

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Invoice", "S:\Db\invoice.xls", True

MsgBox "Completed"

End Sub

Tracy

Re-posting as expert comment:

To turn off warnings in Excel, do this:
Application.DisplayAlerts = False

Turn it back on like this:
Application.DisplayAlerts = True

In Acces it would be this to turn them off
DoCmd.SetWarnings False

And this to turn them on:
DoCmd.SetWarnings True

Tracy

And for your specific example, it would be this:

xl.DisplayAlerts = False

Since you define the application as xl in your code.

kg6lfz

ASKER

Thank you, broomee9 -
I am sorry to ask, but based on my code, where should I put the "x1.DisplaiyAlerts = False" and "x1.DisplaiyAlerts = True". Sorry for being novice. Thank you.

ASKER CERTIFIED SOLUTION

Tracy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kg6lfz

ASKER

It worked perfectly. Thank you very much for your support.