Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Unanswered: Excel Automation from Access Problem

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    [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
    Last edited by DavidCoutts; 01-07-05 at 09:51. Reason: formatting

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Nice one David
    Did the job
    I take back, nearly, everything I've said about you over the last 2 months...

    Thanks
    Matt

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by healdem
    Nice one David
    Did the job
    I take back, nearly, everything I've said about you over the last 2 months...

    Thanks
    Matt
    nah you dont really do you

    Quote Originally Posted by shades
    Do NOT use merged cells in Excel, unless under threat of job loss
    id say more like your life

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •