通过 Outlook VBA 操作 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17599987/
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
Manipulate Excel via Outlook VBA
提问by Charlie
I've almost got this working but I'm stuck on one part. Here's what I'm trying to do:
我几乎已经开始工作了，但我被困在了一个部分。这是我想要做的：
- Save Outlook email attachment (.csv file)
- Open attachment in Excel
- Delete the last 6 lines of the file
- Re-save the file
- 保存 Outlook 电子邮件附件（.csv 文件）
- 在 Excel 中打开附件
- 删除文件的最后 6 行
- 重新保存文件
I'm able to save the file and get it to open in Excel, but then nothing else happens. No matter what I try I'm unable to get any actions to happen within Excel; I can't get it to delete the last 6 rows (Parse the Footer). Any help would be greatly appreciated!
我能够保存文件并让它在 Excel 中打开，但随后什么也没有发生。无论我尝试什么，我都无法在 Excel 中执行任何操作；我无法删除最后 6 行（解析页脚）。任何帮助将不胜感激！
Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim attachName As String
Dim oXL As Object, oWB As Object, oSheet As Object
saveFolder = "C:\Temp\"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
attachName = objAtt.DisplayName
Set objAtt = Nothing
Next
' Start Excel and get Application object
Set oXL = CreateObject("Excel.Application")
' Hide Excel
oXL.Visible = False
' Open the File
Set oWB = oXL.Workbooks.Open(saveFolder & attachName)
'Set the Worksheet
Set oSheet = oWB.Sheets("Sheet1")
'Parse the Footer
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(-5, 0).Range("A1:A6").Select
ActiveCell.Activate
Selection.ClearContents
'Save the File
Set oWB = oXL.Workbooks.Save(saveFolder & "\" & objAtt.DisplayName)
'Clean Up
oWB.Close (True)
oXL.Quit
Set oWB = Nothing
Set oXL = Nothing
End Sub
采纳答案by Charlie
Thanks guys. I got this working. Here's the final code:
谢谢你们。我得到了这个工作。这是最终的代码：
Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim attachName As String
Dim oXL As Excel.Application
Dim oWB As Excel.workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
saveFolder = "C:\Temp\"
'Grab attachment
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
attachName = objAtt.DisplayName
Set objAtt = Nothing
Next
' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
' Get a new workbook.
Set oWB = oXL.Workbooks.Open(saveFolder & attachName)
Set oSheet = oWB.ActiveSheet
' Find Last Row and Clear Contents; Do this 5 Times
Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
oRng.Cells.ClearContents
Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
oRng.Cells.ClearContents
Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
oRng.Cells.ClearContents
Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
oRng.Cells.ClearContents
Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
oRng.Cells.ClearContents
' Make sure Excel is visible and give the user control
' of Microsoft Excel's lifetime.
oXL.Visible = True
oXL.UserControl = True
'Save the File
oWB.Save
oWB.Saved = True
' Quite, Close and Make sure you release object references.
oWB.Close
oXL.Quit
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
End Sub
回答by Andy G
This line
这条线
Set oWB = oXL.Workbooks.Save(saveFolder & "\" & objAtt.DisplayName)
needs to refer to the attachNamestring you stored earlier
需要引用attachName您之前存储的字符串
Set oWB = oXL.Workbooks.Save(saveFolder & "\" & attachName)
because objAtt is Nothingat that point.
因为 objAtt 就Nothing在这一点上。
Use
用
MsgBox objAtt.DisplayName
just before the Save so that you can check that it is suitable.
就在 Save 之前，以便您可以检查它是否合适。
BTW Comment out the line that hides Excel (Visible = True) and step through the code pressing F8 so you can see what is happening.
顺便说一句，注释掉隐藏 Excel (Visible = True) 的行并按 F8 逐步执行代码，以便您可以看到发生了什么。
