asked on

Merge cells with VBA in an OLE object

What is the syntax to merge two cells in an Excel OLE object on an Access form?

Thanks,

Michael

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UniqueData

ASKER

I tried the following:

With Me.Spreadsheet0.Object.Sheets(1)
.Cells(1, intColNum) = strLotNumber
.Cells(2, intColNum) = dtInspectionDate
.Cells(3, intColNum) = "Actual"
.Cells(3, intColNum + 1) = "Target"
strRange = "A" & intColNum & ":" & "B" & intColNum
.Range(strRange).Select
.Selection.Merge
strRange = "A" & intColNum + 1 & ":" & "B" & intColNum + 1
.Range(strRange).Select
.Selection.Merge
End With

but on the line .Selection.Merge i get the error: 'Object doesn't support this property or method'.

Again, this is an OLE object within an Access form if that matters.

Michael

UniqueData

ASKER

ahh, i got it with your help.

I just changed .range(strRange).select
to
.range(strRange).Merge

thanks

UniqueData

ASKER

one more if you don't mind, what about centering the text after it is merged?

Rey Obrero (Capricorn1)

try

.range(strRange).columns.HorizontalAlignment = xlcenter

Rey Obrero (Capricorn1)

or
.range(strRange).HorizontalAlignment = xlcenter

UniqueData

ASKER

perfect, except access didn't understand xlcenter so I opened Excel's vba and found the value -4108

Thanks again