Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / January 2008

Tip: Looking for answers? Try searching our database.

Export Conditional Formatting to Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Reebis - 10 Jan 2008 15:57 GMT
I have a form that uses conditional formatting to turn the cell either red or
green based on if the line item is ahead or behind schedule.  When I go to
export this to excel, the formatting is loast (i.e no red/green in the .xls).
Is there a way to amke the color formatting come thru in the export?

Thanks,

-bob
Dale Fye - 10 Jan 2008 16:46 GMT
Yes,

After you have exported to Excel, you can open the Excel workbook and use
automation to change your formatting of that column.  The way I would do this
is:

1.  Export your report to Excel.
2.  Open Excel, and record a new macro.  Once you have started recording the
new macro:
   a. Select the column you want to format.
   b. Click on the Formatting - Conditional Formatting menu item.
   c. Set your appropriate formatting options
   d. Stop recording the macro
3. Open the Excel VB editor, find the macro you just created, and copy the
code for the formatting and paste it into an Access code module.  Add some
code to open Excel, open the spreadsheet, and then do the formatting.  It
will look something like below (this is from a macro I run):

Public Sub ExcelConditionalFormatting

   dim xlObj as object
   dim wbk as object

   set xlObj = createobject("Excel.Application")
   xlObj.visible = true
   set wbk = GetObject("FileName.xls")
   wbk.visible = true
   wbk.sheets(1).activate

   Sheets(1).Range("C:C").Select
   
   Selection.FormatConditions.Delete
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula
   With Selection.FormatConditions(1).Font
       .Bold = True
       .Italic = False
       .ColorIndex = 2
   End With
   Selection.FormatConditions(1).Interior.ColorIndex = 3
   Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
                                  Formula1:="=" & Chr$(64 + SelCol) & "3"
   With Selection.FormatConditions(2).Font
       .Bold = False
       .Italic = False
       .ColorIndex = xlAutomatic
   End With
   Selection.FormatConditions(2).Interior.ColorIndex = 19
   Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
       Formula1:="=" & Chr$(64 + SelCol) & "4"
   With Selection.FormatConditions(3).Font
       .Bold = False
       .Italic = False
       .ColorIndex = xlAutomatic
   End With
   Selection.FormatConditions(3).Interior.ColorIndex = 44

   wbk.close SaveChanges:=True
   xlObj.Quit

End Sub

You can do this without making Excel visible if you want, or could keep it
hidden until the formatting is complete, then make it visible.  You might
even want to just save the changes (wbk.save) and leave off the close and
quit methods as the last two lines of this subroutine.

HTH
Dale
Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> I have a form that uses conditional formatting to turn the cell either red or
> green based on if the line item is ahead or behind schedule.  When I go to
[quoted text clipped - 4 lines]
>
> -bob
Reebis - 10 Jan 2008 16:56 GMT
Awesome!  Thanks Dale!  Just what I was looking for.

> Yes,
>
[quoted text clipped - 73 lines]
> >
> > -bob
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.