Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Merging excel cells from VBA in access

Posted on 2004-10-29
6
Medium Priority
?
1,708 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
Comment
Question by:Lucas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Bat17 earned 2000 total points
ID: 12445701
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:Lucas
ID: 12445853
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
ID: 12445991
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 13

Author Comment

by:Lucas
ID: 12446553
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:Lucas
ID: 12446567
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:Lucas
ID: 12446601
Ok i figured it out.  But i did use your advise.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

597 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