getValue

[baha17]

Hi Everyone,
To get the value from closed workbook I tried a code from following
link
http://j-walk.com/ss/excel/tips/tip82.htm

but the problem is whenever use that kind of code or similar one, empty
cells copied as "0" which creates head ache for me to delete those zero
values which I can do that. My question is is there a easy way when
getting values from closed workbook not to copy empty cells or those
"0" not appear on destination path. There might be another idea as
well. I think I heard someone does that but not very sure.
Your help will be greatly appreciated.
Regards
Baha

 

[NickH]

Hi Baha,

You can use an IF statement, something like this...

=IF([OtherBook.xls]Sheet1!A1=0,"",[OtherBook.xls]Sheet1!A1)

HTH, NickH

 

[baha17]

Hi Nick,
Here is the code, can you help me to that formula in between
somewhere.I tried too many option including your formula but cannot
think anything else. Thanks for your help
Sub TestGetValues2()
p = "C:\Documents and Settings\BahadiAkcan\My Documents\attendence
check"
f = "tip training" & ".xls"
s = "Completed"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub
Private Function GetValue(path, file, sheet, ref)
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

 

[NickH]

Doh! Sorry Baha,

I didn't follow your original link and just assumed it was being done
with formulas - wrong group, I know.

Try inserting a line at the end of the function like so...

GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

Br, NickH

 

[NickH]

Doh! Sorry Baha,

I didn't follow your original link and just assumed it was being done
with formulas - wrong group, I know.

Try inserting a line at the end of the function like so...

GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

Br, NickH

 

[baha17]

Thanks a lot Nick that really worked. You guys really very helpful.
thanks again
regards,