This one's as long as a piece of string. If you're new to Automation,
these articles will help you get started:
Sample Excel Automation
http://www.mvps.org/access/modules/mdl0006.htm
Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859
ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148)
http://support.microsoft.com/?id=210148
Basically you need to launch (or get hold of) and instance of Excel and
us it to open your workbook. Then create a Worksheet object that
references the sheet you're interested in and a Range object that can
reference the range in question. What follows is air code:
Dim wksW As Excel.Worksheet
Dim rngData As Excel.Range
Dim rngCell As Excel.Range
Dim lngR As Long
Dim lngC As Long
...
Set wksW = objXL.ActiveWorkbook.Worksheets("MyWorksheet")
Now the fun starts, working with the Excel object model. First, you need
to identify the range of cells you're interested in. If you're lucky,
you'll know this already and can just do something like
Set rngData = wksW.Range("A1", "F99")
Probably you'll know the number of columns in advance but not the number
of rows. In that case you need to work your way down a column that you
know will always have a value (the primary key if there is one) until
you find an empty cell and then set the range variable:
lngC = 1 'column of primary key
lngR = 0
Do
lngR = lngR + 1
Until Len(wksW.Cells(lngR, lngC).Formula = 0)
lngC = 6 'last column
Set rngData = wksW.Range(wksW.Cells(1, 1), wksW.Cells(lngR, lngC))
Then you can start iterating through each cell in the various columns,
checking that its .value or .formula property is appropriate, e.g.
For Each rngCell in rngData.Columns(1).Cells
'Each cell in the first column
'Check value and take any necessary action
Next
Hi John,
Thanks a bunch for your suggestions, I shall have a crack
at them!
Is there any chance however, that you might expand on
what you said aboout using automation in access to scan
the values? How exactly do I go about doing this?
Joe
-----Original Message-----
Hi Joe,
I know two basic ways of doing this, and feel the first is usually
simpler:
1: Use conditional formatting (or formulas in additional columns) in the
workbook to check the data in each cell. For instance, if you have a
column that must contain only text values or empty cells, use a
conditional format that changes the background colour of a cell if this
formula
=NOT(OR(B2="",(ISTEXT(B2))))
is true (replace B2 with the appropriate cell reference). This will
highlight all cells that need attention.
2: Use Automation to have your code (running in Access) scan the values
of all the cells in all the columns and correct or report problematic
values.
Can anyone tell me how to report validation errors in a
spreadsheet before it is imported in to the database? In
other words, identify which fields or records will be
picked out as inconsistent with the field type BEFORE the
actual transfer from the spreadsheet to the database goes
ahead - thus giving the user a chance to edit or delete
those invalid records in spreadsheet.
Any response would be most helpful.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
