I have a workbook with other workbooks listed down column G
The below vba code gets the email address from cell C15 in all of these workbooks and adds them to column U like so:
This is my code.
'//Email copy code
Dim startCell As Range, fileRng As Range
Dim files As Variant, values() As Variant, values2() As Variant
Dim path As String, file As String, arg As String
Dim r As Long, i As Long
'Acquire the names of your files
With ThisWorkbook.Worksheets(1) 'amend to your sheet name
Set startCell = .Range("G17") 'amend to start cell of file names
Set fileRng = .Range(startCell, .Cells(.Rows.Count, startCell.Column).End(xlUp))
files = fileRng.Value2
'Size your output array
ReDim values(1 To UBound(files, 1), 1 To 1)
'Populate output array with values from workbooks
For r = 1 To UBound(files, 1)
'Create argument to read workbook value
i = InStrRev(files(r, 1), "\")
path = Left(files(r, 1), i)
file = Right(files(r, 1), Len(files(r, 1)) - i)
arg = "'" & path & "[" & file & "]Sheet1'!R15C3"
'Acquire the value
values(r, 1) = ExecuteExcel4Macro(arg)
'Write values to sheet
fileRng.Offset(, 20).Value = values
This code works on my windows laptop at home. However, at work, my IT department disable the use of CMD and other shell functions - which i believe this code uses to grab the value from the workbooks.
As a result, this code doesn't get the value from column C15.
Can anyone give me a workaround? Thanks
How about this workaround:
arg = path & "[" & file & "]Sheet1'!R15C3" 'Acquire the value values(r, 1) = arg 'values(r, 1) = ExecuteExcel4Macro(arg)
Would this work? What does it produce? What should it produce?