Hi All,

I built a macro to get data from excel workbooks without open it. So what i
do is use the function ExecuteExcel4Macro to get the value from couple of
cell in the excel workbook. Til now it work very well. And is few faster
than first open the excel book and then get the value.

But now i got the following problem, when i try to use this funcyion
'ExecuteExcel4Macro' to get data from some cells this has another
cell-format. Its doesn't work any more. Normally i just try to get data from
a cell with 'General' format. And i when try to get value from cell with
'Date-format' of another format, it didn't seems to work any more.

Can someone help me to solve this problem? or does anyone get a sugguestion?
other methode?

Best regards,

Patrick.

I suspect you don't understand that dates are store as a number representing
the number of days from a base date (Usually 31 dec 1899).

? CDBL(now)
38569.4854050926

? format(38569.4854050926,"mmm dd, yyyy hh:mm")
Aug 05, 2005 11:38

so I suspect you are getting the correct data, but now just need to
interpret it correctly.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Hi All,

I built a macro to get data from excel workbooks without open it. So what
i
do is use the function ExecuteExcel4Macro to get the value from couple of
cell in the excel workbook. Til now it work very well. And is few faster
than first open the excel book and then get the value.

But now i got the following problem, when i try to use this funcyion
'ExecuteExcel4Macro' to get data from some cells this has another
cell-format. Its doesn't work any more. Normally i just try to get data
from
a cell with 'General' format. And i when try to get value from cell with
'Date-format' of another format, it didn't seems to work any more.

Can someone help me to solve this problem? or does anyone get a
sugguestion?
other methode?

Best regards,

Patrick.

Hi Tom,

Thanks for your reaction, but it doesn't help yet. I think you didn't
understand my problem. What i do is i use the function 'ExecuteExcel4Macro'
to get the data from an close workbook.

For example:

ExecuteExcel4Macro('C:\Temp\[Test1.xls]Sheet1'!R1C1)

What i want is to get the value of cell "A1" from sheet1 of the workbook
Test.xls.
It works very well, if the cells format is general just normal text. But now
the problem is when i try to get value of a cells it has another cell-format.
The function "ExecuteExcel4Macro" will not get another value, i just get an
empty string back. So my question is are there any other solutiuon to get
data from close workbook?

Best regard,

Pat
"Tom Ogilvy" wrote:

I suspect you don't understand that dates are store as a number representing
the number of days from a base date (Usually 31 dec 1899).

? CDBL(now)
38569.4854050926

? format(38569.4854050926,"mmm dd, yyyy hh:mm")
Aug 05, 2005 11:38

so I suspect you are getting the correct data, but now just need to
interpret it correctly.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Hi All,

I built a macro to get data from excel workbooks without open it. So what
i
do is use the function ExecuteExcel4Macro to get the value from couple of
cell in the excel workbook. Til now it work very well. And is few faster
than first open the excel book and then get the value.

But now i got the following problem, when i try to use this funcyion
'ExecuteExcel4Macro' to get data from some cells this has another
cell-format. Its doesn't work any more. Normally i just try to get data
from
a cell with 'General' format. And i when try to get value from cell with
'Date-format' of another format, it didn't seems to work any more.

Can someone help me to solve this problem? or does anyone get a
sugguestion?
other methode?

Best regards,

Patrick.

I understood you question except the fact that you are getting an empty
string. I assume you are using code from John Walkenbach's site or someone
who has posted John's code:

http://j-walk.com/ss/excel/tips/tip82.htm

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

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

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValue()
p = "c:\Data6\"
f = "99Budget.xls"
s = "Sheet1"
a = "A2"
MsgBox GetValue(p, f, s, a)
End Sub


In my case, A2 contained a date value and was formatted as date. The msgbox
contained a date serial number as I described. Perhaps you are actually
accessing an empty cell. Also note John's caveat that a worksheet must be
the activesheet when you run your code.

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
Hi Tom,

Thanks for your reaction, but it doesn't help yet. I think you didn't
understand my problem. What i do is i use the function
'ExecuteExcel4Macro'
to get the data from an close workbook.

For example:

ExecuteExcel4Macro('C:\Temp\[Test1.xls]Sheet1'!R1C1)

What i want is to get the value of cell "A1" from sheet1 of the workbook
Test.xls.
It works very well, if the cells format is general just normal text. But
now
the problem is when i try to get value of a cells it has another
cell-format.
The function "ExecuteExcel4Macro" will not get another value, i just get
an
empty string back. So my question is are there any other solutiuon to get
data from close workbook?

Best regard,

Pat
"Tom Ogilvy" wrote:

I suspect you don't understand that dates are store as a number
representing
the number of days from a base date (Usually 31 dec 1899).

? CDBL(now)
38569.4854050926

? format(38569.4854050926,"mmm dd, yyyy hh:mm")
Aug 05, 2005 11:38

so I suspect you are getting the correct data, but now just need to
interpret it correctly.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Hi All,

I built a macro to get data from excel workbooks without open it. So
what
i
do is use the function ExecuteExcel4Macro to get the value from couple
of
cell in the excel workbook. Til now it work very well. And is few
faster
than first open the excel book and then get the value.

But now i got the following problem, when i try to use this funcyion
'ExecuteExcel4Macro' to get data from some cells this has another
cell-format. Its doesn't work any more. Normally i just try to get
data
from
a cell with 'General' format. And i when try to get value from cell
with
'Date-format' of another format, it didn't seems to work any more.

Can someone help me to solve this problem? or does anyone get a
sugguestion?
other methode?

Best regards,

Patrick.