Loop through closed workbooks without opening them

[YasserKhalil]

Thanks a lot for explanation ..
It is still not clear for me why do you use this line of code

Code: Select all

Let ws.Range(sRng).Value = Evaluate("=IF(ISERR(" & ws.Range(sRng).Address & ")," & """""" & ",IF(" & ws.Range(sRng).Address & "=0," & """""" & "," & ws.Range(sRng).Address & "))")

[Doc.AElstein]

why do you use this line of code

Code: Select all

....= Evaluate("=IF(ISERR(" & ws.Range(sRng).Address & ")," & """""" & ",IF(" & ws.Range(sRng).Address & "=0," & """""" & "," & ws.Range(sRng).Address & "))")

I do not always use it. Sometimes I use it. This is because lots of 0s and errors are sometimes not wanted

For example:
Lets say, this is my record file: https://imgur.com/4GzdOdk" onclick="window.open(this.href);return false;

RecordFile.JPG

Now if I use this code in the Main File without the extra line: ...

Code: Select all

Sub MessyTempData()
Dim RngTemp As Range: Set RngTemp = Thisworkbook.Worksheets("TempRange").Range("A1:E15")
 Let RngTemp.Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & "Clsdrecord1.xlsx" & "]Tabelle1'!A1"
 Let RngTemp.Value = RngTemp.Value
End Sub

_..then this is how my temporary range in the Main File looks after running that code: https://imgur.com/QmwwcJO" onclick="window.open(this.href);return false;

NullsErrorCrap.JPG

_.__________

If I now include the extra line like this:....

Code: Select all

Sub FukOffNullsAndErrors()
Dim RngTemp As Range: Set RngTemp = ThisWorkbook.Worksheets("TempRange").Range("A1:E15")
 Let RngTemp.Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & "Clsdrecord1.xlsx" & "]Tabelle1'!A1"
 Let RngTemp.Value = RngTemp.Value
 Let RngTemp.Value = Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & "," & RngTemp.Address & "))")
End Sub

......then now the 0s and Error is gone: https://imgur.com/qN6xL8D" onclick="window.open(this.href);return false;

NullsAndErrorDoneGone.JPG

_.____________________________________


The reason why I sometimes need this is:
Sometimes I make calculation of averages in data in my main File. So I do need to make sure that I have empty and not 0, or visa versa, if that is how my actual data is. In other words , I want the temporary range to look exactly like my actual data.
There is one small problem with this extra code line. The problem is that if I do have a 0 in my data, then I do not want that 0 to be lost. So as a workaround, I use a very small number, such as 0.001 instead of 0. My work does not need to be too accurate, so the error caused by this is minimal.
But I would be interested if anyone ever has a better solution to this.

A limitation of the closed workbook reference way is that you only can get at values. So an empty cell or 0 will give you 0 . In my work the difference between 0 or empty in my data is important.

( Another workaround for me is if I replace all empty cells in my data with a value such as “EmptyNowtInIt”. Then I can add another nested If in that Evaluate code line to replace all values of “EmptyNowtInIt” with “” : like this
Change
Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & "," & RngTemp.Address & "))")
to
Evaluate("=IF(ISERR(" & RngTemp.Address & ")," & """""" & ",IF(" & RngTemp.Address & "=0," & """""" & ",IF(" & RngTemp.Address & "=""EmptyNowtInIt""," & """""" & "," & RngTemp.Address & ")))")
In other words I change the "pseudo Array formula evaluation"
from
{=IF(ISERR($A$1:$E$15),"",IF($A$1:$E$15=0,"",$A$1:$E$15))}
to
{=IF(ISERR($A$1:$E$15),"",IF($A$1:$E$15=0,"",IF($A$1:$E$15="EmptyNowtInIt","",$A$1:$E$15)))}
I prefer not to do this as then my data looks very messy and is bigger as I have lots of empty cells. As I do not have many 0s in my data, then replacing all 0s with a very small number does not effect my data much. I can do this replacement easilly with a simple VBA code)

[YasserKhalil]

Thank you very much for great help. Best Regards