Merging Excel cells using VBA

[drjekyll325]

Hi everyone,

I need to use Access 97 to generate an Excel spreadsheet of nursing schedules. The spreadsheet needs to be formatted in a particular way. I am exporting a data table from Access, and then using VBA to open and modify the spreadsheet.

So far, so good. All of my coded formatting changes work great. All, that is, except for the "merge cells" functionality. I have tried several different methods, none of which give me any compilation or run-time errors, although they will throw up the usual warning message if more than one cell is field in. When I open the spreadsheet after my code has finished, the cells are not merged, although all the other formatting changes I made were successful.

Here are some of the methods I've used: (objSchedule is my worksheet object)

1)
objSchedule.Range("G1:J1").Merge

2)
objSchedule.Range("G1:J1").MergeCells = True

3) --copied and pasted from an excel macro --
objSchedule.Range("G1:J1").Select
objSchedule.Range("G1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With


Does anyone have any suggestions? What am I overlooking?

Chris

 

[Philem]

Hullo. Try this modification to method 3:

objSchedule.Range("G1:J1").Select
Selection.Merge

That should get you what you want. I noticed that in Method 3, you were selecting a range, then activating a cell. Not sure if that would cause a problem, but best to leave the activation out.

HTH

P

 

[drjekyll325]

Philem,

Thanks for the idea. I gave it a try - even though it's just Method #1 written in two lines instead of one - but got no results. The behavior was exactly the same as described above.

Any more ideas?

Chris