Merge Cells in Excel by Using VBA in Access

[March]

I have to write VBA to merge cells in Excel.

The code need to be in Access.

I write the code to open excel file. Once the file opens, I copy record set
in Access to excel. (Everything works well.)


However, I don't know how to write the code to merge cells.

I use Cells(1,1) instance of range("A1"). I use "Cells" because it is easy
for me when I update the file.

I use the code below to find last used row and col.

Dim xApp as Object

Set xApp = CreateObject("Excel.Application")

lastUsedRow = xApp.sheets("sheet1").UsedRange.Rows.Count

lastUsedCol = xApp.sheets("sheet1").UsedRange.Columns.Count

I want to know how to reference
col(1) = A, col(2) = B, ..., col(26) = Z, col(27) = AB,..., col(52) = AZ,...
and so on.

I have to fill data across the column up to "DE" and will have more.

Column is only problem that I have to deal with.

Please give me suggestion.


March

 

[Larry Daugherty]

You're working an Excel issue so work it in Excel, not Access. Write
and debug your macro in Excel. When it works properly then put the
VBA into the Access module and fire for effect.

HTH

 

[James A. Fortune]

I have to write VBA to merge cells in Excel.

The code need to be in Access.

I write the code to open excel file. Once the file opens, I copy record set
in Access to excel. (Everything works well.)


However, I don't know how to write the code to merge cells.

I use Cells(1,1) instance of range("A1"). I use "Cells" because it is easy
for me when I update the file.

I use the code below to find last used row and col.

Dim xApp as Object

Set xApp = CreateObject("Excel.Application")

lastUsedRow = xApp.sheets("sheet1").UsedRange.Rows.Count

lastUsedCol = xApp.sheets("sheet1").UsedRange.Columns.Count

I want to know how to reference
col(1) = A, col(2) = B, ..., col(26) = Z, col(27) = AB,..., col(52) = AZ,...
and so on.

I have to fill data across the column up to "DE" and will have more.

Column is only problem that I have to deal with.

Please give me suggestion.


March

Sample code in Access to merge cells in an Excel spreadsheet:

'Merge cells for label above NC Count labels
objXL.Range("V1:W1").Select
With objXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With

Remember that a Reference must exist for the Excel Object Library for
Access to understand xlCenter and xlBottom.

James A. Fortune
(e-mail address removed)