+ Reply to Thread
Results 1 to 6 of 6

DDE Data Streaming Macro

  1. #1
    Registered User
    Join Date
    10-05-2005
    Posts
    16

    DDE Data Streaming Macro

    ' Hello,
    'I am having problems that I hope you all can give some advice on
    'I am trying to set this up so it can easily be positioned in any column by changing initialOffset.

    'The first problem I am having is that when I do that regardless of where I set the
    ' initial offset it only works in col A<n>n and B<n> with initialOffset set to 0

    'With initialOffset set to 1, entering data into b1 and c1 and it is not reliable and somtimes will acknowledge the data in b1 but does not see the data in c1 and I get the itype not found error it wil find the text entered Col B1 but not the type in Col C1.

    'and what really gets me is that I can use any number for the write to spreadsheet
    'and it offsets perfectly
    'I cannot find what is different except one is a write and the other an entry.
    '
    '
    'Another problem is that I want this streaming data to also fill on another sheet
    'I have tried a few things but did not have much luck and my methods gave
    'unreliable results

    Sub requestData()

    ' description vars
    Dim name As String
    Dim iType As String

    ' get description
    Const initialOffset = 0
    name = UCase(ActiveCell.offset(0, initialOffset + 0).Value)
    iType = UCase(ActiveCell.offset(0, initialOffset + 1).Value)

    ' must have symbol, secType
    If name = "" Then
    Beep
    MsgBox ("enter name")
    Exit Sub
    End If
    If iType = "" Then
    Beep
    MsgBox ("enter iTYPE.")
    Exit Sub
    End If

    ' Place in spreadsheet
    Const reqOffset = 10 + initialOffset
    ActiveCell.offset(0, reqOffset + 1).Value = name
    ActiveCell.offset(0, reqOffset + 2).Value = iType

    ActiveCell.offset(1, 0).Activate
    End Sub

  2. #2
    Vacation's Over
    Guest

    RE: DDE Data Streaming Macro

    If at first you do not get an answer try adding more info not just reposting
    the same

    quick read:

    Const reqOffset = 10 + initialOffset

    why are you trying to set a constant = a variable???

    try
    reqOffset = 10 + initialOffset

    also I don't use UCase for things like this just put
    OPTION COMPARE TEXT
    at the very top of the module and all code will ignore case in text
    comparisons.

    IF you repost:

    What happened? What did tou expect to happen?

    Try to limit to one part of the problem per posting, easier to fix one piece
    at a time.


    "rnrss" wrote:

    >
    > ' Hello,
    > 'I am having problems that I hope you all can give some advice on
    > 'I am trying to set this up so it can easily be positioned in any
    > column by changing initialOffset.
    >
    > 'The first problem I am having is that when I do that regardless of
    > where I set the
    > ' initial offset it only works in col A<n>n and B<n> with initialOffset
    > set to 0
    >
    > 'With initialOffset set to 1, entering data into b1 and c1 and it is
    > not reliable and somtimes will acknowledge the data in b1 but does not
    > see the data in c1 and I get the itype not found error it wil find the
    > text entered Col B1 but not the type in Col C1.
    >
    > 'and what really gets me is that I can use any number for the write to
    > spreadsheet
    > 'and it offsets perfectly
    > 'I cannot find what is different except one is a write and the other an
    > entry.
    > '
    > '
    > 'Another problem is that I want this streaming data to also fill on
    > another sheet
    > 'I have tried a few things but did not have much luck and my methods
    > gave
    > 'unreliable results
    >
    > Sub requestData()
    >
    > ' description vars
    > Dim name As String
    > Dim iType As String
    >
    > ' get description
    > Const initialOffset = 0
    > name = UCase(ActiveCell.offset(0, initialOffset + 0).Value)
    > iType = UCase(ActiveCell.offset(0, initialOffset + 1).Value)
    >
    > ' must have symbol, secType
    > If name = "" Then
    > Beep
    > MsgBox ("enter name")
    > Exit Sub
    > End If
    > If iType = "" Then
    > Beep
    > MsgBox ("enter iTYPE.")
    > Exit Sub
    > End If
    >
    > ' Place in spreadsheet
    > Const reqOffset = 10 + initialOffset
    > ActiveCell.offset(0, reqOffset + 1).Value = name
    > ActiveCell.offset(0, reqOffset + 2).Value = iType
    >
    > ActiveCell.offset(1, 0).Activate
    > End Sub
    >
    >
    > --
    > rnrss
    > ------------------------------------------------------------------------
    > rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
    > View this thread: http://www.excelforum.com/showthread...hreadid=474208
    >
    >


  3. #3
    Registered User
    Join Date
    10-05-2005
    Posts
    16
    Thanks for the info!

    I am trying to change the position: left to right on both the typed input data and also the output stream by changing a single number that remains constant and is only changed should I change the layout format.

    I also need the output to be caps.

    The output offsets perfectly however the input does not, using the same technique. I am not sure what is happening to the input, so I filed several cells to see which it picked up...

    Here is a pic: http://i22.photobucket.com/albums/b3...ffsetissue.jpg

    I changed the program as you suggested, removing Ucase and it no longer gave me caps on the output so I put them back in. I left the option compare in at this point.

    To get this to output I select the name cell and hit run and it outputs the selected cell and uses that as the starting point to place that one and all others to their specified location.

    an offset of 0 is col a and an offset of 1 should be col b

    Removing the constant helped, it seems to be stable now however when I enter an offset of 1 it picks cell 1+1 for the input... cellB + 1 or cell C...

    the cyan cells are where I placed the cursor and the yellow is what actually was outputted...

    Option Compare Text
    Dim genId As Integer

    Sub requestData()

    ' description vars
    Dim name As String
    Dim iType As String

    ' get description
    initialOffset = 1
    name = UCase(ActiveCell.offset(0, initialOffset + 0).Value)
    iType = UCase(ActiveCell.offset(0, initialOffset + 1).Value)

    ' must have symbol, secType
    If name = "" Then
    Beep
    MsgBox ("enter name")
    Exit Sub
    End If
    If iType = "" Then
    Beep
    MsgBox ("enter iTYPE.")
    Exit Sub
    End If

    ' Place in spreadsheet
    reqOffset = initialOffset + 10
    ActiveCell.offset(0, reqOffset + 1).Value = name
    ActiveCell.offset(0, reqOffset + 2).Value = iType

    ActiveCell.offset(1, 0).Activate
    End Sub

  4. #4
    Registered User
    Join Date
    10-05-2005
    Posts
    16

  5. #5
    Vacation's Over
    Guest

    Re: DDE Data Streaming Macro

    Hey I like the photobucket.com idea for posting I'll have to look into it

    I think your problem is comming from the use of ActiveCell

    I'm not sure because I only use ActiveCell as a last option and even then
    just use it once, think about it when code is running cut, paste which cell
    is active?

    try

    dim myCell as range
    set myCell = activeCell

    at begining of Code then just use MyCell - which will not change unless you
    change it

    set myCell = MyCell.Offset(1,0)



    set myCell = nothing
    end Sub
    "rnrss" wrote:

    >
    > Did an expansion of that little test and here are the results. again
    > the cyan is the cursor position for each value entered into
    > initoffset...
    >
    > I guess I expected the top output with the bottom cursor positions...
    >
    > It seems to skip a cell when moving the cursor to the right... Note
    > that BBB is missing in the second putput pattern...
    >
    > Here is another pic of the results, hope that clarifies both the
    > problem and what i hope to get this to do...
    >
    > [image:
    > http://i22.photobucket.com/albums/b3...tissue002.jpg]
    >
    > rnrss
    >
    >
    > --
    > rnrss
    > ------------------------------------------------------------------------
    > rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
    > View this thread: http://www.excelforum.com/showthread...hreadid=474208
    >
    >


  6. #6
    Registered User
    Join Date
    10-05-2005
    Posts
    16
    Miller time!
    Thanks and have a great weekend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1