Define Excel Range from VBA in Access

[rachelkm2]

Hi All,

I have a procedure which allows the user to export a filtered recordset from an Access subform to Excel (using the CopyFromRecordset method). I am in the process of making sure the resulting excel worksheet is formatted appropriately.

I would like to add borders to the exported records (not the entire sheet), but the number of records will vary from export to export, so I cannot set a range based on known start and end - ie ("A3:A100").

Can anyone tell me how I can set my range to include all cells which contain data?

I've come across examples which show how to identify the last row with data - ie .End(xlDown) - but I can figure out how to use that value in the range.

Thanks!

 

[SpentGeezer]

Greeting.

I had a bit of a plpay around with it. This should get you started. Save the excel file to c:\

SPENT

 

[boblarson]

Greeting.

I had a bit of a plpay around with it. This should get you started. Save the excel file to c:\

SPENT

Here's a little more efficient code than this:

'get the number of columns
CellVal = objSheet.cells(1, theCOL).Value
Do Until CellVal = ""
    maxCOLS = maxCOLS + 1
    theCOL = theCOL + 1
    CellVal = objSheet.cells(1, theCOL).Value
Loop
 
'get the number of rows
CellVal = objSheet.cells(theROW, 1).Value
Do Until CellVal = ""
    maxROWS = maxROWS + 1
    theROW = theROW + 1
    CellVal = objSheet.cells(theROW, 1).Value
Loop

Dim rng As Object
 
Set rng = objSheet.UsedRange
 
rng.Select
 
With objXL.Selection.Borders(7) ' constant for xlEdgeLeft Is 7 because we are using late binding
.LineStyle = 1 ' xlContinuous
.Weight = -4138 'xlMedium
.ColorIndex = -4105 'xlAutomatic
End With

You can use the Excel Macro recorder to get the basic syntax and then you just have to connect it correctly to the objXL object. And using late binding (not setting a reference to Excel) means you also have to supply the constant values which you can get from the Excel VBA Window - Immediate Window by typing in ?xlContinuous and hitting enter (and the same for the rest).

So UsedRange is good for getting what is used on the sheet. You can also use UsedRange.Address to get the actual range address and then you can split it to get each part if necessary.

 

[SpentGeezer]

Arrghh my noobism on full display!!

Not bad Bobby, another tip from the master to put in my notebook.