问题描述:

I have a workbook with other workbooks listed down column G

Folder\File1.xls

Folder\File2.xls

Folder\File3.xls

Folder\File4.xls

The below vba code gets the email address from cell C15 in all of these workbooks and adds them to column U like so:

Folder\File1.xls Email

Folder\File2.xls Email

Folder\File3.xls Email

Folder\File4.xls Email

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))

End With

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)

Next

'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?

相关阅读:
Top