Excel object: variable range instead of hard code

[Ivan]

Hi,

I have the codes for merging few Excel cells into one in access.

With xlsActiveSheet.Range("E6:G6")
.Select
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True

End With

It works fine, however, I need to make the loop instead of hard code for the
range E6:G6
because I may have more than one set cells need to be merged.
Which method which I use to solve this problem?

Thanks

 

[Guest]

Ivan,

The parameters for the Range are just strings formatted to refer to cells. I have used two differerent ways of doing what you want:

1) fabricate a string with the required syntax, something line

Dim sRange as String
Dim sUpperLeft as String 'RC format (R-Row Letter, C-Column Number)
Dim sLowerRight as String

sUpperLeft = <Some Cell reference, from a TextBox or MousePick perhaps>
sLoweRight = <Some Cell Reference, from a textBox or Mouse Pick perhaps>

sRange = sUpperLeft & ":" & sLowerRight

With xlsActiveSheet.Range(sRange)
..
..
..
End With

2) the other way is to use the fact that the Range method will take the corner cells directly. So do the above, but replace the With as follows:

With xlsActiveSheet.Range(sUpperLoft, sLowerRight)
..
..
..
End with

Hope this helps.

Victor

 

[Guest]

Ivan,

Ignore my method 2) -- this method works only on the corners are specified as Cells, I am not sure that the string parameters will work the way that you want it to.

Sorry about that.

Victor

 

[Jamie Collins]

...

I need to make the loop instead of hard code for the
range E6:G6
because I may have more than one set cells need to be merged.

Do you mean you want to pick up all the continuous cells (non-blank
rows) from E6:G6 to the bottom of the range? If you use SQL you can
specify the maximum number of rows and only the 'used range' will be
returned e.g.

SELECT *
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet1$E6:G65536]
UNION ALL
SELECT *
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet2$E6:G65536]
;

Jamie.

--

 

[Ivan]

Thanks for your help.
Does it have any easier way to convert the Row number to English Character ?
such as I want to refer row 27, it will convert to the cell reference "AA"
....

Thanks.

Ivan,

Ignore my method 2) -- this method works only on the corners are specified
as Cells, I am not sure that the string parameters will work the way that
you want it to.

Sorry about that.

Victor

Ivan,

The parameters for the Range are just strings formatted to refer to cells.

I have used two differerent ways of doing what you want:

1) fabricate a string with the required syntax, something line

Dim sRange as String
Dim sUpperLeft as String 'RC format (R-Row Letter, C-Column Number)
Dim sLowerRight as String

sUpperLeft = <Some Cell reference, from a TextBox or MousePick perhaps>
sLoweRight = <Some Cell Reference, from a textBox or Mouse Pick perhaps>

sRange = sUpperLeft & ":" & sLowerRight

With xlsActiveSheet.Range(sRange)
.
.
.
End With

2) the other way is to use the fact that the Range method will take the

corner cells directly. So do the above, but replace the With as follows:

 

[Ivan]

thanks, sorry I didn't write it clear.

Actually, I have an Access database want to output to excel ..

...

I need to make the loop instead of hard code for the
range E6:G6
because I may have more than one set cells need to be merged.

Do you mean you want to pick up all the continuous cells (non-blank
rows) from E6:G6 to the bottom of the range? If you use SQL you can
specify the maximum number of rows and only the 'used range' will be
returned e.g.

SELECT *
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet1$E6:G65536]
UNION ALL
SELECT *
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet2$E6:G65536]
;

Jamie.

--

 

[Jamie Collins]

Actually, I have an Access database want to output to excel

INSERT INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet1$E6:G65536]
SELECT *
FROM MyTable
WHERE MyKeyCol = 99
;

INSERT INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].[MySheet2$E6:G65536]
SELECT *
FROM MyTable
WHERE MyKeyCol = 98
;

Jamie

--