Automating Excell via Access

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Hello all,

Can someone point me to some docs on how to automate
excell via access? I've found some example code that I've
incorporated into my app, but I'm looking at what else I
could do. Here is what I have:

Dim objxlbook As Excel.workbook
Dim objxlapp As Excel.Application
Dim objxlrange As Excel.Range
Dim objsheet As Excel.Worksheet
Set objxlbook = GetObject(fname)
Set objxlapp = objxlbook.Parent
Set objsheet = objxlbook.ActiveSheet
begrange = Day(fndstdte) + 46
endrange = begrange + 50
Set objxlrange = objsheet.Range("A" & begrange & ":AZ" &
endrange)
objxlapp.Visible = True
objxlbook.Windows(1).Visible = True
dne = 0
For i = 1 To UBound(objxlrange.Value, 1)
If objxlrange.Value(i, 3) = "IP:" & serial And
objxlrange.Value(i, 2) = whoami Then
objxlrange.Cells(i, 3).Value = "Complete"
objxlbook.Save
objxlbook.Close
dne = 1
Exit For
End If
Next i


What I'd like to know how to do is to open the sheet in a
read-only mode, or better yet, w/ multiple edit
capability. I found the above code in a book, but it
didn't describe any more detail. I need to know where to
find the docs that describes the above, and all the
options that are available.

Thanks in advance
Sean

BTW, this is win2k, acces and excel 2000
 
You need to learn the "object model" of Excel.

An "object model" is the particular set of objects, methods, properties &
events that are supported by a program. Although many Office programs use
Visual Basic for Applications (VBA) as their programming language, they all
have different object models. Thus, Access has objects like database,
tabledef, form, etc. Excel has objects like workbook, sheet, range, etc.

There are two ways to learn the Excel object model. 1) Read the Excel VBA
help. 2) Select the "record macro" option, do some things (open a workbook,
select a column, enter some values, whatever), turn macro recording off,
then use Tools:Macros to view the VBA produced by your actions. Then you can
use that VBA, from Access, to make Excel perform those tasks.

HTH,
TC
 
Back
Top