Solved

Merging excel cells from VBA in access

Posted on 2004-10-29
1,400 Views
Last Modified: 2008-03-10
I want to create a custom report in excel.  I'm extracting the values of a recordset to the report.

I want to merge some cells but i can't because it doesn't work.

....
with myExcel.ActiveSheet
   .cells(1,2).Merge true     <----THIS DOESN'T WORK...WHY???
end with

with myExcel.ActiveSheet
   .range("A1","B1").merge true   <---THIS WORKS...WHY???
end with

How come the merge method works with range property and not with cells property?  
I have to loop through the recordset and add records to each row below, and i can't do it like this either

with myExcel.ActiveSheet
   .range("A & row & ","B & row & ").Merge true
   row = row + 1
end with

WHY?

0
Question by:lucas911
    6 Comments
     
    LVL 9

    Accepted Solution

    by:
    The first example is only Merging one Cell!!
    to merge a range of cells you need
    Range(Cells(1, 1), Cells(5, 3)).merge true

    HTH

    Peter
    0
     
    LVL 13

    Author Comment

    by:lucas911
    This doesn't work:

    myExcel.ActiveSheet.Range(Cells(1, 1), Cells(5, 3)).Merge True

    ERROR: APPLICATION DEFINED OR OBJECT DEFINED ERROR

    My declaration:

    dim myExcel as Object

    set myExcel = CreateObject("Excel.Application")

    WHY???
    0
     
    LVL 9

    Expert Comment

    by:Bat17
    Can you post the rest of your code please?
    There does not appear to be anything wrong with what you have but you dont show wether you are opening a workbook or creating a new one.

    Peter
    0
     
    LVL 13

    Author Comment

    by:lucas911
    I tried to do it in a different way:
    -----
    Dim myExcel As New Excel.Application
    Dim mywrk As Excel.Workbook
    Dim mysht As Excel.worksheet
    Dim rs As DAO.Recordset
    Dim row As Long, col As Long

    Set mywrk = myExcel.Workbooks.Open("c:\rbc excel report.xls", , True)
    Set mysht = mywrk.Worksheets(1)
    row = 6
    col = 1

    'create and open the recordset and the Excel report
    Set rs = CurrentDb.OpenRecordset("Select * from zCritical_illness_RBC where datenotified between " & "#" & Format(Me.txtfrom, "medium date") & "#" & " and " & "#" & Format(Me.txtToDate, "medium date") & "#" & "")

    If rs.EOF Then
        MsgBox "There is no data found for this time period.  Select a different time period."
        Exit Sub
    End If

    'open the excel report and populate the rows and columns

    With myExcel.mysht
        Do Until rs.EOF
           .range.(cells(row,col),.cells(row,col+1)).Merge True   <-- THIS IS WHAT MS HELP FILE HAS
           .Cells(row, col).Value = rs!certnum
            row = row + 1
            rs.MoveNext
        Loop
    End With

    rs.Close
    Set rs = Nothing
       
    Set myExcel = Nothing
    Set mywrk = Nothing
    Set mysht = Nothing
    ----------------------------
    hope you can help
    0
     
    LVL 13

    Author Comment

    by:lucas911
    it should be:

    .range(.cells(row,col),.cells(row,col+1)).Merge True   <-- THIS IS WHAT MS HELP FILE HAS
    0
     
    LVL 13

    Author Comment

    by:lucas911
    Ok i figured it out.  But i did use your advise.

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    845 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now