Help calling a vbs from a bat

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

 

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?

 

can you post the xls you are working from, and the bits of data you need to get out of it.

 

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

 

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%)?

 

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.

 

Anyone else able to point me in the right direction? I'm still struggling with this.

 

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?

 

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?

 

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.

 

Thanks! I would really appreciate it.

 

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.

 

And? Did you get a chance to have a look at it?

 

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.

 

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.

 

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
    

 

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

 

Well does it actually work? *grin*

 

No. both ways it just continues through with no error but the file is not updated.

 

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.