Question Export to excel button based on records in current view

[vbaInet]

Good! So now I want you to copy the entire code from your database and put it in code tags.

 

[sladetroityer]

' FilterType - 1 = AVE, 2 = SCE-CHA, 3 = EPR, 4 = SCE-INT, 5 = SCI, 6 = VEE, 7 = UCN, 8 = SCI-NSA: Restraints, 9 = SCE-ENG, 10 = SCE-ELE, 11 = SCE-BOD, 12 = ALL, 13 = SCI:, 14 = SCI-ARD, 15 = SCI-BPS, 16 = SCI-CPG, 17 = SCI-DEB, 18 = SCI-MFS, 19 = SCI-NSA
    If (TempVars!FilterType = 1) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""AVE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 2) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-CHA""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 3) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""EPR""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 4) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-INT""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 5) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""SCI""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 6) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""VEE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 7) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""UCN""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 8) Then
        DoCmd.SetFilter "", "[strLowest_Level_Department]=""SCI-NSA: Restraints""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 9) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ENG""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 10) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ELE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 11) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-BOD""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 12) Then
        DoCmd.SetFilter "", "", ""
    ElseIf (TempVars!FilterType = 13) Then
        DoCmd.SetFilter "", "Left$([FIN],4)=""SCI:""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 14) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-ARD""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 15) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-BPS""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 16) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-CPG""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 17) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-DEB""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 18) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-MFS""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 19) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-NSA""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 20) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-PCV""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 21) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-SRT""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 22) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VIV""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 23) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VLE""", "NavigationSubform"
    End If

mcrFilter_Exit:
    Exit Function
mcrFilter_Err:
    MsgBox Error$
    Resume mcrFilter_Exit
End Function

 

[vbaInet]

So this is Access 2010 and you're using what they call a Navigation Form?

 

[sladetroityer]

Yes it is. I have a navagation form that is populated when the user picks a Button on the prior form. On this navigation form I have employee list tab, employee change tab, employee add tab, and report center tab. When the initial cmd button is picked the filter runs and the proper records are displyed. I can't get the filter to work on the reports which are all based off the same query. Employee query

 

[vbaInet]

Alright, I don't use Access 2010 and the SetFilter method is new in 2010. I don't want to get you to rewrite it to an older version so I will try and work with what you have (unless someone with relevant experience can chime in ).

Have you tried using the Import/Export wizard on the form to export to excel?

 

[sladetroityer]

No I have not.

 

[vbaInet]

Well, you should give it a try. You should find it on the ribbon.

 

[sladetroityer]

VBAInet,
I don't even see the EXPORT wizard. Where is it located? And it is part of the normal Access program or is it an add on?

 

[vbaInet]

Built-in functionality. The tab should be called External Data or something along those lines.

 

[sladetroityer]

Hmmm,
I don't see any wizards under the External Data Tab.......

Do you have any idea about my other thread about filtering my reports via Macros? I've got the filters working but then access keeps asking me for a Subform or Subreport.......

 

[vbaInet]

See the attached image. It's Access 2007 but it should give you some idea.

There's also more than enough information about this on the following links:
http://office.microsoft.com/en-us/access-help/export-data-to-excel-HA101819737.aspx
http://www.functionx.com/access/Lesson05.htm

I believe Alansidman is looking into your thread.

 

[sladetroityer]

Thanks for all your help but I'm still not seeing the Export/Import wizard...... I created a button to export the data and it works fine. However, it's exporting the entire query and not the filtered information that I have been asking about. The data is filtered on the form that I am exporting from but then when you look at the excel spreadsheet it is quite apparent that it exports from the entire query.

 

[vbaInet]

Show me the code behind this button.

Also I want you to put a Msgbox before the Export code. I want the message box to display the Filter property of the form and tell me if it displays a value:

MsgBox Me.Filter

 

[sladetroityer]

For whatever reson my export to excel botton is gone. The form that I have it on is in a datasheet view from. When I look at the form in Design view the export button is present but when I switch to Form view it is not visible.

 

[sladetroityer]

I used the built in macro exportwithformatting

 

[vbaInet]

Perhaps the form you were trying to export is unbound and it has a subform that is bound.

 

[sladetroityer]

How do I fix that?

 

[vbaInet]

How do you fix what exactly? I guess by binding the form??