Excel Macro Strings
 

Hello, I am trying to use the concatenate operator to
combine 3 different individual strings with numbers in
them into one string. Something like this:

month = xx
day = xx
year = xx

date = month & "/" & day & "/" & year

But this doesn't work. Is this the correct operator to
use for this kind of purpose and I am just using it
incorrectly? Or is there a whole other way and I am
missing something?

Excel Macro Strings
 

Brandon,

My first suggestion would be to use different variable names, month, day and
year are functions and date is a property. Try your code again with things
like myMonth, myDay, myYear and myDate, if it still acts up, post back

Dan E

"Brandon" wrote in message
...
Hello, I am trying to use the concatenate operator to
combine 3 different individual strings with numbers in
them into one string. Something like this:

month = xx
day = xx
year = xx

date = month & "/" & day & "/" & year

But this doesn't work. Is this the correct operator to
use for this kind of purpose and I am just using it
incorrectly? Or is there a whole other way and I am
missing something?

Excel Macro Strings
 

Yes, I did that already, the month/day/year was just for
showing what kind of number I am using to try and convert
into a date. Any other ideas?

Excel Macro Strings
 

It looks correct to me then, what is the problem, does it output something
incorrect, nothing at all, etc . . . Perhaps a little more description would
help.

Dan E

"Brandon" wrote in message
...
Yes, I did that already, the month/day/year was just for
showing what kind of number I am using to try and convert
into a date. Any other ideas?

Excel Macro Strings
 

Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a
vague error message.

Excel Macro Strings
 

Post the "vague error message"

Dan E

"Brandon" wrote in message
...
Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a
vague error message.

Excel Macro Strings
 

Run-time error '13':
Type Mismatch


This is my code:

**********

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = DDERequest(Channel, "V30145:B")
PLCDay = DDERequest(Channel, "V30146:B")
PLCYear = DDERequest(Channel, "V30147:B")

DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

**********

When I retrieve the date values, they are all numbers,
not strings, and also-- the "/0" in the line that assigns
a value to PLCDate, the "0" is because when I retrieve
the year from the PLC (programmable logic controller), it
comes as 03 which becomes just '3'. I think it is just a
variable type inconsistency, like I am using numbers but
it is looking for a String.
Thanks for all the help you're giving, Dan E.

Excel Macro Strings
 

myday = 10
mymonth = 5
myyear = 2003
? mymonth & "/" & myday & "/" & myyear
5/10/2003
? cdate(mymonth & "/" & myday & "/" & myyear)
5/10/2003
? format(cdate(mymonth & "/" & myday & "/" & myyear),"mmm dd, yyyy")
May 10, 2003

What is "date" dimensioned as

Sub testdate()
Dim dtDate As Date
myday = 10
mymonth = 5
myyear = 2003
dtDate = mymonth & "/" & myday & "/" & myyear
Debug.Print dtDate
End Sub

worked.

You can try
msgbox isdate(mymonth & "/" & myday & "/" & myyear) & " " & _
mymonth & "/" & myday & "/" & myyear


to see if vba sees your string as a date

Regards,
Tom Ogilvy


"Brandon" wrote in message
...
Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a
vague error message.

Excel Macro Strings
 

Brandon,

I won't guarantee anything, but you can give this a try, I can't test it out
because unfortunately I don't have any PLC's though their is an RTU or two
kicking around here. I put in some CStr (Convert to string) statements
which may be of use.

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = CStr(DDERequest(Channel, "V30145:B"))
PLCDay = CStr(DDERequest(Channel, "V30146:B"))
PLCYear = CStr(DDERequest(Channel, "V30147:B"))

DDETerminate Channel
'''''
PLCDate = CStr(PLCMonth & "/" & PLCDay & "/0" & PLCYear)
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

Dan E

"Brandon" wrote in message
...
Run-time error '13':
Type Mismatch


This is my code:

**********

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = DDERequest(Channel, "V30145:B")
PLCDay = DDERequest(Channel, "V30146:B")
PLCYear = DDERequest(Channel, "V30147:B")

DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

**********

When I retrieve the date values, they are all numbers,
not strings, and also-- the "/0" in the line that assigns
a value to PLCDate, the "0" is because when I retrieve
the year from the PLC (programmable logic controller), it
comes as 03 which becomes just '3'. I think it is just a
variable type inconsistency, like I am using numbers but
it is looking for a String.
Thanks for all the help you're giving, Dan E.

Excel Macro Strings
 

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
'Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = 12 ' DDERequest(Channel, "V30145:B")
PLCDay = 5 'DDERequest(Channel, "V30146:B")
PLCYear = 3 'DDERequest(Channel, "V30147:B")

'DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
curr_tmp = 3
Worksheets("Sheet1").Cells(curr_tmp, 18).Value = DateValue(PLCDate)

End Sub



worked fine for me - there must be something screwed up in your data.


try
msg = "-" & PLCMonth & "<-" & typename(PLCMonth) & vbNewline & _
"- & PLCDay & "<-" & typename(PLCDay) & vbNewline & _
"- & PLCYear & "<-" & typename(PLCYear)

msgbox msg
before PCLDate = . . .



--
Regards,
Tom Ogilvy

"Brandon" wrote in message
...
Run-time error '13':
Type Mismatch


This is my code:

**********

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = DDERequest(Channel, "V30145:B")
PLCDay = DDERequest(Channel, "V30146:B")
PLCYear = DDERequest(Channel, "V30147:B")

DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

**********

When I retrieve the date values, they are all numbers,
not strings, and also-- the "/0" in the line that assigns
a value to PLCDate, the "0" is because when I retrieve
the year from the PLC (programmable logic controller), it
comes as 03 which becomes just '3'. I think it is just a
variable type inconsistency, like I am using numbers but
it is looking for a String.
Thanks for all the help you're giving, Dan E.

Excel Macro Strings
 

Force it into a string.
Dim PLCMonth As String, PLCDay As String, PLCYear As String, PLCDate As
String

Worksheets("Sheet1").Cells(curr_TMP, 18).Value = CDate(PLCDate)



--

John

johnf202 at hotmail dot com


"Brandon" wrote in message
...
Run-time error '13':
Type Mismatch


This is my code:

**********

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = DDERequest(Channel, "V30145:B")
PLCDay = DDERequest(Channel, "V30146:B")
PLCYear = DDERequest(Channel, "V30147:B")

DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

**********

When I retrieve the date values, they are all numbers,
not strings, and also-- the "/0" in the line that assigns
a value to PLCDate, the "0" is because when I retrieve
the year from the PLC (programmable logic controller), it
comes as 03 which becomes just '3'. I think it is just a
variable type inconsistency, like I am using numbers but
it is looking for a String.
Thanks for all the help you're giving, Dan E.

Excel Macro Strings
 

Okay, I think what you asked me to do is what helped
address the problem the best. First, the code...

BEGIN PROGRAM
***

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = CInt(DDERequest(Channel, "V30145:B"))
temp2 = CInt(DDERequest(Channel, "V30146:B"))
temp3 = CInt(DDERequest(Channel, "V30147:B"))
DDETerminate Channel

PLCMonth = CStr(temp1)
PLCDay = CStr(temp2)
PLCYear = CStr(temp3)

PLCDate = PLCMonth & "/" & PLCDay) & "/0" & PLCYear)
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

***
END PROGRAM

The Error code 13 comes into play on the first of the PLC
variable assignment lines: PLCMonth = CStr(temp1)
It gets the value all right from the PLC, but for some
reason, it cannot convert them to String. I checked in my
DSData manual and it says nothing about the data type
they are forced into when they come into an Excel
spreadsheet, but for some reason- whatever it is- it
cannot be converted to a String right away. Does anyone
have any more ideas?

Excel Macro Strings
 

Why not just use typename as I suggested and get the type in English.

Regards,
Tom Ogilvy

Dan E wrote in message
...
Brandon,

You could give VarType a try

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = CInt(DDERequest(Channel, "V30145:B"))
temp2 = CInt(DDERequest(Channel, "V30146:B"))
temp3 = CInt(DDERequest(Channel, "V30147:B"))
DDETerminate Channel

MType = VarType(temp1)
DType = VarType(temp2)
YType = VarType(temp3)

Debug.Print MType, DType, YType

vbEmpty 0 Empty (uninitialized)
vbNull 1 Null (no valid data)
vbInteger 2 Integer
vbLong 3 Long integer
vbSingle 4 Single-precision floating-point number
vbDouble 5 Double-precision floating-point number
vbCurrency 6 Currency value
vbDate 7 Date value
vbString 8 String
vbObject 9 Object
vbError 10 Error value
vbBoolean 11 Boolean value
vbVariant 12 Variant (used only with arrays of variants)
vbDataObject 13 A data access object
vbDecimal 14 Decimal value
vbByte 17 Byte value
vbUserDefinedType 36 Variants that contain user-defined types
vbArray 8192 Array


"Brandon" wrote in message
...
Sorry, I didn't really understand your post. I saw
the "what data type is date" but then you tested
something and said it worked so I thought you found out
yourself. Is there another way to test other than
just "isdate", because that is the only one I found in VB
Excel help. In my last post I showed the problem lies in
the format of the number I am retrieving from an outside
source. It is not a date, I tested the IsDate(temp1), but
I can't test if it is an Int with IsInt, or if it's a
string with IsString. Is there any other way I can find
out what data type the number is stored as? And even if i
do somehow find out, how can something just not be
converted to a String if it is some form of number? I
have never seen anything like this error in any
programming I have done before.
Thanks for any help you can still give me Tom.
-Brandon

Excel Macro Strings
 

what about this: in my 2nd post:

msg = "-" & PLCMonth & "<-" & typename(PLCMonth) & vbNewline & _
"- & PLCDay & "<-" & typename(PLCDay) & vbNewline & _
"- & PLCYear & "<-" & typename(PLCYear)

msgbox msg
before PCLDate = . . .

That tells you what the type of the data is.

Regards,
Tom Ogilvy



Brandon wrote in message
...
Okay, I think what you asked me to do is what helped
address the problem the best. First, the code...

BEGIN PROGRAM
***

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = CInt(DDERequest(Channel, "V30145:B"))
temp2 = CInt(DDERequest(Channel, "V30146:B"))
temp3 = CInt(DDERequest(Channel, "V30147:B"))
DDETerminate Channel

PLCMonth = CStr(temp1)
PLCDay = CStr(temp2)
PLCYear = CStr(temp3)

PLCDate = PLCMonth & "/" & PLCDay) & "/0" & PLCYear)
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

***
END PROGRAM

The Error code 13 comes into play on the first of the PLC
variable assignment lines: PLCMonth = CStr(temp1)
It gets the value all right from the PLC, but for some
reason, it cannot convert them to String. I checked in my
DSData manual and it says nothing about the data type
they are forced into when they come into an Excel
spreadsheet, but for some reason- whatever it is- it
cannot be converted to a String right away. Does anyone
have any more ideas?