MS Access Excel automation "Select method of rane class failed?

Question

0.00/5 (No votes)

See more:

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:

VB

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

Posted 23-Feb-12 8:01am

avianrand

Updated 23-Feb-12 8:02am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2012-02-23T08:14:00

Solution 1

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

Posted 23-Feb-12 8:14am

ZurdoDev

Comments

avianrand 23-Feb-12 15:52pm

Thanks, but neither of those solutions work. I've tried many variations on the "i" and even just hard coded things like Range("A1:B2"), etc. The problem doesn't seem to be in the way I'm referencing the cells but in the way I'm referencing the workbook or worksheet or something like that.

ZurdoDev 23-Feb-12 16:16pm

I don't know if it would make a difference but when I have done it in the past I believe xlApp has an OpenWorkbook method, or something like that, instead of using the GetObject() method. So, .Activate does not throw an error? Also, you may want to try and actually .Open() the workbook. Microsoft's documentation says that Office was never intended to be fully automated so some things really on the UI a lot and it may need to be actually open on the screen for Select to work.

avianrand · Answer 2 · 2012-02-23T13:53:00

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

VB

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