Excel Automation from Access Problem

healdem · #1 (**permalink**) 01-07-05, 06:47

I am creating a spreadhseet based on data from Jet Tables driven by a form in Access using VBA. The extract works fine. However I'm having a problem with retrospectively deleting columns in the spreadsheet. Having created the Excel sheet I then apply formatting (lines etc). As part of the formatting process I want to be able to delete redundant columns. There may be one column in block of 4 which for 'prettiness' needs to be deleted. I can't get the Excel automation object to do this.

The problem relates to the fact that I have merged cells in a block of 4 columns, when I try to programmatically delete one of those columns, vba deletes the 4 columns, not the selected column. Its something to do with the range select. If I don't merge these cells then the vba works as expected. So my question is has anyone else encountered this, and if so is there a solution.

envionment is XP Pro SR1 with Office 2002 build 6501

DavidCoutts · #2 (**permalink**) 01-07-05, 07:49

Hi Healdem,

Excel does this automatically anyway,
Try it manually and youll see what i mean,

The onlyway i have found round this is to unmerge the cells, I tend to use Centre accross selection to stop this problem,

this code will get rid of your merges

Code:

Sub Test()
    Dim rng As Range
    
    For Each rng In UsedRange
        If rng.MergeCells = True Then
            rng.MergeCells = False
        End If
    Next rng
End Sub

David

healdem · #3 (**permalink**) 01-07-05, 08:33

Hi David
Thanks for the reply and suggestion.

I'm trying to avoid having to re-write the Access VBA, I can switch off the cell merge, or delay it untill after the format, but it gets very messy, and being a wimp I'm trying to avoid having to do this.

The really maddening thing is that if I use the Excel spreadhseet I can happily do what the code suggests it should do. IE select a column and delete that column, with Excel happily handling merged & non merged cells.

After all the code I use was nabbed fro a recorded macro in Excel and repackaged. So I'm just hacked off that the VBA automation object doesn't support this functionality.

DavidCoutts · #4 (**permalink**) 01-07-05, 08:48

[QUOTE=healdem]
The really maddening thing is that if I use the Excel spreadhseet I can happily do what the code suggests it should do. IE select a column and delete that column, with Excel happily handling merged & non merged cells.
[\QUOTE]

I cant but im using 2000

i could write a fudge for you if you want,
i basically use excel everyday, and i completly hate merged cells for when i have to alter data the way i want, they are somtimes the bane of my existance

My principle suggestion would be instead of merging the cells why not do something like this

rng.HorizontalAlignment = xlCenterAcrossSelection
Then when the Column is delete no adverse effects happen

healdem · #5 (**permalink**) 01-07-05, 09:25

Nice one David
Did the job
I take back, nearly, everything I've said about you over the last 2 months...

Thanks
Matt

shades · #6 (**permalink**) 01-07-05, 09:31

General principle:

Do NOT use merged cells in Excel, unless under threat of job loss

Center across selection does the same without any of the headaches.