Access 2007 vba to find last row in Excel 2007 wor

问题:

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:

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.

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

回答1:

Your code has a number of unqualified and partially qualified references to Worksheets and Ranges. These will refer to the ActiveWorkbook or ActiveSheet, probably not wjhat you want, and will cause unpredictable results.

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")