Solved

Acess Export to excel - font colour

Posted on 2011-03-02
7
250 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

12 Experts available now in Live!

Get 1:1 Help Now