Export Report to Excel formatting issue

[cubadew]

Ok... Ive been reading all the other horror stories on this forum about trying to export a report directly to excel and how the formatting is totally stripped.

However a simple acformatxls is a 90% solution to what I am doing because it allows me to apply my search filters to the query as well as limit the data actually exported and I can make custom headers using the text box names.

My problem is that one of my fields contains data that is in this format "07-1000" which we use as a tracking number. The dash is part of the data... and I think that is part of the problem because when it gets to excel it is some crazy number like -31542 or something. I checked and the field is a "General" field which I thought would accept the data as is... but not so much.

So I guess I have 2 questions.
1. What is it doing to my data to make it look so funky?
2. Is there any thing I can do to fix it?

I have searched high and low and I have yet to find a way to apply formatting to an excel report after using acformat. I think this would be a good solution, but have no clue how to impliment it.
I should also note that whatever excel is doing to my data seems to be consistent and almost like a formula was applied since tracking numbers that are close togther are likewise very similar in the skewed data varying by 1 or 2 numbers.

 

[cubadew]

Bingo!

I found out that it is something to do with the report! I copied all the fields into a continuous form named "excel" and added the following code to a button.... Please note this code comes after a very long filter sql statement that compiles all the users search selections into one search filter. Ill add that after the code snippet.

DoCmd.OutputTo acOutputForm, "excel", _
    acFormatXLS, "", True, , False

and Voila! it worked perfectly. Using this I am able to add my own custom field names by naming the text boxes the most user friendly name and I can apply my filter to the query limiting the data to only what I have searched for. This added to the fact that it only exports the text fields I add to the continuous form instead of the entire query makes it very very useful.

I hope this helps someone else... I couldnt find this answer on any other forum

Here is the complete code.

Private Sub Export_button_Click()
Me!AllEvents.Form.OrderByOn = True
DoCmd.OpenForm "Excel"
'On Error Resume Next
Dim strWhere As String                  'The criteria string.
Dim lngLen As Long                      'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    'DoCmd.Hourglass True 'Changes the cursor to the hourlass

' If combo is blank or contains "ALL", we do nothing.
If Not IsNull(Me.FromEventText_Box.Value) Then
        strWhere = strWhere & "([avEventNumber] >= """ & Me.FromEventText_Box.Value & """) AND "
    End If
    
    If Not IsNull(Me.ToEventText_Box.Value) Then
        strWhere = strWhere & "([avEventNumber] <= """ & Me.ToEventText_Box & """) AND "
    End If

    ' If combo is blank or contains "ALL", we do nothing.
    If Not IsNull(Me.Status_combo.Value) Then
        strWhere = strWhere & "([EventStatus]= """ & Me.Status_combo.Value & """) AND "
    End If

    If Not IsNull(Me.State_combo.Value) Then
       strWhere = strWhere & "([StateAbb]= """ & Me.State_combo.Value & """) AND "
   End If

If Not IsNull(Me.City_combo.Value) Then
       strWhere = strWhere & "([avSiteCity]= """ & Me.City_combo.Value & """) AND "
   End If
   
   If Not IsNull(Me.Sponsor_combo.Value) Then
       strWhere = strWhere & "([avSponsor]= """ & Me.Sponsor_combo.Value & """) AND "
   End If
   
   If Not IsNull(Me.POC_combo.Value) Then
       strWhere = strWhere & "([Expr2a]= """ & Me.POC_combo.Value & """) AND "
   End If
   
   If Not IsNull(Me.Site_combo.Value) Then
       strWhere = strWhere & "([avSite]= """ & Me.Site_combo.Value & """) AND "
   End If
    If Not IsNull(Me.SupSquad_combo.Value) Then
       strWhere = strWhere & "([avSupSquadron]= """ & Me.SupSquad_combo.Value & """) AND "
   End If
    If Not IsNull(Me.ACLookup_combo.Value) Then
       strWhere = strWhere & "([AC_ID]= """ & Me.ACLookup_combo.Value & """) AND "
   End If
    If Not IsNull(Me.EventLookup_combo.Value) Then
       strWhere = strWhere & "([avSupEventType]= """ & Me.EventLookup_combo.Value & """) AND "
   End If
   If Not IsNull(Me.SportType_combo.Value) Then
       strWhere = strWhere & "([sports_Type]= """ & Me.SportType_combo.Value & """) AND "
   End If
     If Not IsNull(Me.NascarSeries_combo.Value) Then
       strWhere = strWhere & "([NascarSeries]= """ & Me.NascarSeries_combo.Value & """) AND "
   End If
    If Not IsNull(Me.EventType_combo.Value) Then
       strWhere = strWhere & "([avSupEventType]= """ & Me.EventType_combo.Value & """) AND "
   End If
   If Not IsNull(Me.ResponseType_combo.Value) Then
       strWhere = strWhere & "([responsetype]= """ & Me.ResponseType_combo.Value & """) AND "
   End If
   If Not IsNull(Me.EventTitle_combo.Value) Then
       strWhere = strWhere & "([avEventTitle]= """ & Me.EventTitle_combo.Value & """) AND "
   End If
'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.BA_checkbox = -1 Then
        strWhere = strWhere & "([avBA] = True) AND "
    ElseIf Me.BA_checkbox = 0 Then
        strWhere = strWhere & "([avBA] = False) AND "
    End If
If Me.TB_checkbox = -1 Then
        strWhere = strWhere & "([avTB] = True) AND "
    ElseIf Me.TB_checkbox = 0 Then
        strWhere = strWhere & "([avTB] = False) AND "
    End If
    If Me.GK_checkbox = -1 Then
        strWhere = strWhere & "([avGK] = True) AND "
    ElseIf Me.GK_checkbox = 0 Then
        strWhere = strWhere & "([avGK] = False) AND "
    End If
    If Me.OT_checkbox = -1 Then
        strWhere = strWhere & "([avOTteam] = True) AND "
    ElseIf Me.OT_checkbox = 0 Then
        strWhere = strWhere & "([avOTteam] = False) AND "
    End If
    If Me.FO_checkbox = -1 Then
        strWhere = strWhere & "([avFO] = True) AND "
    ElseIf Me.FO_checkbox = 0 Then
        strWhere = strWhere & "([avFO] = False) AND "
    End If
    If Me.SD_checkbox = -1 Then
        strWhere = strWhere & "([avSD] = True) AND "
    ElseIf Me.SD_checkbox = 0 Then
        strWhere = strWhere & "([avSD] = False) AND "
    End If
    If Me.TD_checkbox = -1 Then
        strWhere = strWhere & "([avTD] = True) AND "
    ElseIf Me.TD_checkbox = 0 Then
        strWhere = strWhere & "([avTD] = False) AND "
    End If
    If Me.LF_checkbox = -1 Then
        strWhere = strWhere & "([avLF] = True) AND "
    ElseIf Me.LF_checkbox = 0 Then
        strWhere = strWhere & "([avLF] = False) AND "
    End If
    If Me.SupportedEvent_checkbox = -1 Then
        strWhere = strWhere & "([avSupported] = True) AND "
    ElseIf Me.SupportedEvent_checkbox = 0 Then
        strWhere = strWhere & "([avSupported] = False) AND "
    End If
    If Me.Congr_ckbox = -1 Then
        strWhere = strWhere & "([avCongressional] = True) AND "
    ElseIf Me.Congr_ckbox = 0 Then
        strWhere = strWhere & "([avCongressional] = False) AND "
    End If
      If Me.LeapFrogsSupSearch_ckbox = -1 Then
        strWhere = strWhere & "([LeapFrogsSup] = True) AND "
    ElseIf Me.LeapFrogsSupSearch_ckbox = 0 Then
        strWhere = strWhere & "([LeapFrogsSup] = False) AND "
    End If
     If Me.BlueAngelsSupSearch_ckbox = -1 Then
        strWhere = strWhere & "([BlueAngelsSup] = True) AND "
    ElseIf Me.BlueAngelsSupSearch_ckbox = 0 Then
        strWhere = strWhere & "([BlueAngelsSup] = False) AND "
    End If
     If Me.BAreturn_ckbox = -1 Then
        strWhere = strWhere & "([baReturn] = True) AND "
    ElseIf Me.BAreturn_ckbox = 0 Then
        strWhere = strWhere & "([baReturn] = False) AND "
    End If
     If Me.LFreturn_ckbox = -1 Then
        strWhere = strWhere & "([lfReturnJump] = True) AND "
    ElseIf Me.LFreturn_ckbox = 0 Then
        strWhere = strWhere & "([lfReturnJump] = False) AND "
    End If
     If Me.BAreturnNo_ckbox = -1 Then
        strWhere = strWhere & "([baReturnNo] = True) AND "
    ElseIf Me.BAreturnNo_ckbox = 0 Then
        strWhere = strWhere & "([baReturnNo] = False) AND "
    End If
     If Me.LFreturnNo_ckbox = -1 Then
        strWhere = strWhere & "([lfReturnJumpNo] = True) AND "
    ElseIf Me.LFreturnNo_ckbox = 0 Then
        strWhere = strWhere & "([lfReturnJumpNo] = False) AND "
    End If

     If Me.NAVCO_checkbox = -1 Then
        strWhere = strWhere & "([NAVCO] = True) AND "
    ElseIf Me.NAVCO_checkbox = 0 Then
        strWhere = strWhere & "([NAVCO] = False) AND "
    End If
     'If Not IsNull(Me.SearchAircraft_Combo.Value) Then
     '   strWhere = strWhere & "([Jet]= """ & Left(Me.SearchAircraft_Combo.Value, InStr(1, Me.SearchAircraft_Combo.Value, " ", vbTextCompare) - 1) & """) AND "
   ' End If
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.FromText_Box) Then
        strWhere = strWhere & "([avtotaldate] >= " & Format(Me.FromText_Box, conJetDate) & ") AND "
    End If
    
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.ToText_Box) Then   'Less than the next day.
        strWhere = strWhere & "([avtotaldate] <= " & Format(Me.ToText_Box, conJetDate) & ") AND "
    End If
      'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)


 'Finally, apply the string as the form's Filter.
    Forms!Excel.Form.Filter = strWhere
    Forms!Excel.Form.FilterOn = True
     Me!AllEvents.Form.Filter = strWhere
    Me!AllEvents.Form.FilterOn = True
    End If
On Error Resume Next
DoCmd.OutputTo acOutputForm, "excel", _
    acFormatXLS, "", True, , False    

DoCmd.Close acForm, "Excel"
End Sub