Help calling a vbs from a bat

Discussion in 'Programming & Software Development' started by UserInterface, Aug 9, 2013.

  1. Rezin

    Rezin Member

    Joined:
    Oct 27, 2002
    Messages:
    9,490
    Code:
    strNewText = Replace(strText, "TA=ABCDEF", "TA=" & [COLOR="Red"]strnewTA[/COLOR])
    Are you using the correct variable? It's nowhere else in that code block.

    Edit: strnewuserId, is what it should be.
     
  2. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    My bad, I changed it because it was easier for me to know what the step was, but then I changed it back before posting and missed one.
    On my end they are both strnewTA.

    Shouldn't I get an error of some sort if it is not working or get the message "User Id not found!"? Because it just looks like the script runs but nothing happens.
    How can I work out what point it fails at?
     
    Last edited: Aug 27, 2013
  3. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    Yeah - you're going to have to echo your variables out to files.

    Easiest way would be to do as follows:

    Set objFile = objFSO.OpenTextFile("c:\stepone.txt", ForWriting)
    objFile.WriteLine strUsername, foundrange, strnewuserId, strNewText
    objFile.Close

    Just add that bit into the script (just before the final string update to the file) and see what your variables are at that point - just review the file.

    That way you can see if the foundrange is actually returning a reference and then finding the lookup.

    It 'should' be returning the "User ID not found" if the range isn't working...
     
  4. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    I tried that earlier this morning but had the same issue although I was just trying to echo the username variable and it is quite possible that I did it wrong.

    I just added your lines in instead but get the same problem, script runs with out error but nothing happens.. Checked c:\ and nothing there either.

    I have tried changing the xls to a local path and running. I tried changing the strUsername to the users ID on the machine I am testing on but everything I try just has the same out come, script runs through really quick but does not give an error.
     
  5. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    How are you actually running this script? Just calling a vbs from a batch script?

    It shouldn't be running that fast, you're actually calling an excel open function (with visible = true) - so it should take a decent length of time to run that and excel should actually open in the session.

    If that's not happening, I don't think your script is actually running.
     
  6. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    I am opening CMD and running "cscript script.vbs"
     
  7. revhed

    revhed Member

    Joined:
    Aug 22, 2001
    Messages:
    1,528
    Location:
    Darwin
    The other thing you can do is setup a number of text box popups that get displayed as the script steps through. You can echo variables and text as needed to help you keep track of where the script is up to.

    When satisfied with the script operation just comment them out.

    Example:
    MsgBox("number of files is " & CStr(counter.Count))
     
  8. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    I tried that, I put this here at the start and I don't see it.

    strUsername = objnetwork.UserName
    MsgBox ("Test Text Here")
     
  9. revhed

    revhed Member

    Joined:
    Aug 22, 2001
    Messages:
    1,528
    Location:
    Darwin
    Might be the space you have after "msgbox"

    I created a new VBS file in notepad.

    This was the only line:

    MsgBox("This script is working")

    Launched with:

    wscript /nologo testing.vbs

    works fine!

    Make sure your virus scanner is not interfering with vbs scripts

    Note: I prefer wscript over cscript - especially for deployments by I forget why - it's been a while
     
    Last edited: Aug 27, 2013
  10. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    If i create a test script with just that line it works fine.
    Something in the script is making it fail but it does not give me any information of what that is.

    I tried just this to see if it would open excel (excel just has one row with the needed information) but nothing happens.

    Code:
    Sub OCAU()
    
    
    'Const
    Const xlValues = -4163
    Const ForReading = 1
    Const ForWriting = 2
    
    'Set variables
    'Dim strUsername As String
    strUsername = objnetwork.UserName
    MsgBox("Test Text Here")
    
    'Variables for opening User ID List
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    
    'Open User ID List and select first worksheet
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\UserIDremoved\Desktop\TP2007_Install\test.xls")
    Set objWorksheet = objWorkbook.Worksheets("Sheet1")
    
    'Perform a lookup on the User ID column to find your string
    'Lookup the user Id column and return the range
    Set foundrange = objWorksheet.Range("C:C").Find(strUsername)
    
    'If it has found the user ID, then return the value offset by 4 otherwise exit
        If Not foundrange Is Nothing Then
            MsgBox("User Id IS found!")
        Else
            MsgBox("User Id NOT found!")
            Exit Sub
        End If
    	
    	'Used for logging, will post all the data to a log file that show variables.
    Set objFile = objFSO.OpenTextFile("c:\output.txt", ForWriting)
    objFile.WriteLine strUsername, foundrange, strnewuserId, strNewText
    objFile.Close
    
    End Sub
    I also tested this section

    Code:
    Set objNetwork = CreateObject("WScript.Network") 
    strUsername = objnetwork.UserName
    MsgBox("strUsername")
    But don't know how to break up the rest of it into small part to see what works or not.
     
    Last edited: Aug 27, 2013
  11. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Can you explain this line?

    strnewTA = foundrange.Offset(0, 4).Value

    Does it find the column with the UID's then count 4 cells to the right of that or does it count 4 columns right from first in the sheet like this 0,1,2,3

    I am just wondering if this is the part that does not work as its the bit that I understand the least..

    Or is it this line
    Set foundrange = objWorksheet.Range("C:C").Find(strUsername)
    Is that telling it to only return range of column C (holds the UID's) then when we try to return the offset to get the required cell it is then out of range.. i dunno..
     
    Last edited: Aug 27, 2013
  12. revhed

    revhed Member

    Joined:
    Aug 22, 2001
    Messages:
    1,528
    Location:
    Darwin
    What are you using to create/edit your script? are you just using notepad or something more elegant?

    If your using something like primalscript, you should be using the debugger to step thorough it line by line. If you are only using something like notepad, your can add message boxes at each step to help figure out where things are happening (like a pseudo debugger). It can look a bit ugly, but does work.

    I like notepad++ for quick and dirty script editing (it's free) if you don't have a dedicated scripting tool.
     
  13. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    I'm using notepad ++ as well.
    I've never used a debugger though, I thought that was just for things like C etc..
     

Share This Page

Advertisement: