Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
Hi Baha,
You can use an IF statement, something like this... =IF([OtherBook.xls]Sheet1!A1=0,"",[OtherBook.xls]Sheet1!A1) HTH, NickH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
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 wrote: Hi Baha, You can use an IF statement, something like this... =IF([OtherBook.xls]Sheet1!A1=0,"",[OtherBook.xls]Sheet1!A1) HTH, NickH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
getValue
Thanks a lot Nick that really worked. You guys really very helpful.
thanks again regards, Baha NickH wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GetValue - Closed Workbook? | Excel Discussion (Misc queries) | |||
Getvalue from closed workbook | Excel Discussion (Misc queries) | |||
GetValue to extract data from closed files | Excel Programming |