访问 2007 vba 以查找 Excel 2007 工作表中的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17771620/
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
Access 2007 vba to find last row in Excel 2007 worksheet
提问by user1407577
I have some VBA code within an Access 2007 database that exports data to an Excel 2007 file. I have a problem with this piece of the code:
我在 Access 2007 数据库中有一些 VBA 代码，可将数据导出到 Excel 2007 文件。我对这段代码有问题：
Sub GetLastRow(strSheet, strColum)
Dim MyRange As Range
Dim lngLastRow As Long
Set MyRange = Worksheets(strSheet).Range(strColum & "1")
lngLastRow = Cells(65536, MyRange.Column).End(xlUp).Row
lngLastRow = lngLastRow + 1
Rows(lngLastRow & ":1048576").Select
Selection.Delete Shift:=xlUp
End Sub
The issue is the variable lngLastRow does not count belong the header rows (these are already in the excel file) in excel file unless I manually open the Excel session and then continue running the code. I would like to solve this correctly, but as a minimum if I could include some code to display the excel file so it appears automatically that would solve the issue anyway. But can't see where/how I could do this.
问题是变量 lngLastRow 不属于 excel 文件中的标题行（这些已经在 excel 文件中），除非我手动打开 Excel 会话然后继续运行代码。我想正确解决这个问题，但至少如果我可以包含一些代码来显示 excel 文件，这样它就会自动出现，无论如何都可以解决这个问题。但看不到我在哪里/如何做到这一点。
The following is the function that calls the above function.
下面是调用上述函数的函数。
Function CreateExcelData()
'Copies data to be exported to an Excel workbook
Dim objExcel As Excel.Application
Dim strTemplate As String
Dim strPathFile As String
Dim RowCount As Integer
Dim wbExported As Workbook 'The initial exported data
Dim wbAllData As Workbook 'Workbook to copy exported data to
Dim rngUsed As Range 'Used range in exported data
Dim Sheet As Worksheet
'Try GetObject first in case Excel Application is already open.
On Error Resume Next
Set objExcel = GetObject(, "excel.Application")
If Err.Number <> 0 Then
'GetObject returns error if not already open
'so use CreateObject
On Error GoTo 0 'Turnoff ASAP so error trapping is available
Set objExcel = CreateObject("Excel.Application")
End If
strTemplate = "TEMPLATE.xlsm"
strPathFile = strPath & strTemplate
strPathFileFinal = strPath & strReportName & "_" & Mydat & ".xlsm"
FileCopy strPathFile, strPathFileFinal
'Open the exported data workbook and assign to a variable
Set wbExported = objExcel.Workbooks.Open(strFilePath)
'Open the data workbook to receive the exported data and assign to a variable.
Set wbAllData = objExcel.Workbooks.Open(strPathFileFinal)
'Exported data
With wbExported.Sheets(1).UsedRange
Set rngUsed = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count)
End With
With wbAllData.Sheets("MainSheet")
'Copy exported data and paste to first empty cell of MainSheet in File
rngUsed.Copy
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With
Call GetLastRow("MainSheet", "A")
wbExported.Close
wbAllData.Save
wbAllData.Close
Set rngUsed = Nothing
Set wbExported = Nothing
Set wbAllData = Nothing
Set objExcel = Nothing
Kill strFilePath
End Function
回答by chris neilsen
Your code has a number of unqualified and partially qualified references to Worksheetsand Ranges. These will refer to the ActiveWorkbookor ActiveSheet, probably not wjhat you want, and will cause unpredictable results.
您的代码有许多不合格和部分合格的参考Worksheets和Ranges。这些将引用ActiveWorkbookor ActiveSheet，可能不是您想要的，并且会导致不可预测的结果。
Try this refactor
试试这个重构
Sub GetLastRow(MyRange As Excel.Range)
Dim lngLastRow As Long
With MyRange.Worksheet
lngLastRow = .Cells(.Rows.Count, MyRange.Column).End(xlUp).Row
.Range(.Cells(lngLastRow + 1, 1), .Cells(.Rows.Count, 1)).EntireRow.Delete
End With
End Sub
Call it like this
像这样称呼
GetLastRow wbAllData.Worksheets("MainSheet").Columns("A")
