I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

I haven't used Harlan's function in a while, but are you sure that the
conversion to date isn't happening when you put the value back in the cell in
the worksheet?

If you know you want to return text, you could try this:

c.Value = "'" & xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))

Maybe it'll work ok for you.

Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

--

Dave Peterson

Read the value into a string before wrting to the cell

Dim TempString as string
For Each c In r
TempString = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
c.value = TempString
Next c

Also make surre you Excel4Macro is 2nd workbook is defined as a string

sub Excel4Macro(.....) as string

"Paul" wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

And if it does work ok, but you want to check before you convert to text:

Dim res as variant
res = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
if isdate(res) then
c.value = res
else
c.value = "'" & res
end if

(or add more significant checks.)

Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

--

Dave Peterson

Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

On Oct 3, 6:08 pm, Dave Peterson wrote:
I haven't used Harlan's function in a while, but are you sure that the
conversion to date isn't happening when you put the value back in the cell in
the worksheet?

I use the debugger to step through the function, and the datatype on
c.Value is listed as "Variant/Double".


If you know you want to return text, you could try this:

c.Value = "'" & xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))

That does solve the date-conversion problem, but some of what I need
to get are actual numbers, so they shouldn't be turned to strings.
Thanks for the pointer, though, I think this gets me headed in the
right direction.

Thanks,
Paul

"Paul" wrote:
I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?


On Oct 3, 6:08 pm, Joel wrote:
Read the value into a string before wrting to the cell

Dim TempString as string
For Each c In r
TempString = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
c.value = TempString
Next c

Thanks for the suggestion! That was the first thing I tried, and it
didn't work. When the string variable was assigned to c.value, did
the same thing as the direct assignment did before.

Maybe you could use that other suggestion.

Paul wrote:

Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

On Oct 3, 6:08 pm, Dave Peterson wrote:
I haven't used Harlan's function in a while, but are you sure that the
conversion to date isn't happening when you put the value back in the cell in
the worksheet?

I use the debugger to step through the function, and the datatype on
c.Value is listed as "Variant/Double".


If you know you want to return text, you could try this:

c.Value = "'" & xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))

That does solve the date-conversion problem, but some of what I need
to get are actual numbers, so they shouldn't be turned to strings.
Thanks for the pointer, though, I think this gets me headed in the
right direction.

Thanks,
Paul

--

Dave Peterson

This is what ended up working:

Dim temp as Variant
temp = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
If IsDate(temp) Then
c.value = "'" & temp
else
c.value = temp
end if

This would of course break the function if it were really supposed to
come through as a date, but for this application, it worked.

Thanks for the help!

On Oct 3, 6:09 pm, Dave Peterson wrote:
And if it does work ok, but you want to check before you convert to text:

Dim res as variant
res = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
if isdate(res) then
c.value = res
else
c.value = "'" & res
end if

(or add more significant checks.)



Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

--

Dave Peterson

Oftentimes, you really have to know your data to make those good
assumptions/decisions.



Paul wrote:

This is what ended up working:

Dim temp as Variant
temp = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
If IsDate(temp) Then
c.value = "'" & temp
else
c.value = temp
end if

This would of course break the function if it were really supposed to
come through as a date, but for this application, it worked.

Thanks for the help!

On Oct 3, 6:09 pm, Dave Peterson wrote:
And if it does work ok, but you want to check before you convert to text:

Dim res as variant
res = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
if isdate(res) then
c.value = res
else
c.value = "'" & res
end if

(or add more significant checks.)



Paul wrote:

I am using Harlan Grove's PULL function to grab data from other
spreadsheets, and it mostly works except that some strings that look
like dates get converted to numbers and therefore don't work with
VLOOKUP and the like. For example, "06-6107" gets changed to
1536728. It seems that this is happening in this bit of code:

For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

The Value property is a Variant, so in the conversion the string is
interpreted as a date. Is there any way to force that to be converted
a Variant/String instead of Variant/Double?

Thanks,
Paul

--

Dave Peterson

--

Dave Peterson