Identify which columns in a datasheet are selected
I'm exporting a datasheet to Excel via vba thanks to Nick67 and Capricorn1. In that code, I exempt those columns from the datasheet that have been hidden by the user. Now I would like to take that one step further and only export the columns that have selected by the user. For example, if the user selects several columns that are next to each other and only want to export them.
Is there a way to identify which columns are selected? I assume so, since the Find functionality works on "selected" columns. I just have not been able to identify the specific property of the datasheet or of the controls collection to identify.
Is there a way to identify which columns are selected? I assume so, since the Find functionality works on "selected" columns. I just have not been able to identify the specific property of the datasheet or of the controls collection to identify.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi @fyed,
I've never played much with the datasheet.
Are the columns selected always required to be contiguous?
I can't make it do anything but that.
I think this is what you need
It's from the Access 2010 help
We can already determine order, so if this spits out, like I think it will, where the selection starts and how many columns it is, it should give you what you need
I've never played much with the datasheet.
Are the columns selected always required to be contiguous?
I can't make it do anything but that.
I think this is what you need
It's from the Access 2010 help
Public Sub SetHeightWidth(ByRef frm As Form)
Dim lngNumRows As Long
Dim lngNumColumns As Long
Dim lngTopRow As Long
Dim lngLeftColumn As Long
Dim strMsg As String
' Form is in Datasheet view.
If frm.CurrentView = 2 Then
' Number of rows selected.
lngNumRows = frm.SelHeight
' Number of columns selected.
lngNumColumns = frm.SelWidth
' Topmost row selected.
lngTopRow = frm.SelTop
' Leftmost column selected.
lngLeftColumn = frm.SelLeft
' Display message.
strMsg = "Number of rows: " & lngNumRows & vbCrLf
strMsg = strMsg & "Number of columns: " _
& lngNumColumns & vbCrLf
strMsg = strMsg & "Top row: " & lngTopRow & vbCrLf
strMsg = strMsg & "Left column: " & lngLeftColumn
MsgBox strMsg, vbInformation
End If
End Sub
We can already determine order, so if this spits out, like I think it will, where the selection starts and how many columns it is, it should give you what you need
Hi Dale
You can use the form's SelLeft and SelWidth properties to determine the left-most column and the number of columns selected. You might need to play with it a bit, because in my brief experimentation I discovered that when the first column was selected, SelLeft was 2 (!!!)
Assuming your users are able to drag columns to change their order, the next problem will be identifying which controls are in the selected columns. As far as I can tell, there is no easy way to convert a column index to a control name or field name. For this you can use each control's ColumnOrder property. Loop through all the eligible controls and populate an array with the control names using ColumnOrder as an index. Then you can simply loop from SelLeft to SelLeft+SelWidth-1 to determine the fields to be exported.
You will probably also want to omit any hidden columns. For this you can check each control's ColumnHidden property.
Good luck!
Graham
You can use the form's SelLeft and SelWidth properties to determine the left-most column and the number of columns selected. You might need to play with it a bit, because in my brief experimentation I discovered that when the first column was selected, SelLeft was 2 (!!!)
Assuming your users are able to drag columns to change their order, the next problem will be identifying which controls are in the selected columns. As far as I can tell, there is no easy way to convert a column index to a control name or field name. For this you can use each control's ColumnOrder property. Loop through all the eligible controls and populate an array with the control names using ColumnOrder as an index. Then you can simply loop from SelLeft to SelLeft+SelWidth-1 to determine the fields to be exported.
You will probably also want to omit any hidden columns. For this you can check each control's ColumnHidden property.
Good luck!
Graham
Premium Content
You need an Expert Office subscription to comment.Start Free Trial