We help IT Professionals succeed at work.

Export filtered subform results and only visible columns to excel.

140 Views
Last Modified: 2017-03-27
I have a small database the uses 1 main form with a subform. Users can filter that subform with the normal filters at the column heads along with hiding columns. I have the code written to export the filtered results and works well, but it includes all columns whether it is hidden or not. How can I only include visible columns?

This is what I am using so far
Function MakeExcel(OutputName As String, ByVal strMainFormName As String, Optional ByVal strSubformName As String)
Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim intCount As Integer
    Dim strSQL As String
    Dim bolWithFilterOn As Boolean
    Dim strTempQryDef As String
    Dim strRecordSource As String
   
    strTempQryDef = "DonorExport"
   
    If strSubformName = "" Then
        bolWithFilterOn = Forms(strMainFormName).Form.FilterOn
        strRecordSource = Forms(strMainFormName).Form.RecordSource
    Else
        bolWithFilterOn = Forms(strMainFormName)(strSubformName).Form.FilterOn
        strRecordSource = Forms(strMainFormName)(strSubformName).Form.RecordSource
    End If
    If InStr(strRecordSource, "SELECT ") <> 0 Then
        strSQL = strRecordSource
    Else
        strSQL = "SELECT * FROM [" & strRecordSource & "]"
    End If
   
    ' just in case our sql string ends with ";"
    strSQL = Replace(strSQL, ";", "")
    Set rs = Forms(strMainFormName)(strSubformName).Form.RecordsetClone
        For intCount = 0 To rs.Fields.Count - 1

        Next intCount
           
    If bolWithFilterOn Then
        strSQL = strSQL & _
            IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
            IIf(strSubformName = "", Forms(strMainFormName).Form.Filter, Forms(strMainFormName)(strSubformName).Form.Filter)
    End If
   
    Set db = CurrentDb
   
    'create temporary query
    Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
    Set qrydef = Nothing
   
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:=strTempQryDef, _
    FileName:=OutputName & ".xlsx"
   
    ' Delete the temporary query
    db.QueryDefs.Delete strTempQryDef
   
    Set db = Nothing
   
    Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.Workbooks.Open OutputName & ".xlsx", True, False

Set xlApp = Nothing
End Function
Comment
Watch Question

Mike EghtebasDatabase and Application Developer

Commented:
.
.
 IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
            IIf(strSubformName = "", Forms(strMainFormName).Form.Filter, Forms(strMainFormName)(strSubformName).Form.Filter)
    End If
   
    Set db = CurrentDb
   
'remove column names you want to exclude by:
strSQL = replace(strSQL ,"CustName,", "")     ' this line will remove CustName
    'create temporary query
.
.

From your excel export working right now, list all extra column names you do not want to have and repeat the following line as many times necessary.

strSQL = replace(strSQL ,"anotherColumnNameToHide,", "")    
etc.

If in the original recordset SQL the last hidden column name is right before FROM clause then use:

strSQL = replace(strSQL ,"last hidden column name", "")

Author

Commented:
Thanks for the quick response. What i am looking for, is when the code is looping through the recordset, if that column on the subform was hidden, then don't include it in the export. These columns will change every time the subform is exported.
Mike EghtebasDatabase and Application Developer

Commented:
I see your point. But, the text box names for those hidden text boxes will remain the same. So, the question is narrowed down to how to exclude the hidden column while looping to build SQL string.

Could you list the control name for all hidden columns? also the name of your form and subform. Identify hidden columns whether they are in the form or subform.

brb

Author

Commented:
Sorry, but I didn't see all of your first response. Let me try that quick
Mike EghtebasDatabase and Application Developer

Commented:
I just came back from a run. I took your question with me (mentally).

If you have a public array variable in a any module and as your report is setting hidden property of designate text boxes, you then add the name of the ControlSources to this array variable.

Therefore, you can easily loop through the array variable and apply :

strSQL = replace(strSQL ,"anotherColumnNameToHide,", "")    

to exclude the unwanted column names from the SQL string. As saying goes, there are different ways to skin an apple (I don't like to skin a cat). One way also is to check visible properties to exclude them from SQL string but the array method is much better. Because sometimes the developer sets text box width to zero with visible set to true.

If you like the array solution, let me know to proceed.

Mike
Mike EghtebasDatabase and Application Developer

Commented:
In any of your existing or new module add:

Public HiddenColumns() As String    ' because we don't know how many items there will be, we leave it blank to ReDim it later.

'in the code where you cycle to force hidden property to false have
Erase HiddenColumns    'this will clear this array in case it had some previous values
Dim i As Integer

For i = 1 To 9
i=1
ReDim Preserve HiddenColumns(i)
HiddenColumns(i) ="CustName"     ' here I used CustName but you most likely will have something else.

i=I+1
ReDim Preserve HiddenColumns(i)
HiddenColumns(i) ="AnotherCol"    
'
'

Open in new window

Mike EghtebasDatabase and Application Developer

Commented:
Incorporate this piece of code with you sample above.

Dim i as integer
Dim arraysize as integer
arraysize =UBound(HiddenColumns)
For i=1 to arraysize 
   strSQL = replace(strSQL, HiddenColumns(i) & ",", "")     
next i

Open in new window

Author

Commented:
Mike,

Thanks for the replys. Since I'm more of a beginner of vba than an expert, I need to digest your ideas first. (just means I need to do some more homework on arrays that I haven't done before, please be patient). If you have any other words of wisdom, I would love to hear it, as I would love to make this work on this form.

I have tried countless things while you were out on your run, all to no avail. I will try this now

Mark
Mike EghtebasDatabase and Application Developer

Commented:
re:> all to no avail.

This is part of process. It may not seem that you have not gotten much but definitely you have.

Words of wisdom you ask. Creation starts with imitation. As you make the described solution work for you, not only you learn it but pretty soon you get hang of it as you use it in some other cases. If possible at all, make a sample database and uploaded here for me or someone else to take a look at it; include the following answers as well.
1. You have one form and one sub-form on it.
2. Some fields on the sub-form and some from the sub-form are hidden.
3. How many different configurations your form/sub-form generates?
4.Include your existing exported Excel files with unwanted column names identified.

Mike

Author

Commented:
Mike,

Thanks for words of wisdom. I don't do this for a living, so I find myself learning new things (and re-learning forgotten things) every time.
Since the data is name and financial related, I will make a temp database this week. It will be a couple of days, but I will be back because I really want this Function to work.

Thanks for all the help

Mark

Author

Commented:
So I got it done quicker than I thought. Here is a stripped down version.

There is only one main form with only one subform in this DB. The subform is where the user will filter and hide unwanted columns for his desired results.
What I am looking for, is when you hide columns on the subform , I only want to add the visible columns to the exported Excel file. The filtering works great, but all columns always shows. On the left is a option box that calls several different querys, but for this, I just have one. Since I call a query name instead of SQL when I update the RecordSource of the subform,  I left in the Function GetQuerySQL I was working on for building the Sql statement, one of my last ideas I didn't finish.

Thanks for all the help.
DonorTemp.mdb
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It was the shortest, easiest solution to the problem, but may not be so universal.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.