ExecuteExcel4Macro fails in Excel 2010

[IanFScott]

Using ExecuteExcel4Macro and Office 2010

Using the function below grabs a value from a closed workbook.
It has worked perfectly for years (Excel 97 up to Excel 2007)
On upgrade to Excel 2010 the ExecuteExcel4Macro now produces an Error 2023. has anyone come across this - and if so have you found any quick solution?

[vba]
'===================================================================
' Gets value from a cell - GetLastSheet will already have established
' that the file and sheet exist so this is the simple version
'
Function GetValue(Path, File, Sheet, Ref)
Dim Arg As String
GetValue = 0
' Create the argument
Arg = "'" & Path & "[" & File & "]" & CStr(Sheet) & "'!" & _
Range(Ref).Range("A1").Address(, , xlR1C1)
' Execute the macro
GetValue = ExecuteExcel4Macro(Arg)
End Function

[/vba]

[Kenneth Hobs]

It is a reference error that you would have gotten before. I usually iterate and delete lines of code with that error since it is something that I did not need anyway if the sheet does not exist.

[vba]Sub RemoveRowsWithNoAC()
Dim thePath As String, cell As Range
thePath = "U:\Design Worksheets\"
On Error Resume Next
For Each cell In Range("A2", Range("A" & Rows.count).End(xlUp))
'If cell.Row = 4 Then Exit Sub
' Debug.Print cell.Address, IsNumeric(Range("D" & cell.Row)), VarType(IsNumeric(Range("D" & cell.Row)))
If Range("D" & cell.Row).value = "Error 2023" Or Range("D" & cell.Row).value = 0 Then
cell.EntireRow.Delete xlUp
GoTo NextCell
End If
If Not (Range("D" & cell.Row).value > 0) Then
cell.EntireRow.Delete xlUp
End If


NextCell:

Next cell

End Sub[/vba]
This explains returning some error info: http://www.cpearson.com/Excel/ReturningErrors.aspx

[VBA]Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function[/VBA]

[IanFScott]

Thanks for the reply - I probably was not clear enough.
The Function works perfectly in Office 2003 and 2007 (I have machines with all these variants installed) looking at exactly the same target files.
The overall routine looks into 40 or so timesheet workbooks and extracts various figures.
We are graudually upgrading to Office 2010 and when the routine was tried on Office 2010 it failed as described. It is therefore a specific 2010 problem and I was just wondering whether anyone else has seen this.

[Kenneth Hobs]

Are you sure that it fails or is it just reporting the error text in the result? I run 2010 and it has not happened anymore than before with 2003.

Try a test on one sheet in one test workbook. You can include the test workbook if you like but I would guess that it is the reference issue as I explained.

[snb]

Did you consider using some formulae (in a separate worksheet) instead ?

e.g. in cell Z1:
[vba]='G:\OF\[adressen.xls]Blad1'!A1[/vba]

[IanFScott]

I have exactly the same problem. The only extra point I can offer is that if I open the first target file (to check the correct data is there) then close it and run the macro that file (and that file only) will work (but only until you close and restart Excel).
Explain that if you can .
Also I have found that the Excel4Macro seems to work on Excel 2010 files which are created from scratch - the problem may be with files you have "upgraded" from the older versions (not fully tested).

[jumpjack]

SOLUTION? This error happens in case first sheet in the workbook has not the expected name! Default is "sheet1", if it has been changed, macro will work fine, also in 2010... but reference being wrong, it will raise an error. This argument: = 'G:\OF\[adressen.xls]Blad1'!A1 Looks for "Blad1" sheet; if it's named "Sheet1" instead, macro will fail. Also don't forget different default sheets name in different languages!

[Kenneth Hobs]

Solution to what? Please ask your question in your own thread. Reference this thread if needed. I explained how to check for errors and how to account for them. Using either of the two methods, you must know the sheet name. The first sheet does not matter in either case. What is critical is that you know the sheet name.

All methods have limitations. The trick is to ask the right question, not to try to fix a method that is limited in achieving the goal of the question.

[jumpjack]

Solution to what?

To original question:

ExecuteExcel4Macro fails in Excel 2010

Macro does not fails, it's working fine.
But the macro engine can't find the sheets and fails.
And it does not depend on 2010 or 2007 or 2003.

[Kenneth Hobs]

You are just saying what I said.

[jumpjack]

Nope.
You think I have a question, instead I have an answer. ;-)

[Bridsworth]

Has anyone figured out this problem? I'm getting the same problem IanFScott is getting, he references the issue in his message at 12-21-2012, 07:45 AM. The macro works if I've opened the files then closed them (defeating the purpose of the macro in the first place), but it doesn't work if I've opened a fresh instance of Excel. I'm running Excel 2010 on Windows 7 if that makes any difference. The files it's trying to pull from are .xls files (can't be helped - they are coming from a web based reporting system). I've confirmed multiple times that the sheet names, file names, path, cell reference are all correct.