Solved

Acess Export to excel - font colour

Posted on 2011-03-02
7
288 Views
Last Modified: 2016-05-15
All

A silly question but i need to try and get an answer.

I have a query in Access 2007 that is emailed to recipients in .xls.  However, the exported excel workbook displays light grey font - not black.  

I realise it is easy to change this in the file but ...... recipents want the font to be black.....

any thoughts appreciated
0
Comment
Question by:shaz0503
  • 5
7 Comments
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 500 total points
ID: 35025555
Use access object to create some automation

dim xlapp as object
Set xlapp = createobject etc...

record a macro to do select all and set font to black....

get xlapp to open the saved file,
then do a with xlapp and paste in the macro...

can provide code if required.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35025579
http://support.microsoft.com/kb/247412

Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add


   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Last Name"
   oSheet.Range("B1").Value = "First Name"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Doe"
   oSheet.Range("B2").Value = "John"

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 total points
ID: 35025598
so here is a marco to open a file, change font and colour of the used range...

    Workbooks.Open Filename:= _
        "C:\Documents and Settings\sball\My Documents\Book1font.xls"
    ActiveWorkbook.UsedRange.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
   
        With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 4.99893185216834E-02
    End With

   
    ActiveWorkbook.Save
End Sub
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 15

Expert Comment

by:Simon Ball
ID: 35025606
then you can put the excel change code inside the automation to make changes to the spreadsheet
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35025650
0
 

Author Comment

by:shaz0503
ID: 35185414
Apologies for no action on this one - have been busy with other 'stuff'.  Hope to get to this next week.....

rgds

S
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now