get excel cell properties from access

[dief2]

We are importing an Excel spreadsheet into an Access Db. We do not want the entire spreadsheet, just certain cells which may change from import to import, so we have an Access table that lists the cells we need that the user can alter. Then we loop through the table and use transfer spreadsheet to bring the cells in one by one based on the values in the Access control table. This is a spreadsheet that is used to send information to the EPA on impurities in water discharged from a plant. If the amount is under a certain amount (AMT for example) the report finally generated by Access should say < AMT rather than the actual amount. To distinguish < AMT in the spreadsheet, the Excel cells have a red background, and the operator will enter the minimum amount rather than the actual amount. So we need to know tha background color of the cell to distinguish the minimum amount from the actual. Does anybody know if I can capture the background color of the Excel cell from Access?

Thanks in advance

 

[Philem]

Hullo, and welcome to the board! This is not too difficult, as Excel exposes a great deal of formatting to Access. Simply look for Cell.Interior.ColorIndex to equal whatever red is being used.

HTH

P

 

[Cbrine]

Philem,
It sounds like they are not using the Excel object to get the data into the access table, but are using transfer spreadsheet to get the data in. So your post most likely looks like gooblygook to them.

Here is the code to use the Excel Object from MSAccess.
You will need to update both FullPath and FileName to your values.
I would suggest you change your program to use the excel object instead of the transferspreadsheet.
You can determine cell values using
WS.Range("???").value
and use DOA or ADO to update your tables.

Dim XL As Excel.Application
Dim WB As Workbook, WS As Worksheet, Excel As Application
Dim FullPath as string, FileName as String

FullPath = 'Equals your filepath to the Excel workbook
FileName = 'Equals the Excel Workbook Filename

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open (FullPath)
XL.Visible = False

'Format Spreadsheet and Close Excel
XL.DisplayAlerts = False

Set WB = XL.Workbooks(FileName)
Set WS = WB.Worksheets(XL.ActiveSheet.Name)

msgbox = WS.range("A2").interior.colorindex

WB.Close
XL.Quit

HTH

 

[dief2]

Appreciate the responses. A quick question - is the Excel.application an add-in? We're a vary large corporation - I don't think I have access to any Access addins like the Excel object and that's why we had to use transfer spreadsheet (PS working with MIS is a very time consuming process & that's why I'm writing this). Is there any way to do it without the excel object or did I miss something & we probably have access to the excel object?

 

[Cbrine]

Sorry,
It's been a while since I set this up on my database. You need to add a reference, for I believe, "Microsoft Excel Object Library8.0". This is a free reference library for MSOffice. You will need to update the references on each system that the database is being used on, but it's a free item.

HTH

PS- Without using the excel object, you will not be able to get the cell color format at all. Nothing in the MSAccess(At least in 97) will allow you to get that detail from an excel workbook. You only hope is,

1. Utilize the excel object to get that value, or
2. Use a formula in excel that build's a field with the colorformat value, that you could import into access and do your filters on.

Excel Custom Function. Just add a module to your excel workbook and copy this code. Then put the function on your Worksheet, and copy and paste as any formula. This will ID the the cells interior color as a number.

Function WhatColor(R as Range)
WhatColor= R.interior.colorindex
Exit Function