excel - 修复导出的 Excel 文件的 VBA 访问中的运行时错误？

Question

我试图找出我的代码存在的问题。

我正在尝试将文件导出到 excel 并使用条件格式删除唯一的。我第一次运行它时效果很好。

如果我在文件夹中已经有文件的情况下再次运行它，我最终会在该行出现“运行时错误 Method 'Rows' of object_gobal' failed”lrow = Cells(Rows.Count, 1).END(xlUp).Row尝试跳过此行只会给我相同类型的错误下一行。

我希望能够运行它并让它覆盖或创建文件的第二个版本而不会出现错误，并继续我要求它执行的条件格式。

任何帮助将不胜感激！

'Build Retail Bump File Pass Through Query
db.QueryDefs.Delete "qryRetailBumpFile"
Set qdfPassThrough = db.CreateQueryDef("qryRetailBumpFile")
qdfPassThrough.Connect = "ODBC;DSN=SupplyChainMisc;Description=SupplyChainMisc;Trusted_Connection=Yes;DATABASE=SupplyChain_Misc;"
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.Sql = StrSQL

'Create Excel File
Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim lrow As Long

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryRetailBumpFile", "\\page\data\NFInventory\groups\CID\Company Bumps(WORK IN PROGRESS)\RETAILS\Test\" & strBrand & "\" & strSeason & "\" & strPrefix & "\" & strPrefix & " " & Format(Now(), "MM-DD-YY") & " Retail.xlsx", True

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("\\page\data\NFInventory\groups\CID\Company Bumps(WORK IN PROGRESS)\RETAILS\Test\" & strBrand & "\" & strSeason & "\" & strPrefix & "\" & strPrefix & " " & Format(Now(), "MM-DD-YY") & " Retail.xlsx")
appExcel.Visible = True


With myWorkbook.worksheets("qryRetailBumpFile")
lrow = Cells(Rows.Count, 1).END(xlUp).Row <-- Error here

.Range("A2:A" & lrow).Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlUnique

With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 5
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
.Range("A1").Select

'Filter Color and delete
.Range("A1:M" & lrow).AutoFilter
ActiveSheet.Range("A2:M" & lrow).AutoFilter Field:=1, Criteria1:=RGB(5, 0, 0), Operator:=xlFilterCellColor

With ActiveWorkbook.Worksheets("qryRetailBumpFile")
    lr = .Cells(.Rows.Count, 1).END(xlUp).Row
    If lr < 2 Then Exit Sub
    .Cells(1, 1).Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.Range("A1:M" & lrow).AutoFilter
End With
myWorkbook.Close

Set appExcel = Nothing
Set myWorkbook = Nothing
db.Close

谢谢！-德克

标签： excelvbams-access