Merging excel cells from Access 2

[robojeff]

I am attempting to merge a couple of cells in excel from access after populating these cells with automation but I keep getting a "Select method of Range class failed" error.

I got the following code from a macro that I created in excel but this macro must need some additional changes to work from within access:

Set objXLSheet = objXLBook.Worksheets("Sheet2")

With objXLSheet
.Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge

End With

What am I missing?

Thanks

 

[PHV]

you have to use full qualified references, ie replace this:
Selection
with this:
.Application.Selection

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV-

I tried the following:

Set objXLSheet = objXLBook.Worksheets("Sheet2")

With objXLSheet
.Range("A1:E1").Select
With Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Application.Selection.Merge

End With

and received a Compile error: Method or data member not found...

I am guessing that I did not modify this correctly or that I am still missing something...

==================
PHV (MIS) 20 Feb 09 10:28
you have to use full qualified references, ie replace this:
Selection
with this:
.Application.Selection

 

[PHV]

Set objXLSheet = objXLBook.Worksheets("Sheet2")
With objXLSheet
    .Range("A1:E1").Select
    With [highlight].[/highlight]Application.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    [highlight].[/highlight]Application.Selection.Merge
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV-

Your suggestion works great on the first batch of cells that I want to merge but chokes up with a "Select method of Range class failed" message when I attempt to merge another set of cells on the next sheet with the same code:

.Range("A1:N1").Select
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Application.Selection.Merge

 

[PHV]

Did you reset objXLSheet to the proper sheet object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

First, I had it set up within the "with" of this objXLSheet as shown below:

Set objXLSheet = objXLBook.Worksheets("Sheet2")
With objXLSheet

.Columns("A:N").ColumnWidth = 14
.Columns("A:N").HorizontalAlignment = xlCenter
.Columns("A:N").WrapText = True
.Cells(intLoop, 1).Font.Bold = True
.Cells(intLoop - 1, 1).Font.Bold = True
.Cells(intLoop, 1) = "Problem Code:"
.Range("A1:N1").Select
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Application.Selection.Merge

and I changed to the following:

Set objXLSheet = objXLBook.Worksheets("Sheet2")
With objXLSheet
.Range("A1:N1").Select
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Application.Selection.Merge

In both cases, I get a "set method of range class failed" message

By the way, Sheet2 does exist as a worksheet on the spreadsheet...

===========


PHV (MIS) 20 Feb 09 15:53
Did you reset objXLSheet to the proper sheet object ?

 

[PHV]

Sheet2 does exist as a worksheet on the spreadsheet
So, what do you want to do ?

 

[SkipVought]

Please use the Code TGML tag, rather than the Quote TGML tag, when posting CODE.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

PHV (MIS) 21 Feb 09 10:45
Sheet2 does exist as a worksheet on the spreadsheet
So, what do you want to do ?

I am attempting to merge and center the text in the cells on sheet2 as I did on sheet1 but using the same code bombs with a "set method of range class failed" message even after I reset objXLSheet to the proper sheet object (Sheet2) and I have verified the Sheet2 does exist...

what am I doing wrong?

thank you for your patience...

Jeff

 

[henniec]

Check if the name of sheet2 is 100% correct, no space before or after on work sheet tab. Activate your worksheet after you set the worksheet, so you are sure it is the active one you are working with and facing you when you step through.

Always set activate a worksheet after you set it.

Hennie

 

[robojeff]

Thanks Hennie-

I was not activating the worksheet after I set it...