MS Access Excel自动化" rane类的选择方法失败吗? [英] MS Access Excel automation "Select method of rane class failed?

问题描述

我已尝试根据我在网上找到的内容以多种不同方式对此进行更改，但无法使其正常工作.发生错误的位置是"xlWkSht.Rows(Trim(Str(i))&:& Trim(Str(i))).Select"行.任何帮助，将不胜感激.这是我的代码:

I''ve tried changing this around so many different ways based on what I''ve found online and just can''t get it to work. The line "xlWkSht.Rows(Trim(Str(i)) & ":" & Trim(Str(i))).Select" is where the error occurs. Any help would be appreciated. Here''s my code:

Dim xlApp As Excel.Application
Dim xlWkBk As Excel.Workbook
Dim xlWkSht As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlWkBk = GetObject(strSaveToPathAndFileName)
Set xlWkSht = xlWkBk.Worksheets(1)


'Find the last row
Dim ExcelLastCell As Object, iLastDataRow As Integer
Set ExcelLastCell = xlWkSht.Cells.SpecialCells(xlLastCell)
iLastDataRow = ExcelLastCell.row

Dim i As Integer
For i = 1 To iLastDataRow
    If Trim(xlWkSht.Cells(i, 1)) <> "" Then
        If Trim(xlWkSht.Cells(i, 8).Value) = "UNKNOWN" Then

            xlWkSht.Activate
            xlWkSht.Rows(Trim(Str(i)) & ":" & Trim(Str(i))).Select
            'xlWkSht.Range("A" & Trim(Str(i)), ":J" & Trim(Str(i))).Select
            xlWkBk.Selection.Font.ColorIndex = 3
            xlWkBk.Selection.Font.Bold = True

        End If
    End If
Next i

推荐答案

您是否尝试过从Str(i)移除Str?如果我在VBA中添加Str会出错，但是没有它会起作用.由于仅选择1行，您可以尝试做的另一件事是执行Rows(i).选择

Have you tried taking the Str off of Str(i)? If I add Str in VBA it errors but it works without it. The other thing you can try since you are only selecting 1 rows is to do Rows(i).Select

ryanb31关于Open方法的建议，促使进行进一步调查.这是最终可以正常运行的代码:

ryanb31''s suggestion about an Open method, prompted further investigation. This is the code that finally works well:

Dim xlApp As Excel.Application
Dim xlWkBk As Excel.Workbook
Dim xlWkSht As Excel.Worksheet


Set xlApp = CreateObject("Excel.Application")
'Set xlWkBk = GetObject(strSaveToPathAndFileName)
'Set xlWkSht = xlWkBk.Worksheets(1)

xlApp.Workbooks.Open strSaveToPathAndFileName
Set xlWkSht = xlApp.Worksheets(1)

'Find the last row
Dim ExcelLastCell As Object, iLastDataRow As Integer
Set ExcelLastCell = xlWkSht.Cells.SpecialCells(xlLastCell)
iLastDataRow = ExcelLastCell.row

'xlApp.Visible = True

Dim i As Integer
For i = 1 To iLastDataRow
    If Trim(xlWkSht.Cells(i, 1)) <> "" Then
        If Trim(xlWkSht.Cells(i, 8).Value) = "UNKNOWN" Then

            xlWkSht.Activate
            'xlWkSht.Rows(Trim(Str(i)) & ":" & Trim(Str(i))).Select
            xlWkSht.Rows(i).Select
            xlApp.Selection.Font.ColorIndex = 3
            xlApp.Selection.Font.Bold = True

        End If
    End If
Next i

xlWkSht.Cells(1, 1).Select
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Workbooks.Close

Set xlWkSht = Nothing
Set xlWkBk = Nothing
Set xlApp = Nothing

这篇关于MS Access Excel自动化" rane类的选择方法失败吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！