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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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:
Set mysht = mywrk.Worksheets(1)
row = 6
col = 1
'create and open the recordset and the Excel report
Set rs = CurrentDb.OpenRecordset("S
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),.ce
.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
-----
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:
Set mysht = mywrk.Worksheets(1)
row = 6
col = 1
'create and open the recordset and the Excel report
Set rs = CurrentDb.OpenRecordset("S
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),.ce
.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
ASKER
it should be:
.range(.cells(row,col),.ce
.range(.cells(row,col),.ce
ASKER
Ok i figured it out. But i did use your advise.
ASKER
myExcel.ActiveSheet.Range(
ERROR: APPLICATION DEFINED OR OBJECT DEFINED ERROR
My declaration:
dim myExcel as Object
set myExcel = CreateObject("Excel.Applic
WHY???