访问2007 vba以在Excel 2007工作表中查找最后一行 [英] Access 2007 vba to find last row in Excel 2007 worksheet

问题描述

我在Access 2007数据库中有一些VBA代码将数据导出到Excel 2007文件。我有这个代码的问题：

  Sub GetLastRow（strSheet，strColum）
 Dim MyRange As Range 
 Dim lngLastRow As Long 
 
设置MyRange = Worksheets（strSheet）.Range（strColum&1）
 
 lngLastRow =单元格（65536，MyRange.Column ）.End（xlUp）.Row 
 lngLastRow = lngLastRow + 1 
行（lngLastRow&：1048576）。选择
 
 Selection.Delete Shift：= xlUp 
 End Sub 
  

问题是变量lngLastRow不计入属于标题行（这些已经是在excel文件中）在excel文件中，除非我手动打开Excel会话，然后继续运行代码。我想正确地解决这个问题，但是至少如果我可以包含一些代码来显示excel文件，所以它会自动出现，这样可以解决问题。但是看不到/可以这样做。

以下是调用上述功能的函数。

 函数CreateExcelData（）
'将要导出到Excel工作簿的数据复制
 Dim objExcel作为Excel.Application 
 Dim strTemplate As String 
 Dim strPathFile As String 
 Dim RowCount As Integer 
 Dim wbExported As Workbook'初始导出的数据
 Dim wbAllData As Workbook'Workbook将导出的数据复制到
 Dim rngUsed As范围'导出数据中的使用范围
 Dim Sheet As Worksheet 
 
'首先尝试GetObject，以防Excel应用程序已经打开。 
 On Error Resume Next 
 Set objExcel = GetObject（，excel.Application）
如果Err.Number<> 0然后
'GetObject返回错误，如果尚未打开
'所以使用CreateObject 
 On错误GoTo 0'Turnoff ASAP so error trapping is available 
 Set objExcel = CreateObject（Excel应用程序）
如果
 
 strTemplate =TEMPLATE.xlsm
 strPathFile = strPath& strTemplate 
 strPathFileFinal = strPath& strReportName& _& Mydat& .xlsm
 
 FileCopy strPathFile，strPathFileFinal 
 
'打开导出的数据工作簿并分配给变量
设置wbExported = objExcel.Workbooks.Open（strFilePath） 
 
'打开数据工作簿以接收导出的数据并分配给一个变量。 
设置wbAllData = objExcel.Workbooks.Open（strPathFileFinal）
 
'导出的数据
带有wbExported.Sheets（1）.UsedRange 
设置rngUsed = .Offset（1 ，0）_ 
 .Resize（.Rows.Count  -  1，.Columns.Count）
结束
 
带有wbAllData.Sheets（MainSheet）
 '复制导出的数据并粘贴到MainSheet的第一个空单元文件
 rngUsed.Copy 
 .Cells（Rows.Count，A）。End（xlUp）.Offset（1，0）.PasteSpecial粘贴：= xlPasteValues 
结束
 
调用GetLastRow（MainSheet，A）
 
 wbExported.Close 
 
 wbAllData。保存
 wbAllData.Close 
 
设置rngUsed = Nothing 
设置wbExported =没有
设置wbAllData =没有
设置objExcel =没有
 
 Kill strFilePath 
 
结束函数
  

解决方案

您的代码具有许多不合格和部分合格的工作表和范围的参考。这些将引用 ActiveWorkbook 或 ActiveSheet ，可能不是你想要的，并将导致不可预测的结果。

尝试这个重构

  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 
  pre> 
 
 
像这样调用
  GetLastRow wbAllData.Worksheets（ MainSheet）。列（A）
  
 
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

解决方案
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")


                        
这篇关于访问2007 vba以在Excel 2007工作表中查找最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！