none
Access Report Headers to Excel Export Spreadsheet

    Question

  • Is there a way to get the Access Report Headers into the Excel spreadsheet the data is being sent to?
    I have report parameters, namely the start and ending dates, in the Access Report header, but when I export to Excel, it only exports the data, with column titles, and no headers.

    Thank you in advance for any help.


    Gee

    Wednesday, August 28, 2013 6:45 PM

Answers

  • I found a work-around.

    If I put a textbox next to the Group Sum field, with the date parameters like

    ="Report Includes Dates "& " "&"[Start Date]&" "&"Through"&" "&[End Date]

    as the Control Source, it shows up on the Excel Spreadsheet. 

    It shows up in every row, but with a simple query that inserts a row at the top of the spreadsheet, then moves the first date parameter to cell A1 and then deletes that column of repeated date parameters and does some other report-identification routines, it works perfectly!


    Gee

    • Marked as answer by GretaF Thursday, August 29, 2013 4:22 PM
    Thursday, August 29, 2013 4:22 PM

All replies

  • Depending on how you are exporting to Excel -- if these headers are static (don't change) you can export your data to an existing Excel worksheet that already has the headers with Excel Automation and ADO.  If the headers change -- then -- also with a little Excel automation and ADO -- you need to export the header data separately and manipulate the header data as desired with Excel Automation.

    If you don't plan on coding much -- the other alternative is to import the data from Access into Excel and create the headers all manually.  This is a reasonable option if you prefer to not get too involved with VBA coding.


    Rich P

    Wednesday, August 28, 2013 7:02 PM
  • The report is for others, so I need to have the header in the export to sheet.

    The headers are not static either.  One Text box contains the Start date and End date that are entered when the report is run, and the other contains the category that is selected.

    So when the button is clicked run the report (query), it has a category, start date and end date that need to be entered and also need to show up on the Excel spreadsheet.

    Now they say "Text24" and "Text27" when I need them to say which category and the start and end dates.

    I'm not very good at VBA coding, but I'll do anything to get this project done right, so...do you have a solution?


    Gee


    • Edited by GretaF Wednesday, August 28, 2013 7:12 PM
    Wednesday, August 28, 2013 7:10 PM
  • Hi

    If you know nothing of VBA programming, try to export the report in RTF format and then back to Excel.

    Regards


    Wednesday, August 28, 2013 7:14 PM
  • I didn't say I know nothing!  I've been working with Access for years so am pretty good at it, but never was formally trained so new processes like this need careful explaining.

    It needs to be a single-click export to excel for the users.


    Gee

    Wednesday, August 28, 2013 7:20 PM
  • 'Exportar a Excel mediante una plantilla el contenido actual del rsDAO_Movtos

    '================================================================

    'Exportar a Excel mediante una plantilla el contenido actual del rsDAO_Movtos
    'La variable "bCongrupo" indica que se incluya los totales de los grupos
    Function funExportCotiza_xls(sSubTitulo As String) As String
    Dim intRegActual As Long
    Dim iNumColumn As Long
    Dim iFontSize As Integer
    Dim i As Integer
    Dim sFileName As String 'Nombre del archivo
    Dim bExisteReg As Boolean
    Dim iNumRegVentas As Integer
    Dim rsFactDet  As DAO.Recordset
    Dim sFileTemp As String
    Dim sSQLOrden  As String
    On Error Resume Next

    bExisteReg = False  'Indica si existen registros

            '**** Abrimos el libro de Excel
            Set xlsExcelApp = New Excel.Application
            Set xlsLibro = xlsExcelApp.Workbooks.Open(fun_rutaApp & "\xlsPlantCotiza.xls")
            'Pedimos el nombre del archivo
            sFileTemp = sSubTitulo '"Cotiza(" & Day(Now()) & Month(Now()) & Year(Now()) & ")"
            sFileName = xlsExcelApp.GetSaveAsFilename(fun_rutaApp & "\docGenerados\" & sFileTemp & "", "Archivos de Excel(*.xls), *.xls", 1, "Guardar Cotización, Como...")
            'expresión.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

            
            'Se selecciono un archivo?
            If sFileName = "falso" Then
                'Cerramos y salimos
                        'Cerramos el Excel, sin mostrar mensajes
                    xlsExcelApp.DisplayAlerts = False
                    xlsLibro.Close
                    xlsExcelApp.Quit
                    xlsExcelApp.DisplayAlerts = True
                                                             
            
                    Set xlsExcelApp = Nothing
                    Set xlsLibro = Nothing
                    
                Exit Function
            End If
            
                    
            If InStr(sFileName, ".xls") = 0 Then
                sFileName = sFileName & "xls"
            End If
            funExportCotiza_xls = "malo"
            
           'Activamos la primera Hoja
          xlsLibro.Sheets(1).Activate
          
          
       sSQLOrden = "SELECT tbFactPartTemp.*, tbCatMaster.Descripcion, * " & _
        " FROM tbFactPartTemp INNER JOIN tbCatMaster ON tbFactPartTemp.IdProducto = tbCatMaster.IdProducto " & _
        " ORDER BY tbFactPartTemp.IdItem; "
        'Abrimos la tabla de detalle (ASIGNACIONES)
        Set rsFactDet = CurrentDb.OpenRecordset(sSQLOrden)
        
       


       intRegActual = 10 ' No. de Fila
       iNumColumn = 2 ' columna B
       iFontSize = 9 ' Tamaño de la fuente
       
       i = 1
       
          With xlsLibro.ActiveSheet
             'strValorGrupo = "xxx"
         
            .Cells(3, 2) = lstClientes.Column(1)   'Nombre de la Empresa "B3"
            .Cells(4, 2) = lbCAlle.Caption  ' Nombre de la calle "B4"
            .Cells(5, 2) = lbColonia.Caption ' Nombre de la colonia
            .Cells(6, 2) = "RFC : " & lbRFC.Caption
            .Cells(8, 2) = "Atención : " & txtAtencionA
            
            .Cells(2, 8) = "Cotización ( " & txtFACT_IdLlave & " )" 'H2
            .Cells(4, 8) = txtFecFactura 'Fecha del docto
            .Cells(5, 8) = lstVendedores.Column(1)     'Quien envia
            
            
                    
            ' ***** Partidas de la cotizacion ******
             While rsFactDet.EOF = False
                
                        
                        .Cells(intRegActual, iNumColumn).HorizontalAlignment = xlCenter
                        .Cells(intRegActual, iNumColumn) = i
                        
                        .Cells(intRegActual, iNumColumn + 1).Font.Size = iFontSize
                        .Cells(intRegActual, iNumColumn + 1) = rsFactDet.Fields("tbCatMaster.IdProducto")  'Clave del producto
                        
                        .Cells(intRegActual, iNumColumn + 2) = rsFactDet.Fields("Cantidad")
                        .Cells(intRegActual, iNumColumn + 3) = rsFactDet.Fields("Descripcion")
                        .Cells(intRegActual, iNumColumn + 5) = Format(rsFactDet.Fields("Precio"), "##,###.###") 'Precio
                        .Cells(intRegActual, iNumColumn + 5).Style = "Comma"
                        
                        .Cells(intRegActual, iNumColumn + 6) = rsFactDet.Fields("SubTotal") 'Cantidad Vendidas
                        .Cells(intRegActual, iNumColumn + 6).Style = "Comma"
                                            
                                                                                        
                'Incrementamos el Numero de fila
                intRegActual = intRegActual + 1
                rsFactDet.MoveNext
                i = i + 1
             
             Wend 'While rsDAO_movtos.EOF = False
             
             
             
                     
                        'Aplicamos los bordes al rango añadido
                        .Range("b10:h" & intRegActual - 1).Borders.Color = RGB(125, 125, 125)
                        .Range("b10:h" & intRegActual - 1).Borders.LineStyle = 1
                        
                                      
                     'Ponemos los totales
                     intRegActual = intRegActual + 2
                    'Primer renglon
                   ' .Range("b" & intRegActual & ":h" & intRegActual - 1).Borders.Color = RGB(125, 125, 125)
                    .Range("b" & intRegActual & ":h" & intRegActual - 1).Borders(xlEdgeTop).LineStyle = 1
                    
                    'Bloque de Subtotales
                    .Range("f" & intRegActual - 1 & ":h" & (intRegActual + 2) - 1).Borders.LineStyle = 1
                    .Range("b" & intRegActual - 1 & ":h" & (intRegActual + 2) - 1).Borders(xlEdgeBottom).LineStyle = xlDouble
                    .Range("g" & intRegActual - 1 & ":h" & (intRegActual + 2) - 1).Borders(xlEdgeLeft).LineStyle = xlNone
                    
                                                   
                              
          End With 'With xlsLibro.ActiveSheet
          
        '  Range("a1").Select
          
                'Guardamos el Archivo seleccionado
            If InStr(sFileName, ".xls") > 0 Then
                xlsLibro.SaveCopyAs sFileName
            Else
                xlsLibro.SaveCopyAs sFileName & "xls"
            End If
          
          'Cerramos el Excel, sin mostrar mensajes
            xlsExcelApp.DisplayAlerts = False
            xlsExcelApp.Quit
            xlsExcelApp.DisplayAlerts = True
            
                xlsLibro.Close False
                Set xlsLibro = Nothing
                Set xlsExcelApp = Nothing
          

                
          'Cerramos la tabla temporal
          Set rsFactDet = Nothing
          
          'El valor a regresar
          funExportCotiza_xls = sFileName

    End Function



    Wednesday, August 28, 2013 8:08 PM
  • OK...thanks for that, but I only speak English.


    Gee

    Wednesday, August 28, 2013 9:22 PM
  • To work you need:

    - Having a template, in this case called "xlsPlantCotiza.xls".
    - The path where it generates is App + docGenerados \.
    - Fun_rutaApp, is a routine that returns where it's running the application.
    - Refer to the Excel library


    Greetings!


    deneg_nhj

    Wednesday, August 28, 2013 9:33 PM
  • I found a work-around.

    If I put a textbox next to the Group Sum field, with the date parameters like

    ="Report Includes Dates "& " "&"[Start Date]&" "&"Through"&" "&[End Date]

    as the Control Source, it shows up on the Excel Spreadsheet. 

    It shows up in every row, but with a simple query that inserts a row at the top of the spreadsheet, then moves the first date parameter to cell A1 and then deletes that column of repeated date parameters and does some other report-identification routines, it works perfectly!


    Gee

    • Marked as answer by GretaF Thursday, August 29, 2013 4:22 PM
    Thursday, August 29, 2013 4:22 PM