Posted to microsoft.public.excel.programming
|
|
Compile Error: Invalid use of object
Thanks for the assist. I have NO IDEA why that changed, because it worked
at one time and I don't remember changing it. Also thanks for the assist
on the syntax.
"Chip Pearson" wrote:
You need to change the line
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, _
UpdateLinks = False)
to
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly:=True, _
UpdateLinks:= False)
Note the colons before the equal signs.
Also, your syntax is wrong on the line
If oWB Is Not Nothing Then
This should be
If Not oWB Is Nothing Then
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Barb Reinhardt" wrote
in message
...
I have the following macro:
Sub LastEdit()
Dim oWB As Workbook
Dim aWB As Workbook
Dim aWS As Worksheet
Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
'May need to change cell values to work for current
application. Started at
H originally
Cells(1, "AQ").Value = "Last Author"
Cells(1, "AR").Value = "Creation Date"
Cells(1, "AS").Value = "Last Save Time"
'For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To 11
Set oWB = Nothing
Debug.Print Cells(i, "B")
On Error Resume Next
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, _
UpdateLinks = False)
On Error GoTo 0
If oWB Is Not Nothing Then
aWS.Range("AQ" & i).Value =
ActiveWorkbook.BuiltinDocumentProperties("Last Author").Value
aWS.Range("AR" & i).Value =
ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
aWS.Range("AS" & i).Value =
ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time").Value
oWB.Close SaveChanges:=False
aWB.Save
End If
Next i
aWB.Save
End Sub
I get the listed compile error on this line
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, _
UpdateLinks = False)
What am I doing wrong?
Thanks,
Barb Reinhardt
|