Merging excel cells from VBA in access

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?

LVL 13
LucasMS Dynamics DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bat17Connect With a Mentor Commented:
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
 
LucasMS Dynamics DeveloperAuthor Commented:
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
 
Bat17Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LucasMS Dynamics DeveloperAuthor Commented:
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
 
LucasMS Dynamics DeveloperAuthor Commented:
it should be:

.range(.cells(row,col),.cells(row,col+1)).Merge True   <-- THIS IS WHAT MS HELP FILE HAS
0
 
LucasMS Dynamics DeveloperAuthor Commented:
Ok i figured it out.  But i did use your advise.

0
All Courses

From novice to tech pro — start learning today.