Export data from visible datasheet columns in order displayed

Dale Fye
Dale Fye used Ask the Experts™
on
I've got a couple of forms which display data in subforms which display query results in a datasheet format.

I don't normally like datasheets, but my client has about a dozen people using this particular application, and each of them is only interested is about 10 of the 30 fields in this table.  So they wanted a way for their users to hide and rearrange the columns and the datasheet seemed like the most appropriate option.

Now they want their users to be able to export the filtered recordset that is currently displayed in the datasheet to an Excel spreadsheet, but they only want to display the fields that are visible in the subform, and they want them displayed in the column order the user has defined by dragging the columns.

So, I'm looking for a way to build a dynamic SQL string which selects only those fields that are displayed and in the order they are displayed.  I know I can use the columnHidden property of the controls collection to determine whether the column is visible or not.  But I've been unable to identify the appropriate property to loop through the collection and identify the sequence that they are displayed in, so I can build the SQL string.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:

Dim ctl As Control
For Each ctl In Me.subformControlName.Form.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.ColumnHidden Then
            Debug.Print ctl.Name
        End If
    End If

Next
Top Expert 2016

Commented:
oh, you  need the visible columns


Dim ctl As Control
For Each ctl In Me.subformControlName.Form.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.ColumnHidden=0 Then
            Debug.Print ctl.Name
        End If
    End If

Next
Top Expert 2016
Commented:
better use this if there are combo boxes

Dim ctl As Control
For Each ctl In Me.subformControlName.Form.Controls
    If ctl.ControlType <> acLabel Then
        If ctl.ColumnHidden=0 Then
            Debug.Print ctl.Name
        End If
    End If

Next
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Cappy (It's Ray, right)?

I already had that part, what I'm trying to figure out is the sequence (left to right) of the columns, as they are displayed in the subform.

If possible, I'd prefer to iterate through these in sequence, but could create an array and populate the specific array element based on the displayed column sequence.  Then loop through the array to build the SQL string.
Most Valuable Expert 2014
Commented:
Details here
http://msdn.microsoft.com/en-us/library/aa224067(v=office.11).aspx
There is a ColumnOrder property as well as ColumnHidden

Sample attached that loads the name, columnhidden and columnorder properties to an array.
Then messageboxes the resulting strings
columns.mdb
Top Expert 2016

Commented:
i did not notice that it was you fyed,

there is the solution posted by Nick
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, guys.  Precisely what I needed.

I so rarely use datasheets that this was really bugging me.  I knew there had to be a way to find this.
Most Valuable Expert 2014

Commented:
No biggie.  Glad to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial