如何通过访问 vba 在 excel 应用程序中设置警告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23254629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set warnings off in excel application from access vba
提问by vuyy1182
I'm opening excel workbook from access, after writing contents from access to excel, i'm using xlobj.saveto save the workbook. Excel application is giving some warnings says this workbook already exists do u want to replace it. How to disable such warnings from access.
我正在从访问中打开 excel 工作簿，在从访问中写入内容到 excel 后，我正在使用xlobj.save来保存工作簿。Excel 应用程序发出一些警告说这个工作簿已经存在你想替换它。如何禁止访问此类警告。
I'm using DoCmd.SetWarnings offbut not working.
我正在使用DoCmd.SetWarnings 关闭但不工作。
Here is my code
这是我的代码
Public Sub sCopyResultstoexcel(conSHT_NAME As Variant, conWKB_NAME As Variant,
qrytable As String)
'Copy records to first 20000 rows
'in an existing Excel Workbook and worksheet
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim rs_Attribute As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset(qrytable, dbOpenSnapshot)
With objXL
.Visible = True
DoCmd.SetWarnings off
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Cells.ClearContents
DoCmd.SetWarnings off
.Range(.Cells(2, 1), .Cells(conMAX_ROWS, _
intLastCol)).CopyFromRecordset rs
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).Font.Bold = True
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).WrapText = False
'Formatting
With objSht.Range("A1:CP1")
.HorizontalAlignment = xlCenter
.ColumnWidth = "8"
.Font.Italic = False
.Font.Bold = True
.EntireColumn.ColumnWidth = 15
End With
'Adding fields
With rs
For i = 1 To .Fields.Count
objSht.Cells(1, i) = .Fields(i - 1).Name
Next i
DoCmd.SetWarnings off
objWkb.Save
End With
End With
End With
'objWkb.Close
'objXL.Quit
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
回答by Casey Morter
I have had similar issues with trying to open read only / already open workbooks with VBA.
我在尝试使用 VBA 打开只读/已经打开的工作簿时遇到了类似的问题。
after your line:
在您的线路之后：
Set objXL = New Excel.Application
add
添加
objXL.DisplayAlerts = False
objXL.AskToUpdateLinks = False
objXL.EnableEvents = False
To get around saving over existing documents, you can save it somewhere temporarily, and then force copy it. Kind of brutish, but works...
要避免保存现有文档，您可以将其临时保存在某处，然后强制复制它。有点野蛮，但有效......
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile SourceFile, DestinationFile, True
FSO.DeleteFile SourceFile
where SourceFile and DestinationFile are paths with file names.
其中 SourceFile 和 DestinationFile 是带有文件名的路径。
回答by steveo40
- Check to see if the file already exists (use the Dir command).
- If it does, delete it.
- Save your file.
- 检查文件是否已经存在（使用 Dir 命令）。
- 如果是，请删除它。
- 保存您的文件。
