Help calling a vbs from a bat

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

  1. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    I generally setup ranges on the sheet I want to search, then it's as simple as using offsets to query cells.

    IE: setup a range on your worksheet called RNG_Source - then you can build a loop to setup a process.

    Code:
    Dim lng2return As Long
    Dim Lng1offset As Long
    Dim strSourceID As String
    
    'Loop through the references getting the user ID
    'Set the opening grid reference for the Retrieve Loop
    Lng1offset = -1
    Do While 1 = 1
        Lng1offset = Lng1offset + 1
        With range("RNG_Source").Offset(Lng1offset, 0)
            strSourceID= Trim(.Cells(1, 2))
        End With
        
    'Stop loop as soon as there is no IDlisted
        If strSourceID= "" Then Exit Do
       
    [I]   'Perform whatever task you want here, using the variable 
    Perform Task  strSourceID[/I]    
        Loop
    That piece of code will loop through a column of data, parsing the variable and performing the task then going to the next variable, stopping as soon as there are no more ID's in the column (If strSourceID= "" Then Exit Do)

    Edit : sorry, the .cells(1,2) reference will actually pickup the first row (current row) and the 2nd Column (hence the ,2 reference).

    Looking at your data you could quite easily do this as required:
    Code:
    With range("RNG_Source").Offset(Lng1offset, 0)
        strUserFirst= Trim(.Cells(1,1))
        strUserSecond= Trim(.Cells(1, 2))
        StrSourceID= Trim(.Cells(1, 3))		
    End With
    
     
    Last edited: Aug 14, 2013
  2. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Sorry I have gone through this a million times and I just don't understand.

    Maybe pasting the script that I have will help, but it might just confuse me more as I have really just thrown bits together just so I could get a feel for what steps had to be done. I will just change the path of the server and leave everything else in.

    You can see that I set the variable to strFirstAddress and that is returning A3 (for an example). What I feel that I need to do is then change that to A5 as the 5th column is where the TA is stored and A was the row that had the right UID..

    Code:
    'Const
    Const xlValues = -4163
    Const ForReading = 1
    Const ForWriting = 2
    
    'Variables for opening User ID List
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    
    'Open qik.properties and copy whole file to variable then close file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\SABRE\Apps\Turbo\7.0\app\qik.properties", ForReading)
    strText = objFile.ReadAll
    objFile.Close
    
    'Set variables
    strUsername = objnetwork.UserName
    
    'Open User ID List and select first worksheet
    Set objWorkbook = objExcel.Workbooks.Open("\\servername\Share\User ID List.xls")
    Set objWorksheet = objWorkbook.Worksheets("Sheet1")
    
    'Select range in excel (all data)
    Set objRange = objWorksheet.UsedRange
    
    'Find object - Will find user name
    Set objTarget = objRange.Find(strUsername)
    
    'Echo cell found and set to variable "strFirstAddress"
    If Not objTarget Is Nothing Then
        Wscript.Echo objTarget.AddressLocal(False,False)
        strFirstAddress = objTarget.AddressLocal(False,False)
    End If
    
    'Used to stop the search once object is found
    Do Until (objTarget Is Nothing)
        Set objTarget = objRange.FindNext(objTarget)
    
        strHolder = objTarget.AddressLocal(False,False)
        If strHolder = strFirstAddress Then
            Exit Do
        End If
    
        'Used to echo response
    	Wscript.Echo objTarget.AddressLocal(False,False)
    	
    	'My code to replace text in stored variable before writing
    strNewText = Replace(strText, "TA=ABCDEF", strFirstAddress)
    
    'Open qik.properties for writing and write line then close file
    Set objFile = objFSO.OpenTextFile("C:\SABRE\Apps\Turbo\7.0\app\qik.properties", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close
    'End my code
    Loop
    Does that make sense I am I totally barking up the wrong tree like I think I am?
     
  3. PabloEscobar

    PabloEscobar Member

    Joined:
    Jan 28, 2008
    Messages:
    14,419
    can you post the xls you are working from, and the bits of data you need to get out of it.
     
  4. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    This is just a sample but the headings are the same and the format of each cell is the same.
    There are more worksheets in it as well but they do not get used by my department.

    http://www.filedropper.com/sample_5
     
  5. PabloEscobar

    PabloEscobar Member

    Joined:
    Jan 28, 2008
    Messages:
    14,419
    And what bits in the spreadsheet map to a variable you have access to on the client?

    is the User ID what the user logs into the computer as (%username%)?
     
  6. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Yes, I will pull the user variable form the logged in user then I wish to use that to find the right row (by looking up the UID) and pull the Primary TA from that spread sheet.
     
  7. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Anyone else able to point me in the right direction? I'm still struggling with this.
     
  8. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    Right, can I confirm your requirements here:

    • You need to update a specific field in the the qik.properties file to a new data field
    • You've got an excel file with a list of details, but the important ones are User ID and I'm guessing Matrix User ID
    • You've got a variable called strUsername which you're created which is the windows username
    • From that, you basically want to do a vlookup to the Matrix User Id, and do a find and replace in your qik.properties file (well, variable at this point) and then you want to rewrite the updated variable back to the qik.properties file?
     
  9. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    You need to update a specific field in the the qik.properties file to a new data field
    You've got an excel file with a list of details, but the important ones are User ID and TA Primary
    You've got a variable called strUsername which you're created which is the windows username
    From that, you basically want to do a vlookup to the Primary TA, and do a find and replace in your qik.properties file (well, variable at this point) and then you want to rewrite the updated variable back to the qik.properties file?

    But that is what I am after, just the different field that I am looking up..
    The fact you are asking that make me think that I am way off on my script. Do I need to just start again?
     
  10. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    I wouldn't say you need to start again - think you just were getting a bit confused in the code.

    Let me have a look at it tonight and i'll try to modify your code to get something working.
     
  11. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Thanks! I would really appreciate it.
     
  12. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    okay - I'm tired and it's been a day, so excuse the crappiness of the code:

    Here is a nice simple version of doing what you're attempting.

    Code:
    Sub OCAU()
    
    
    'Const
    Const xlValues = -4163
    Const ForReading = 1
    Const ForWriting = 2
    
    'Set variables
    Dim strUsername As String
    strUsername = "UAXA123"
    
    'Open qik.properties and copy whole file to variable then close file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("c:\users\tekin\documents\ocau.txt", ForReading)
    strText = objFile.ReadAll
    objFile.Close
    
    
    '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\tekin\documents\sample.xlsm")
    Set objWorksheet = objWorkbook.Worksheets("Sheet1")
    
    'Perform a lookup on the User ID column to find your string
    Dim foundrange As Excel.Range
    Dim strnewuserId As 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
            strnewuserId = foundrange.Offset(0, 4).Value
        Else
            MsgBox ("User Id not found!")
            Exit Sub
        End If
            
        
    'Return the offset (4 columns right) value of the Username
    strNewText = Replace(strText, "999999", strnewuserId)
    
    'Open qik.properties for writing and write line then close file
    Set objFile = objFSO.OpenTextFile("c:\users\tekin\documents\ocau.txt", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close
    
    
    End Sub
    
    Obviously I've changed excel files etc - but the logic is there.

    If you want to PM me your email address I'll zip up an example and send it to you.
     
  13. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    And? Did you get a chance to have a look at it?
     
  14. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    I started modifying it on Friday but ran out of time as we had people off sick.
    I will try it in the next hour.
     
  15. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Firstly, thanks so much for this. It has a much better flow to it now and is a lot easier to understand what it is really doing for each step.

    However, I keep getting an error on the line
    Dim strUsername As String

    I changed this to the following as I wish it to get the UID automatically
    strUsername = objnetwork.UserName
    then it stops on one of these lines instead
    Dim foundrange As Excel.Range
    Dim strnewuserId As String

    Always stops on the As part..
    I read up on the net about it and I can not see any reason that it should not work.
     
  16. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    Hey - sorry I was writing this at home so used vba in excel rather than VBS (forgot you were using that).

    Erm....vbs doesn't handle ranges it seems. Give this a crack without the declarations, but I'm not sure if ti's going to like the range.

    Code:
    '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
            strnewuserId = foundrange.Offset(0, 4).Value
        Else
            MsgBox ("User Id not found!")
            Exit Sub
        End If
        
    
     
  17. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    Assuming that I have done it right, it just goes through with no error anymore.

    I am not sure if it should say "sheet1" or have the name that they have called it as below..

    Code:
    Sub OCAU()
    
    
    'Const
    Const xlValues = -4163
    Const ForReading = 1
    Const ForWriting = 2
    
    'Set variables
    'Dim strUsername As String
    strUsername = objnetwork.UserName
    
    'Open qik.properties and copy whole file to variable then close file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\SABRE\Apps\Turbo\7.0\app\qik.properties", ForReading)
    strText = objFile.ReadAll
    objFile.Close
    
    
    '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("\\Server\share\User ID List.xls")
    Set objWorksheet = objWorkbook.Worksheets("User Id - Name")
    
    '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
            strnewuserId = foundrange.Offset(0, 4).Value
        Else
            MsgBox ("User Id not found!")
            Exit Sub
        End If
        
    'Return the offset (4 columns right) value of the Username
    strNewText = Replace(strText, "TA=ABCDEF", "TA=" & strnewTA)
    
    'Open qik.properties for writing and write line then close file
    Set objFile = objFSO.OpenTextFile("C:\SABRE\Apps\Turbo\7.0\app\qik.properties", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close
    
    
    End Sub
     
  18. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    Well does it actually work? *grin*
     
  19. OP
    OP
    UserInterface

    UserInterface Member

    Joined:
    Jan 13, 2011
    Messages:
    360
    No. both ways it just continues through with no error but the file is not updated.
     
  20. Tekin

    Tekin Member

    Joined:
    Nov 16, 2002
    Messages:
    4,039
    Location:
    Elsewhere.
    Yeah - you're going to have to walk through it to work out which specific piece isn't working.

    I'd recommend echoing your variables out to seperate files at stages to see what is being updated.

    If I had to guess, I'd say its the range that's not working - I'm not familar enough with vbs script (rather than vba) to work out why it's not working.

    You may need to move it to an array - which I don't think is that difficult.
     

Share This Page

Advertisement: