Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Invalid use of object
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: Invalid use of object
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 |
#3
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error: Wrong arguments or Invalid property | Excel Discussion (Misc queries) | |||
what does "Compile Error : Invalid Qualifier" mean? | Excel Programming | |||
VBA Help needed -- compile error: object required | Excel Programming | |||
Compile Error - Wrong number of arguements or invalid property ass | Excel Programming | |||
compile error: invalid qualifier. | Excel Programming |