Formatting during export report to excel (1 Viewer)

akika

Registered User.
Local time
Today, 11:26
Joined
Aug 7, 2018
Messages
102
Hi,
i have below code attached to a button in access form.
How can i amend same such that when it export into the excel:
the header first row(A1 to J1) is bold and calibri font 14; row A2 to A5 - bold and A1 TO J5 get border line.

Code:
Dim outputStats As String
outputStats = CurrentProject.Path & "\Product_Stats_" & Format(Date, "yyyyMMdd") & Format(Time, "hhmmss") & "_" & Environ("USERNAME") & ".xls"
DoCmd.OutputTo acOutputReport, "Products", _
        acSpreadsheetTypeExcel19, , True
End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 20:26
Joined
Aug 11, 2003
Messages
11,696
amend the source report in acces?
 

arnelgp

once i caught a fish alive...
Local time
Tomorrow, 02:26
Joined
May 7, 2009
Messages
13,328
you need to put the output filename (outputStats):
Code:
DoCmd.OutputTo acOutputReport, "Products", aformatxls, outputstats, , , , acExportQualityPrint
Call FormatMyWorkSheet(outputstats)
End Sub

on a sepate Module, paste this code:
Code:
Public Function FormatMyWorkSheet(ByVal excel_file As String)

    Dim xlApp As Object         'Excel.Application
    Dim wb As Object            'Excel.Workbook
    Dim sh As Object            'Excel.Worksheet
    
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open(excel_file)
    Set sh = wb.Sheets(1)
    
    sh.Range("A1:I1").Select
    xlApp.Selection.Font.Bold = True
    With xlApp.Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = -4142      'xlUnderlineStyleNone
        .ThemeColor = 2         'xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = 2          'xlThemeFontMinor
    End With
    sh.Range("A2:A5").Select
    xlApp.Selection.Font.Bold = True
    sh.Range("A1:J5").Select
    'xlApp.Selection.Borders(xlDiagonalDown).LineStyle = -4142       'xlNone
    'xlApp.Selection.Borders(xlDiagonalUp).LineStyle = -4142         'xlNone
    'With Selection.Borders(xlEdgeLeft)
    xlApp.Selection.Borders(5).LineStyle = -4142       'xlNone
    xlApp.Selection.Borders(6).LineStyle = -4142         'xlNone
    With xlApp.Selection.Borders(7)       'xlEdgeLeft
        .LineStyle = 1      'xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2     'xlThin
    End With
    With xlApp.Selection.Borders(8)       'xlEdgeTop
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2     'xlThin
    End With
    With xlApp.Selection.Borders(9)     'xlEdgeBottom
        .LineStyle = 1      'xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2     'xlThin
    End With
    With xlApp.Selection.Borders(10)        'xlEdgeRight
        .LineStyle = 1      'xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2     'xlThin
    End With
    With xlApp.Selection.Borders(11)        'xlInsideVertical
        .LineStyle = 1      'xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2         'xlThin
    End With
    With xlApp.Selection.Borders(12)        'xlInsideHorizontal
        .LineStyle = 1      'xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2         'xlThin
    End With
    
    wb.Close True
    Set sh = Nothing
    Set wb = Nothing
    xlApp.Quit
    Set xlApp = Nothing

End Function
 

akika

Registered User.
Local time
Today, 11:26
Joined
Aug 7, 2018
Messages
102
When click on the export button.. am getting the screen > output To (Select Output format) in which there is Excel 97 - Excel 2003 workbook.
If i select this option getting screen "Microsoft Excel - Compatibility Checker " after clicking on continue it opening the formatted excel.

Is there a way to directly open it using the installed excel and avoid this Compatibility checker?
 

arnelgp

once i caught a fish alive...
Local time
Tomorrow, 02:26
Joined
May 7, 2009
Messages
13,328
ok, just replace My acformat with Your format (acSpreadsheetTypeExcel19).
 

akika

Registered User.
Local time
Today, 11:26
Joined
Aug 7, 2018
Messages
102
good its wrking.. :)

One last question pls...
I've added some colors but its not turning correctly..
any idea how to make the following
black background with white font ,
Dark red background with white font,
dark grey background with white font?

sh.Range("A1:I1").Interior.ColorIndex = 1 -----> Its black
sh.Range("A1:I1").Font.Color = 15 ------> kind of grey blue

Using xlThemeColorDark1 getting error.

ok, just replace My acformat with Your format (acSpreadsheetTypeExcel19).
 

Isaac

Lifelong Learner
Local time
Today, 11:26
Joined
Mar 14, 2017
Messages
5,386
good its wrking.. :)

One last question pls...
I've added some colors but its not turning correctly..
any idea how to make the following
black background with white font ,
Dark red background with white font,
dark grey background with white font?

sh.Range("A1:I1").Interior.ColorIndex = 1 -----> Its black
sh.Range("A1:I1").Font.Color = 15 ------> kind of grey blue

Using xlThemeColorDark1 getting error.
This is when it's time to stop and use the macro recorder yourself, see what kind of code it produces, and make an effort to tweak to suit.
 

Users who are viewing this thread

Top Bottom