Running Excel code

[Mike Collard]

I have used the GetObject method to open an Excel file
which I then wish to manipulate so I recorded a macro in
Excel and naively thought I could just cut and paste the
code into an Access module. Through trial and error I
have got some of the code to work but not all and would
appreciate some guidance:

Sub GetExcel()
Dim MyXL As Object
Dim ExcelWasNotRunning As Boolean
Dim MyWks As Excel.Worksheet
On Error Resume Next ' Defer error trapping.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
DetectExcel

Set MyXL = GetObject("c:\apps\data\excel\Test.XLS")

With MyXL

.Application.Visible = True
.Windows("Test.xls").Activate
'Do manipulations of your file here.

Set MyWks = .ActiveSheet

With MyWks

.Range("H2").Select
.Selection.FormatConditions.Delete
.Selection.FormatConditions.Add Type:=xlExpression,
Formula1:= _
"=AND(H2>0,ISBLANK(J2)="")"
.Selection.FormatConditions(1).Interior.ColorIndex = 15
.Selection.Copy
.Range("H2:H1000").Select
.Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Range("L2:L1000").Select
.Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Range("P21000").Select
.Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Application.CutCopyMode = False
.Columns("J:J").ColumnWidth = 0
.Columns("N:N").ColumnWidth = 0
.Columns("R:R").ColumnWidth = 0
.Range("A2").Select

End With
End With

Etc...

The Range method works but the Selection statement
produces an error 'Method or Data member not found'.

Any ideas?

Thanks

Mike Collard

 

[Shamil Salakhetdinov]

<the Selection statement produces an error >
Mike, selection is a method of MS Excel application object - use something
like:

MyXL.Selection.FormatConditions.Add Type:=xlExpression, ....

Shamil

P.S. I must note your code needs quite some refactoring/polishing...