Need help in ExecuteExcel4Macro VBA

[rong3]

Hi all, I get stuck on ExecuteExcel4Macro, using this for read value from closed file. On my PC and some PC it works perfectly , but just have some PC turn out debug "error 13".
I stuck on is there some requirement for ExecuteExcel4Macro function in VBA, all files are the same code and structure.
Hope your help soon!

[Aflatoon]

Error 13 is a type mismatch, which typically happens when an error is returned. Pretty hard to say why when you haven't posted any code at all or given very much information.

[rong3]

This is code which turns out Error 13 on some PC, in ExecuteExcel4Macro


wbPath = Workbooks("Software_KM.xlsm").Sheets("main").Range("I7").Value
wbName = Workbooks("Software_KM.xlsm").Sheets("main").Range("G9").Value
Ret = "'" & wbPath & "[" & wbName & "]" & "Sheet1" & "'!"
Application.ScreenUpdating = False
On Error Resume Next
For r = 1 To 500
If Replace(ExecuteExcel4Macro(Ret & Range("B" & r).address(, , xlR1C1)), " ", "") <> "0" Then
' Do something
End If
Next r


It works perfectly on some PC , but some others is not.

[rong3]

Edit : I fix by If Replace(ExecuteExcel4Macro(Ret & Range("B" & r).address(, , xlR1C1)), " ", "") <> "0" Then to

If CStr(ExecuteExcel4Macro(Ret & Range("B" & r).address(, , xlR1C1)) <> "0" Then
Then it is OK now, but when I debug for checking value of closed file, it all returns value of Error 2023.
But on my PC it works fine. The path file is OK, all is the same. I really stuck/

[Kenneth Hobs]

'Retrieves a value from a closed workbook
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
'path = "d:\files"
'file = "budget.xls"
'sheet = "Sheet1"
'ref = "A1:R30"



'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = CVErr(xlErrNA)
End If

'Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

[rong3]

Hi, Kenneth Hobs.
I have detected all cause is China font in VBA, I removed the sheetname which contains Chinese and it works OK.
How to resolved by sheename within Chinese? I installed all Chinese 's fonts and VBA editor shows correctly.

[Kenneth Hobs]

That's a new one for me. I guess if you attach a file with one sheet named that way, I can test it. You can try copying the text in the tabname and try save some simple file not a notepad with that name. I suspect that Windows will not allow it.

So, the answer might be to translate those strings to the language of your operating system's regional settings. Mine is English so that is all I could really test. If your test finds the file naming to be the problem, and you like this idea, we can look for a way to do it. It could be coded to add a prefix like, Chinese-EnglishSheetNameHere.xlsx.

[rong3]

That's a new one for me. I guess if you attach a file with one sheet named that way, I can test it. You can try copying the text in the tabname and try save some simple file not a notepad with that name. I suspect that Windows will not allow it.

So, the answer might be to translate those strings to the language of your operating system's regional settings. Mine is English so that is all I could really test. If your test finds the file naming to be the problem, and you like this idea, we can look for a way to do it. It could be coded to add a prefix like, Chinese-EnglishSheetNameHere.xlsx.

Sorry for can not attaching the sample file, but what i solved is set that Chinese into a column on Range Excel and pass it, but now I need help on dynamic that Chinese into this code, I try out and fails.


Dim get_value as String
get_value = ThisWorkbook.Sheets("main").Range("B8").Value 'B8 value stands for "環模".
Sheets("search").Range("C3").Offset(0, i - 1).Formula = "=INDEX('" & path & "[" & filename & "]" & get_value & "!$A:$O,MATCH(CONCATENATE(B3,E3,F3),'" & path & "[" & filename & "]*" & get_value & "'!$O:$O,0)," & i + 2 & ")"


The variable get_value if I set not dynamic then it works perfectly on my PC, but other is sometimes not. I want to dynamic it to formula to get value.
Can you give me a solution without workbook sample. Thank so much!

EDIT_FINAL: I have just solved.