vba 对象“_Global”的运行时错误“1004”方法“行”失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14168468/
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
Run-time error '1004' Method 'Rows' of object '_Global' Failed
提问by user1950339
I am new to VBA as I have just started learning it.
我是 VBA 新手，因为我刚刚开始学习它。
Right now I'm facing a problem in exporting the message body from outlook to excel. The funny thing is when i run the first time, it works. But when when i run the second time, the error message as stated in my title appears.
现在我在将邮件正文从 Outlook 导出到 excel 时遇到了问题。有趣的是，当我第一次运行时，它起作用了。但是当我第二次运行时，出现了标题中所述的错误消息。
I clicked on the debug and it highlighted this code: "offsetRow = Cells(Rows.Count, 1).End(xlUp).Row"
我点击了调试并突出显示了这段代码：“offsetRow = Cells(Rows.Count, 1).End(xlUp).Row”
I have tried various way like selecting the worksheet that I wanted to paste the data into it but to no avail. Therefore, I hope the experts here can assist me in debugging the code. Also feel free to feedback on my coding if I have done any redundancy that will slow my computer.
我尝试了各种方法，例如选择我想将数据粘贴到其中的工作表，但无济于事。所以，希望这里的高手能帮我调试一下代码。如果我做了任何会减慢计算机速度的冗余，也可以随时对我的编码提供反馈。
FYI, this is for my work so that I can export out the email contents into excel. Thanks in advance.
仅供参考，这是我的工作，以便我可以将电子邮件内容导出到 Excel 中。提前致谢。
Sub ExportToExcel()
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object
Dim masterData() As String
Dim subData() As String
Dim i As Integer
Dim offsetRow As Long
strSheet = "For fun.xlsx"
strPath = "C:\Users\XXXXX\Desktop\New folder\"
strSheet = strPath & strSheet
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
'Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then
MsgBox "Thank you for using this service.", vbOKOnly, "Error"
Exit Sub
ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "Please select the correct folder.", vbOKOnly, "Error"
Exit Sub
ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
End If
'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets("Sheet1")
wks.Activate
appExcel.Application.Visible = True
'Copy field items in mail folder.
For Each itm In fld.Items
Set msg = itm
masterData = Split(msg.Body, vbCrLf) 'Seperate according to lines
For i = 0 To UBound(masterData)
If masterData(i) = "" Then
'Do nothing
Else
'do the split here
subData = Split(masterData(i), vbTab)
wks.Activate
offsetRow = Cells(Rows.Count, 1).End(xlUp).Row 'This is where the error appears
If i = 0 Then
intRowCounter = i + offsetRow + 1
Else
intRowCounter = i + offsetRow
End If
For intColumnCounter = 0 To UBound(subData)
Set rng = wks.Cells(intRowCounter, intColumnCounter + 1)
rng.Value = subData(intColumnCounter)
Next intColumnCounter
End If
Next i
Next itm
Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
End Sub
采纳答案by user1950339
I changed the:
我改变了：
offsetRow = Cells(Rows.Count, 1).End(xlUp).Row
into
进入
With wks
offsetRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
and it works now.
现在可以使用了。
回答by chris neilsen
Your problem is because you don't qualify the Excel range references
您的问题是因为您没有限定 Excel 范围引用
Change
改变
offsetRow = Cells(Rows.Count, 1).End(xlUp).Row 'This is where the error appears
To
到
offsetRow = wks.Cells(wks.Rows.Count, 1).End(-4162).Row
BTW there are a lot of optimisations that can be done to this code
顺便说一句，可以对这段代码进行很多优化
