I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has filename.xls needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

FileToOpen = Value2(x, 1) - this one has a filename.xls
WrkBookName = Value2(x, 2) - and this one goes blank

FileToOpen has the fullpath & FileName,xls
WrkBookName has just the FineName.xls when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )


Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:B" & lastrow)
End With

x = 1

For Each Value2 In wsLogRange

FileToOpen = Value2(x, 1)
WrkBookName = Value2(x, 2)
On Error Resume Next

Workbooks.Open Filename:=FileToOpen
' Application.Run (WrkBookName & "!ADD_BUTTONS") ' this
works for manual test: "'test.xls'!ADD_BUTTONS"
' for auto
test: Application.Run (wbTarget.Name & "!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub

First, Value2 is not a good variable name to use for your array. It looks way
too much like the .Value2 property of a range. And it may not confuse excel,
but it would confuse me!

Second, you don't need to have the name in the second column. In fact, I think
that if you have the name twice -- once in column A and once in column B, then
sooner or later, there's gonna be a mistake where they don't match. I wouldn't
do this.

Third, you close without saving. Is that for testing only, or did you really
mean that?

Anyway, this is untested, but it did compile.

Option Explicit
Sub Add_Buttons()

Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim tempWkbk As Workbook
Dim LastRow As Long
Dim RangeCell As Range

Set wsLog = ActiveWorkbook.Worksheets("Log")

With wsLog
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'be careful with those leading dots!
Set wsLogRange = .Range("A2:A" & LastRow)
End With

For Each RangeCell In wsLogRange.Cells

Set tempWkbk = Nothing
On Error Resume Next
Set tempWkbk = Workbooks.Open(Filename:=RangeCell.Value)
On Error GoTo 0

RangeCell.offset(0, 1).value = "" 'empty means ok.

If tempWkbk Is Nothing Then
RangeCell.Offset(0, 1).Value = "Not found!"
Else
Application.Run "'" & tempWkbk.Name & "'!ADD_BUTTONS"
tempWkbk.Close savechanges:=False 'why false????
End If
Next RangeCell

MsgBox "Finished Looping All workbooks from Log " _
& "and running their Sub within"

End Sub



On 07/30/2010 11:35, RompStar wrote:
I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has filename.xls needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

FileToOpen = Value2(x, 1) - this one has a filename.xls
WrkBookName = Value2(x, 2) - and this one goes blank

FileToOpen has the fullpath& FileName,xls
WrkBookName has just the FineName.xls when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )


Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
Sheets("Log").Activate
lastrow = .Range("A"& .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:B"& lastrow)
End With

x = 1

For Each Value2 In wsLogRange

FileToOpen = Value2(x, 1)
WrkBookName = Value2(x, 2)
On Error Resume Next

Workbooks.Open Filename:=FileToOpen
' Application.Run (WrkBookName& "!ADD_BUTTONS") ' this
works for manual test: "'test.xls'!ADD_BUTTONS"
' for auto
test: Application.Run (wbTarget.Name& "!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub

--
Dave Peterson

Dave thanks!!

I didn't do the save at the end after running the ADD_BUTTONS, because
the ADD_BUTTONS has a Save in there, so I didn't want to Save it twice
to save time, let me see how this goes, thanks for your time!



On Jul 30, 11:05*am, Dave Peterson wrote:
First, Value2 is not a good variable name to use for your array. *It looks way
too much like the .Value2 property of a range. *And it may not confuse excel,
but it would confuse me!

Second, you don't need to have the name in the second column. *In fact, I think
that if you have the name twice -- once in column A and once in column B, then
sooner or later, there's gonna be a mistake where they don't match. *I wouldn't
do this.

Third, you close without saving. *Is that for testing only, or did you really
mean that?

Anyway, this is untested, but it did compile.

Option Explicit
Sub Add_Buttons()

* * *Dim wsLog As Worksheet
* * *Dim wsLogRange As Range
* * *Dim tempWkbk As Workbook
* * *Dim LastRow As Long
* * *Dim RangeCell As Range

* * *Set wsLog = ActiveWorkbook.Worksheets("Log")

* * *With wsLog
* * * * *LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
* * * * *'be careful with those leading dots!
* * * * *Set wsLogRange = .Range("A2:A" & LastRow)
* * *End With

* * *For Each RangeCell In wsLogRange.Cells

* * * * *Set tempWkbk = Nothing
* * * * *On Error Resume Next
* * * * *Set tempWkbk = Workbooks.Open(Filename:=RangeCell.Value)
* * * * *On Error GoTo 0

* * * * *RangeCell.offset(0, 1).value = "" 'empty means ok.

* * * * *If tempWkbk Is Nothing Then
* * * * * * *RangeCell.Offset(0, 1).Value = "Not found!"
* * * * *Else
* * * * * * *Application.Run "'" & tempWkbk.Name & "'!ADD_BUTTONS"
* * * * * * *tempWkbk.Close savechanges:=False 'why false????
* * * * *End If
* * *Next RangeCell

* * *MsgBox "Finished Looping All workbooks from Log " _
* * * * * * *& "and running their Sub within"

End Sub

On 07/30/2010 11:35, RompStar wrote:





I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has * filename.xls *needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

* * *FileToOpen = Value2(x, 1) * *- this one has a filename..xls
* * *WrkBookName = Value2(x, 2) * - and this one goes blank

FileToOpen has the fullpath& *FileName,xls
WrkBookName has just the FineName.xls * when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )

Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
* * *Sheets("Log").Activate
* * *lastrow = .Range("A"& *.Rows.Count).End(xlUp).Row
* * *Set wsLogRange = Range("A2:B"& *lastrow)
End With

x = 1

For Each Value2 In wsLogRange

* * *FileToOpen = Value2(x, 1)
* * *WrkBookName = Value2(x, 2)
* * *On Error Resume Next

* * *Workbooks.Open Filename:=FileToOpen
' * *Application.Run (WrkBookName& *"!ADD_BUTTONS") * * * * *' this
works for manual test: "'test.xls'!ADD_BUTTONS"
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *' for auto
test: * Application.Run (wbTarget.Name& *"!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub

--
Dave Peterson- Hide quoted text -

- Show quoted text -

Worked like a charm, thanks dave!!!!

RompStar

On Jul 30, 11:05*am, Dave Peterson wrote:
First, Value2 is not a good variable name to use for your array. *It looks way
too much like the .Value2 property of a range. *And it may not confuse excel,
but it would confuse me!

Second, you don't need to have the name in the second column. *In fact, I think
that if you have the name twice -- once in column A and once in column B, then
sooner or later, there's gonna be a mistake where they don't match. *I wouldn't
do this.

Third, you close without saving. *Is that for testing only, or did you really
mean that?

Anyway, this is untested, but it did compile.

Option Explicit
Sub Add_Buttons()

* * *Dim wsLog As Worksheet
* * *Dim wsLogRange As Range
* * *Dim tempWkbk As Workbook
* * *Dim LastRow As Long
* * *Dim RangeCell As Range

* * *Set wsLog = ActiveWorkbook.Worksheets("Log")

* * *With wsLog
* * * * *LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
* * * * *'be careful with those leading dots!
* * * * *Set wsLogRange = .Range("A2:A" & LastRow)
* * *End With

* * *For Each RangeCell In wsLogRange.Cells

* * * * *Set tempWkbk = Nothing
* * * * *On Error Resume Next
* * * * *Set tempWkbk = Workbooks.Open(Filename:=RangeCell.Value)
* * * * *On Error GoTo 0

* * * * *RangeCell.offset(0, 1).value = "" 'empty means ok.

* * * * *If tempWkbk Is Nothing Then
* * * * * * *RangeCell.Offset(0, 1).Value = "Not found!"
* * * * *Else
* * * * * * *Application.Run "'" & tempWkbk.Name & "'!ADD_BUTTONS"
* * * * * * *tempWkbk.Close savechanges:=False 'why false????
* * * * *End If
* * *Next RangeCell

* * *MsgBox "Finished Looping All workbooks from Log " _
* * * * * * *& "and running their Sub within"

End Sub

On 07/30/2010 11:35, RompStar wrote:





I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has * filename.xls *needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

* * *FileToOpen = Value2(x, 1) * *- this one has a filename..xls
* * *WrkBookName = Value2(x, 2) * - and this one goes blank

FileToOpen has the fullpath& *FileName,xls
WrkBookName has just the FineName.xls * when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )

Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
* * *Sheets("Log").Activate
* * *lastrow = .Range("A"& *.Rows.Count).End(xlUp).Row
* * *Set wsLogRange = Range("A2:B"& *lastrow)
End With

x = 1

For Each Value2 In wsLogRange

* * *FileToOpen = Value2(x, 1)
* * *WrkBookName = Value2(x, 2)
* * *On Error Resume Next

* * *Workbooks.Open Filename:=FileToOpen
' * *Application.Run (WrkBookName& *"!ADD_BUTTONS") * * * * *' this
works for manual test: "'test.xls'!ADD_BUTTONS"
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *' for auto
test: * Application.Run (wbTarget.Name& *"!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub

--
Dave Peterson- Hide quoted text -

- Show quoted text -

Hi

First you have to declare FileToOpen explicit as String, else it will
be declared as Variant. Second, only loop through column A and
remember that Value2 shift one row down at each iteration, so you do
not have to increment X.


Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen As String, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:A" & lastrow)
End With

For Each Value2 In wsLogRange

FileToOpen = Value2 '(1, 1)
WrkBookName = Value2(1, 2)
On Error Resume Next
Debug.Print FileToOpen & " - " & WrkBookName

Workbooks.Open Filename:=FileToOpen
' Application.Run (WrkBookName & "!ADD_BUTTONS") ' this
works for manual test: "'test.xls'!ADD_BUTTONS"
' for auto
test: Application.Run (wbTarget.Name & "!MacroName")
ActiveWorkbook.Close SaveChanges:=False
Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
Within "
End Sub


Best regards,
Per

On 30 Jul., 18:35, RompStar wrote:
I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has * filename.xls *needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

* * FileToOpen = Value2(x, 1) * *- this one has a filename.xls
* * WrkBookName = Value2(x, 2) * - and this one goes blank

FileToOpen has the fullpath & FileName,xls
WrkBookName has just the FineName.xls * when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )

Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
* * Sheets("Log").Activate
* * lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
* * Set wsLogRange = Range("A2:B" & lastrow)
End With

x = 1

For Each Value2 In wsLogRange

* * FileToOpen = Value2(x, 1)
* * WrkBookName = Value2(x, 2)
* * On Error Resume Next

* * Workbooks.Open Filename:=FileToOpen
' * *Application.Run (WrkBookName & "!ADD_BUTTONS") * * * * *' this
works for manual test: "'test.xls'!ADD_BUTTONS"
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ' for auto
test: * Application.Run (wbTarget.Name & "!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub

Thanks Per, its good to see how different people do things, also
greatly appreciated, thanks and have a nice weekend!

RompStar

On Jul 30, 2:19*pm, Per Jessen wrote:
Hi

First you have to declare FileToOpen explicit as String, else it will
be declared as Variant. Second, only loop through column A and
remember that Value2 shift one row down at each iteration, so you do
not have to increment X.

Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen As String, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
* * Sheets("Log").Activate
* * lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
* * Set wsLogRange = Range("A2:A" & lastrow)
End With

For Each Value2 In wsLogRange

* * FileToOpen = Value2 '(1, 1)
* * WrkBookName = Value2(1, 2)
* * On Error Resume Next
* * Debug.Print FileToOpen & " - " & WrkBookName

* * Workbooks.Open Filename:=FileToOpen
* *' Application.Run (WrkBookName & "!ADD_BUTTONS") * * * * *' this
works for manual test: "'test.xls'!ADD_BUTTONS"
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ' for auto
test: * Application.Run (wbTarget.Name & "!MacroName")
* * ActiveWorkbook.Close SaveChanges:=False
Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
Within "
End Sub

Best regards,
Per

On 30 Jul., 18:35, RompStar wrote:



I have two Value2(1) and Value(2) in this test..

Value2(1) is variant (1 to 2)
Value2(2) is variant (1 to 2)

in the first loop it read it right, Value2(1) 1,1 and 1,2

but in the second loop, it don't read right Value(2) 2,1 2,2

Please help if you know how to read it, thanks!

Basically the Range that it reads and sticks into Value2 array ? is
starting at A2:B last row, in this test it is A2:B3 as the range.

A2 has fullpath * filename.xls
B3 has * filename.xls *needed top execute the Sub within each workbook
that is open, as I understand it

Basically the idea of this VBA Proc is to read from my log which
contains \\network\full\path\filename.xls, open each workbook in the
range and then run a Sub within each one called ADD_BUTTONS.

This works on the first workbooks in range, but then for the Next
Value2:

* * FileToOpen = Value2(x, 1) * *- this one has a filename.xls
* * WrkBookName = Value2(x, 2) * - and this one goes blank

FileToOpen has the fullpath & FileName,xls
WrkBookName has just the FineName.xls * when I put a watch on them,
they have the right values, but I think I read it wrong
?

Please advise, maybe I am doing it wrong, not a guru :- )

Sub Add_Buttons()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
* * Sheets("Log").Activate
* * lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
* * Set wsLogRange = Range("A2:B" & lastrow)
End With

x = 1

For Each Value2 In wsLogRange

* * FileToOpen = Value2(x, 1)
* * WrkBookName = Value2(x, 2)
* * On Error Resume Next

* * Workbooks.Open Filename:=FileToOpen
' * *Application.Run (WrkBookName & "!ADD_BUTTONS") * * * * *' this
works for manual test: "'test.xls'!ADD_BUTTONS"
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ' for auto
test: * Application.Run (wbTarget.Name & "!MacroName")
ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next

MsgBox "Finished Looping All workbooks from Log and running their Sub
within"

End Sub- Hide quoted text -

- Show quoted text -