Compile Error: Invalid use of object

B

Barb Reinhardt

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
 
C

Chip Pearson

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



in message
news:[email protected]...
 
B

Barb Reinhardt

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top