We help IT Professionals succeed at work.

Changing Excel cell backcolor from Access TransferSpreadsheet

514 Views
Last Modified: 2013-11-27
I have a query written in Access that creates a spreadsheet by using TransferSpreadsheet. One of the columns that is being sent is a long representation of the color that I want in that cell only. No text, only a colored cell.

My query results looks like this: Publication Name, Address, City, State, Zip, Publication Type, (the long integer value of some color in this cell), SRDS Code, Release Type, (the long integer value of some color in this cell), Release Date, Market Share.

The columns A-F have data, column G would have a color-filled cell, Columns H-I have data, Column J has some other color, Columns K-L have data.

The colors of the cells in the columns may change for every row written. The columns will not have the same colors in every row.

Is there a way to cause Excel to turn this long integer into a color? The code can't be on the Excel side because the worksheets are created over and over again.

Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
You can't do what you want with TransferSpreadsheet or OutputTo. The only way to format the created workbook is to use automation. Automation is more involved than TransferSpreadsheet or OutputTo but it isn't that bad.

Here is one example of using automation in Access to create a workbook:

http://bytes.com/forum/thread193600.html

Kevin
CERTIFIED EXPERT
Top Expert 2008

Commented:
Probably the easiest way to do this is to do what you are doing now and then, using automation from within Access, open the generated workbook and apply the formatting.

Kevin
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks guys!

I have never used automation to create a spreadsheet like this before. Could someone give me the actual code to do this. Attached is the sql statement that would have been in the transgerspreadsheet function as "qryDT_BasicRelease":

Best Regards,
Lenny
SELECT qryDT_BasicPressReleaseMissing.Year, qryDT_BasicPressReleaseMissing.Week, GetTheDateRange([Week],[Year]) AS [PR Date], qryDT_BasicPressReleaseMissing.sPR_JobNumber AS [Job #], tblCF_Company.sCompanyName AS Company, tblCF_Company.iTheColorCode AS CompanyColorCode, qryDT_BasicPressReleaseMissing.sPR_Title AS Subject, IIf([bReleaseProduced]=True,"x",Null) AS Written, IIf([bReleasePrinted]=True,"x",Null) AS Printed, IIf([bReleaseEMailed]=True,"x",Null) AS [E-Mailed], rptqryPhotoAndImagesUnion.TheImage AS Images, tblCF_ReleaseDescription.iTheColorCode AS [Release Color Code], tblCF_ReleaseDescription.sReleaseDescription AS [Release Description], tblCF_Product.sProduct AS Product, qryDT_BasicPressReleaseMissing.sPressReleaseNotes AS [Press Release Notes]
FROM ((tblCF_Product RIGHT JOIN (tblCF_Company RIGHT JOIN qryDT_BasicPressReleaseMissing ON tblCF_Company.iCompanyID = qryDT_BasicPressReleaseMissing.iCompanyID) ON tblCF_Product.iProductID = qryDT_BasicPressReleaseMissing.iProductID) LEFT JOIN tblCF_ReleaseDescription ON qryDT_BasicPressReleaseMissing.iReleaseDescriptionID = tblCF_ReleaseDescription.iReleaseDescriptionID) LEFT JOIN rptqryPhotoAndImagesUnion ON qryDT_BasicPressReleaseMissing.sPR_JobNumber = rptqryPhotoAndImagesUnion.sPR_JobNumber
ORDER BY qryDT_BasicPressReleaseMissing.Year, qryDT_BasicPressReleaseMissing.Week, qryDT_BasicPressReleaseMissing.sPR_JobNumber;

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.