Importing Excell cell numbers (e.g. $A$1) into a new column

[MaliciousMike]

Hello,

It's been a while since i posted here for help as my skills are getting really good!

Anyway.

I'm writing a system that imports spreadsheets from our clients into our database. I have made it so it can import as many spreadsheets as possible at any one time and it loops through them.

Sometimes these spreadsheets can contain up to 400 records each, making a lot of records to check, validate and import.

I want the system to run non stop, even if an error has been found in the spreadsheets, so i've made a log table which logs all the errors in all the spreadsheets.
The problem i'm having is that it takes longer to find the record with the problem than it takes to solve the problem. So i'd like for the log to include the spreadsheet's cell number.

The system imports the spreadsheet into a "check" table:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCheckSheet", My_Path & tmpfile, True

So i'd like the code to import the cell numbers either during this import or using an "INSERT" statement afterwards.

Any help would kick ass!

PS, sorry if this has been solved before but i did search for a while and found nothing.

 

[The_Doc_Man]

You can't do it this way. (Bad news.)

You can do exactly what you wanted with VBA. (Not so bad news.)

The question is whether you wanted to go this way, but here it is...

In VBA you can create an application object for Excel. (If you've not done this before, see "Appliction Object".)

When you create the object, it is an ActiveX copy of Excel for which you can open a spreadsheet. Presumably, you have the name of the workbook already so let's say that getting the names doesn't change. Only how you open them changes.

Once the workbook is open, you can select (and make active) any sheet in it. So then you have the ActiveSheet. To make typing easier, you can create an excel worksheet variable (just like you can create a DAO.Recordset or a Word.Document).

OK, in the spreadsheet you have two collections. These are .Rows and .Columns, and they are both numbered from 1. Each of these collections has a collection called Cells, again numbered from 1. If you choose to access the collection as ActiveSheet.Rows(1).Cells(1), you are accessing Cell A1. Ditto ActiveSheet.Columns(1).Cells(1). (They overmap the same things.)

The cells have fine structure, so I'll suggest that you merely look up Excel Help on VBA to see the structure of a Cell in programming terms. It has properties including things like fill color and content color, font, etc. The sort of things you would expect to see. There is also a .Range.Text element that is the textual content of the cell. But hit the Excel Help to get the straight goods on this, don't rely on my overaged memory.

When you do it this way, you have no doubt about where you saw the error. You can nail it as to row AND column - and even log the offending contents.