Select range in Excel from Access

[RevenueSwede]

Hi!

I create an Excelfile from Access, no problem. My knowledge about Excelprogramming isn't the best.

I have a starting cell, column 4 (D) and starting row 7. I want to select d7 to j7 and make it green. I have quite many columns from D to DG. I have found that I can use:
wks.range("D7","J7").select but I don't want to use the letters becuase I have a counter for the column and row.

wks.cells(row, col).select only let me select one cell.

Any ideas?

 

[AlexCuse]

I believe if you want to select the whole column it is something like this:

.ActiveSheet.Columns("C:C").Select

And if you want a range:

.ActiveSheet.Range("C1:C17").Select

You could of course replace "C:C" with Counter1:Counter2, or something like that.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

forgot, all of this is WITH wks (if wks is a workbook. if it's a worksheet, you'd just need wks.Columns or wks.Range)



Ignorance of certain subjects is a great part of wisdom

 

[RevenueSwede]

But how do I use a counter on the columns, the letters? I have a recordset which gives me a resultset of the edcuational periods for batallions, platoons etc. start date and end date. I need to count how many months in each period, set starting point for selection and end point for selection then format the area in a certain color.

The only part which I'm not getting is how to select a range. I know the starting cell.

 

[RevenueSwede]

wks is a worksheet sorry

 

[AlexCuse]

hm, I think you mean something like this?

Dim Mnths as Integer
Mnths = rs.blah.blah(monthcntfield)

wks.Range("C1:C" & Cstr(1 + Mnths)).Select

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[RevenueSwede]

Ah I'm painting horizontally

numberOfMonths = DateDiff bla bla

wks.Range("D7:" & column(?)& "7").select

D4 is jan-2008 then each column is a month, so 1/1/2008 to 4/31/2008 is D7 to G7. I know the row index all the time because I loop the recordset.

Do you see my "problem"?

 

[RevenueSwede]

It's Friday afternoon here.. "D4 is jan-2008 then ". It should be "D7 is jan-2008 then ".

 

[AlexCuse]

If always starting with D, you could create an array containing the next (12?) column names.

Then do something like this:

wks.Range("D7:" & ArrayName(recordset.Fields.Count - 1)& "7").select

So what you are doing is storing the next n columns that are possible, and these will be directly related to the count of recordset fields (-1, because array is zero-based). So if there is only 1 field in your recordest, you will select D. If there are 3, you will select D:F, and so on... Am I reading this right, and if so, does this make sense?

I hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[RevenueSwede]

Hmm no I'm not always starting on D7 and the worksheet spans to column DG which is 2016. Yeah planning ahead..

My friend said that in Excel you can translate the letter in the column to a number, have you heard of that?

 

[AlexCuse]

Will you always be starting at column D? I just tried to reference a column by number in Excel's macro recorder, and it did not like that. The only way I could see it working is to create a lookup table that matches each column with its' distance from starting column (hopefully always D!, or this could be hairy!). You then could use DLookup (based on the count of fields in your recordset) to return the name of the 'ending column' that you need from this table. Does that make sense?


Ignorance of certain subjects is a great part of wisdom

 

[RevenueSwede]

I solved it I found out that Excel Offset.

firstCell = "$D$7"

I have a another loop around all this..

Do While Not rsPeriod.EOF
startDate = rsPeriod!start
endDate = rsPeriod!end

If startDate < CDate("1/1/2008") Then
noOfMonths = DateDiff("m", CDate("1/1/2008"), endDate)
xls.Range(firstCell).Select
Else
noOfMonths = DateDiff("m", startDate, endDate)
noOfMonthsFromStart = DateDiff("m", CDate("1/1/2008"), startDate)
xls.Range(firstCell).Select
xls.ActiveCell.Offset(0, noOfMonthsFromStart).Select
End If
startCell = xls.ActiveCell.Address
xls.ActiveCell.Offset(0, noOfMonths).Select
endCell = xls.ActiveCell.Address
xls.Range(startCell, endCell).Select
With Selection.Interior
.ColorIndex = GetColor(rsPeriod!Category)
End With
rsPeriod.MoveNext
Loop

xls.Range(firstCell).Select
' -- move selection to next row
xls.ActiveCell.Offset(1, 0).Select
firstCell = xls.ActiveCell.Address

movenext in my other loop

 

[AlexCuse]

Nice! Glad its' working for you, and thanks for posting the solution you found

Ignorance of certain subjects is a great part of wisdom