• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1975
  • Last Modified:

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?

0
Lucas
Asked:
Lucas
  • 4
  • 2
1 Solution
 
Bat17Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now