Results 1 to 10 of 10

Thread: VBA Variables... Help!

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    13

    VBA Variables... Help!

    Hello Gents

    I am having some issues with my code so am needing some help.

    Background is im using VBA (i know its horrible but it has to be used for this task),
    I have a GUI that the user deals with, there are multiple text input boxes. the user will input several numbers and hit my 'Get_values' button.

    So if i focus on 1 text box named 'mobelenum'. i want the user to be able to input a number say 5, in to this box. I then want to be able to use this value of '5' to then input in to a XML search...

    pulling the data out of XML isnt too hard, but im finding it hard to get the users input value into my code.... so here is a very small example of what i have been trying to do .


    Private Sub Get_Values_Click()

    'this inputes the value written from user in to cell Z2 on sheet 1.
    Worksheets("sheet1").Range("Z2").Value = mobelenum.Value

    ' Here i want to declare that mobvar will be an Integer
    Dim mobvar As Integer

    'Now setting the Value associated with mobvar (in this case the number 5)
    mobvar = Worksheets("sheet1").Range("Z2").Value

    Set oXML = CreateObject("MSXML.DOMDocument")
    oXML.async = False
    oXML.Load ("c:\\......._17-32-20.xml")
    Worksheets("sheet1").Range("B2").Value = oXML.DocumentElement.SelectSingleNode("Header/description").nodeTypedValue
    Worksheets("sheet1").Range("B3").Value = oXML.DocumentElement.SelectSingleNode("Header/domain").nodeTypedValue
    Worksheets("sheet1").Range("B4").Value = oXML.DocumentElement.SelectSingleNode("Header/date").nodeTypedValue
    Worksheets("sheet1").Range("B5").Value = oXML.DocumentElement.SelectSingleNode("Header/version").nodeTypedValue
    Worksheets("sheet1").Range("B6").Value = oXML.DocumentElement.SelectSingleNode("Header/author").nodeTypedValue
    Worksheets("sheet1").Range("B7").Value = oXML.DocumentElement.SelectSingleNode("Header/source").nodeTypedValue


    'Now this is the bit im getting stuck on.... i want to input the value of mobvar (user inputted number) to then complete the code so that the correct data associated with MobileNumber 5 is exctracted from the XML file.
    Worksheets("sheet1").Range("B8").Value = oXML.DocumentElement.SelectSingleNode("TableContents/MobileNumber[@MobileNumber='mobvar' '']/IPNumber").nodeTypedValue
    any help would be great as im a complete noob when it comes to extracting XML data using VBA.

    Thanks!

    Smerf

  2. #2

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    13

    Re: VBA Variables... Help!

    I have made a bit more of an advance... but its still not correct...

    Dim strXPath As String


    Set oXML = CreateObject("MSXML.DOMDocument")
    oXML.async = False
    oXML.Load ("C:\..............._17-32-20.xml")
    Worksheets("sheet1").Range("B2").Value = oXML.DocumentElement.SelectSingleNode("Header/description").nodeTypedValue
    Worksheets("sheet1").Range("B3").Value = oXML.DocumentElement.SelectSingleNode("Header/domain").nodeTypedValue
    Worksheets("sheet1").Range("B4").Value = oXML.DocumentElement.SelectSingleNode("Header/date").nodeTypedValue
    Worksheets("sheet1").Range("B5").Value = oXML.DocumentElement.SelectSingleNode("Header/version").nodeTypedValue
    Worksheets("sheet1").Range("B6").Value = oXML.DocumentElement.SelectSingleNode("Header/author").nodeTypedValue
    Worksheets("sheet1").Range("B7").Value = oXML.DocumentElement.SelectSingleNode("Header/source").nodeTypedValue

    'varMyVariable = "mobvar"
    mobvar = Worksheets("sheet1").Range("Z2").Value
    Worksheets("sheet1").Range("Z3").Value = mobvar



    Worksheets("sheet1").Range("B8").Value = strXPath
    strXPath = "TableContents/MobileNumber[@MobileNumber=' + mobvar + ']"


    I am now getting the string ("TableContents/MobileNumber[@MobileNumber=' + mobvar + ']") saved in my excel form instead of a number found from the XML sheet.....


    A quick edit......

    Now a change to that last bit of code to:
    strXPath = "TableContents/MobileNumber[@MobileNumber='" & mobvar & "']"

    im not getting the variables within the node all displayed in one cell..... i now need to try and find how to ensure each result is in its own cell instead of all 3 sub node values in 1 cell
    Last edited by smerf; Dec 3rd, 2012 at 10:16 AM.

  3. #3
    Default Member Bonnie West's Avatar
    Join Date
    Jun 2012
    Location
    InIDE
    Posts
    4,060

    Re: VBA Variables... Help!

    Quote Originally Posted by smerf View Post
    ... im using VBA ...
    It seems you've posted in the wrong forum. Here is the Office Development forum.
    On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
    Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: VBA Variables... Help!

    you need to do string concatenation in the VBA ... you've got it in the formula....

    Code:
     ("TableContents/MobileNumber[@MobileNumber='" & mobvar & "']")
    There's two sets of quote marks... the single ticks ' ... ' that are part of the formula... and the double quotes " ... " which signifies dropping out of the string literal back into the VB code....

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    13

    Re: VBA Variables... Help!

    Quote Originally Posted by Bonnie West View Post
    It seems you've posted in the wrong forum. Here is the Office Development forum.
    thanks, will remember that for next time but im 99% sure that VBA and VB is pretty much like for like with a few exceptions.

    techgnome.


    thanks for your responce and explination . its been almost 10 years since i last programmed using VB / VBA so to say im rusty is an understatement !

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VBA Variables... Help!

    Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    13

    Re: VBA Variables... Help!

    instead of starting a new thread i thought i would continue this one on....

    im now hitting a new issue of sub, sub , sub nodes and their values .


    XML example:

    <TableContents>
    - <IPNumber IPNumber="5">
    - <FeedBand FeedBand="1">
    - <Channel Channel="1">
    <CMPWRE>1</CMPWRE>
    <CMPWIM>0</CMPWIM>

    Now my code is not correct for this:

    Dim IPModNum1 As Integer
    Dim Feedsub1 As Integer
    Dim LogChan1 As Integer

    Dim chan As String
    Dim chan1 As String
    Dim chan2 As String
    Dim chan3 As String
    Dim chan4 As String

    'just storing these numbers for checking
    IPNum1 = Worksheets("sheet1").Range("Z50").Value
    Feedsub1 = Worksheets("sheet1").Range("Z51").Value
    Chan1 = Worksheets("sheet1").Range("Z52").Value

    logchan = "TableContents/IPNumber[@" & IPNum1 & "]"
    logchan1 = "TableContents/IPNumber/FeedBand[@FeedBand='" & Feedsub1 & "']"
    logchan2 = "TableContents/IPNumber/FeedBand/Channel[@Channel='" & Chan1 & "']"
    logchan3 = "TableContents/IPNumber/FeedBand/Channel[@Channel='" & Chan1 & "']/CMPWRE"
    logchan4 = "TableContents/IPNumber/FeedBand/Channel[@Channel='" & Chan1 & "']/CMPWIM"


    Worksheets("sheet1").Range("B56").Value = oXML.DocumentElement.SelectSingleNode(logchan).nodeTypedValue
    Worksheets("sheet1").Range("B57").Value = oXML.DocumentElement.SelectSingleNode(logchan1).nodeTypedValue
    Worksheets("sheet1").Range("B58").Value = oXML.DocumentElement.SelectSingleNode(logchan2).nodeTypedValue
    Worksheets("sheet1").Range("B59").Value = oXML.DocumentElement.SelectSingleNode(logchan3).nodeTypedValue
    Worksheets("sheet1").Range("B60").Value = oXML.DocumentElement.SelectSingleNode(logchan4).nodeTypedValue


    I cannot get anyof the sub values from the table ..

    any one able to point out my stupid mistake for me ?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA Variables... Help!

    what is the actual output of logchan? is it correct for selectsinglenode?
    debug.print logchan

    i did post a text parsing method in your other thread
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: VBA Variables... Help!

    Is that even valid XML? Can you have the same name for both the attribute AND node name? To me that's not intuitive... and inaccurate... if they are attributes like that... they should be attributes of the record they belong to... also I don't see any closing tags for them... so I don't know if we have the complete xml doc or not...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    13

    Re: VBA Variables... Help!

    techgnome... yes the XML is valid. it took me ages to get my head around the way it works, but its a very effective form.

    basically:
    IPNumber has a value ranging from 1-10
    FeedBand has a value ranging from 1-1000
    Channel has a value ranging 1-500
    CMPWRE & CMPWIM are just sub values given to each channel

    <TableContents>
    - <IPNumber IPNumber="5">
    - <FeedBand FeedBand="1">
    - <Channel Channel="1">
    <CMPWRE>1</CMPWRE>
    <CMPWIM>0</CMPWIM>
    -----------Closing tags------- (right at the bottom of the XML doc)
    </LogicalChannel>
    </FeederSubBand>
    </IPModuleNumber>
    </TableContents>


    what im trying to do is say search for this:
    <TableContents>
    - <IPNumber IPNumber="5">
    - <FeedBand FeedBand="1">
    - <Channel Channel="1">
    <CMPWRE>1</CMPWRE>
    <CMPWIM>0</CMPWIM>


    and get the values:
    5
    1
    1
    1
    0
    displayed in indvidual Cells in Excel as i will be having to run them through a very complex equation.

    if i run this code:

    Set oXML = CreateObject("MSXML.DOMDocument")
    oXML.async = False
    oXML.Load ("C:\_14-13-21.xml")
    Worksheets("sheet1").Range("B95").Value = oXML.DocumentElement.SelectSingleNode("Header/description").nodeTypedValue
    Worksheets("sheet1").Range("B96").Value = oXML.DocumentElement.SelectSingleNode("Header/domain").nodeTypedValue
    Worksheets("sheet1").Range("B97").Value = oXML.DocumentElement.SelectSingleNode("Header/date").nodeTypedValue
    Worksheets("sheet1").Range("B98").Value = oXML.DocumentElement.SelectSingleNode("Header/version").nodeTypedValue
    Worksheets("sheet1").Range("B99").Value = oXML.DocumentElement.SelectSingleNode("Header/author").nodeTypedValue
    Worksheets("sheet1").Range("B100").Value = oXML.DocumentElement.SelectSingleNode("Header/source").nodeTypedValue


    Worksheets("sheet1").Range("C101").Value = mobnum.Value '(this is the mobile number text box input for the user)
    Dim Test1 As Integer
    Dim Test2 As Integer
    Dim Test3 As Integer


    Dim LgChan As String
    Dim LgChan1 As String
    Dim LgChan2 As String
    Dim LgChan3 As String
    Dim LgChan4 As String

    Test1 = mobnum.Value
    Test2 = FeedBand.Value
    Test3 = Channel.Value


    LgChan = "TableContents/IPNumber[@" & Test1 & "]"
    LgChan1 = "TableContents/IPNumber/FeedBand[@FeedBand=" & Test2 & "]"
    LgChan2 = "TableContents/IPNumber/FeedBand/Channel[@Channel=" & Test3 & "]"
    LgChan3 = "TableContents/IPNumber/FeedBand/Channel[@Channel=" & Test3 & "]/CMPWRE"
    LgChan4 = "TableContents/IPNumber/FeedBand/lChannel[@Channel=" & Test3 & "]/CMPWIM"

    Worksheets("sheet1").Range("B101").Value = modnum.Value
    Worksheets("sheet1").Range("B102").Value = oXML.DocumentElement.SelectSingleNode(LgChan1).nodeTypedValue
    Worksheets("sheet1").Range("B103").Value = oXML.DocumentElement.SelectSingleNode(LgChan2).nodeTypedValue
    Worksheets("sheet1").Range("B104").Value = oXML.DocumentElement.SelectSingleNode(LgChan3).nodeTypedValue
    Worksheets("sheet1").Range("B105").Value = oXML.DocumentElement.SelectSingleNode(LgChan4).nodeTypedValue
    then i get the following outputs:

    B101 - output = 5
    B102 - output = ALL Numbers that are under IPNumber (about 10000 values)
    B103 - output = Both Values for CMPWRE & CMPWIM
    B104 - output = Value for CMPWRE
    B105 - output = Value for CMPWIM

    when really i want something like:
    B101 - output = 5
    B102 - output = just the feedband number
    B103 - output = just the Channel number
    B104 - output = value for CMPWRE (which im getting)
    B105 - output = Value for CMPWIM (which im getting)


    I will push on to see what i can get done this morning to see if i can resolve the above issues

    Smerf

Posting Permissions

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



Click Here to Expand Forum to Full Width