Export filtered subform results and only visible columns to excel.
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).For
strRecordSource = Forms(strMainFormName).For
Else
bolWithFilterOn = Forms(strMainFormName)(str
strRecordSource = Forms(strMainFormName)(str
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)(str
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).For
End If
Set db = CurrentDb
'create temporary query
Set qrydef = db.CreateQueryDef(strTempQ
Set qrydef = Nothing
DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreads
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.Applic
xlApp.Visible = True
xlApp.Workbooks.Open OutputName & ".xlsx", True, False
Set xlApp = Nothing
End Function
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).For
strRecordSource = Forms(strMainFormName).For
Else
bolWithFilterOn = Forms(strMainFormName)(str
strRecordSource = Forms(strMainFormName)(str
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)(str
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).For
End If
Set db = CurrentDb
'create temporary query
Set qrydef = db.CreateQueryDef(strTempQ
Set qrydef = Nothing
DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreads
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.Applic
xlApp.Visible = True
xlApp.Workbooks.Open OutputName & ".xlsx", True, False
Set xlApp = Nothing
End Function
ASKER
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.
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
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
ASKER
Sorry, but I didn't see all of your first response. Let me try that quick
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
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
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.
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"
'
'
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 iASKER
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
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
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
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
ASKER
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It was the shortest, easiest solution to the problem, but may not be so universal.
.
IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
IIf(strSubformName = "", Forms(strMainFormName).For
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", "")