asked on

Vbscript Lookup and Return

Hello,

The below vbscript is a basic script I found on the web. I would like to use it to look up the entered value in a file or ideally a database and return adjacent value. See notes below. It works as is by entering literal values. I'm ok with just a file lookup, but if can do with SQL database too I'd love to see both ways. Thanks!

' Input Box with a Title
a=InputBox("Enter a Number","Enter Value")

‘User enters a value
If a = "1" Then
‘Value goes in Field2
field2 = a

‘ want this value to lookup field2 value (a) in a spreadsheet or text file and return value from adjacent column ‘and place in field3

field3 = "One"

Else
msgbox "Enter a value for Field3"

End If

Example spreadsheet (if user enters “1”, return “One”):
Value One Value Two
1 One
2 Two

Example text file (if user enters “1”, return “One”):
1,One
2,Two

aikimark

You can do this with ADODB connection and recordset objects. You only need to change the parameters of the connection object.

tracyms

ASKER

aikimark

tracy

Did you bother to search for "ADODB" before you posted your comment?

tracyms

ASKER

No I did not. However I do try and post examples to make it helpful for the experts. That question mark represented more detail in your response. I'd like to move past this and get my question answered please. Can someone respond about how to do this via a file instead of a database. Thank you.

NVIT

> Example text file (if user enters “1”, return “One”):
1,One
The solution here is very similar. It uses an xls file as the source. It outputs to an csv file.

Let me know if you need help with it.

https://www.experts-exchange.com/questions/29056501/I-am-looking-for-VB-Script-that-would-search-for-full-name-or-users-name-in-the-Excel-spreadsheet.html?anchorAnswerId=42309904#a42309904

aikimark

via a file instead of a database

The solution link posted by NVIT is another solution, but it uses Excel automation to retrieve the data. The solution only works if the data is in an Excel workbook. If the data is in a different source (CSV or relational database), you will have very different solutions on your hand. Your question seemed to ask about the need for a general solution that might handle different sources.

I'd like to move past this and get my question answered

I'm trying to help you, but you seem to be searching for a turnkey solution created for you with no effort on your part. If that's the case, you might need to explore the EE Gigs or Live solution paths if you don't get such a solution in this question thread.

tracyms

ASKER

aikimark,

Before I posted for help here, I researched for several days. I also spoke with the support person who handles the application I'm working on this for and he didn't know how to address the issue either. I've posted over 100 questions here and been a member for several years. I usually get great help/feedback/direction and no one ever accused me of not doing my due diligence. I pay for this service to get the experts to help - not to have to engage someone because they've misinterpreted what I said took it personally. Yes, I want to move on because I'm here for a reason related to my work/job. I appreciate your input on a solution. Thank you.

NVIT,

Thanks! I'm actually working with the information you gave and tweaking for my needs. It looks to be very close to what I can use. I'll post back when I get it right.

aikimark

Here's my goto reference for connection strings. They also have connection strings for relational databases.
https://www.connectionstrings.com/excel/
https://www.connectionstrings.com/textfile/

Once you've instantiated your recordset variable, you should be able to use the same .FIND method for all three sources. There may be some difference if a data source lacks a header or uses different column names.

There are many EE closed/answered questions that use ADODB in this manner.

aikimark

Here's a VBA example that uses the SQL to return a row, rather than the .FIND method:
https://www.experts-exchange.com/questions/24672765/Scanning-an-Excel-File-from-Visual-Basic-6-FASTER.html?anchorAnswerId=25156580#a25156580

Depending on the size of the data source and the number of searches/matches you need to perform, a SQL query solution might work for you.

tracyms

ASKER

NVIT,

I used the script in your link and I do have working, however I have a few issues:

If I have thousands of entries, it hangs on looking them up. I anticipate having up to 5000 or more.

Aikimark,

Your above link looks to have a way to help with that but I don’t know how to incorporate into my script (For Each rngCell…). Or if you think the SQL route is quicker.

I went with the file lookup as I thought it would be quicker and easier to implement. I do have a SQL database that I can reference instead but same as before I’m not knowledgeable enough to know how to do that. How difficult is it to take what I posted and make it a quicker lookup with what I have and tweak it for a database lookup if I want to try it that way?

Also, I'm getting an error with the script. The script I'm using is below and I've attached an image of how its working right now.

' Read an Excel Spreadsheet
sFNInput="C:\local\Test-groupname.xlsx"

' User enters value
sInp=Inputbox("Enter Identity text")
If sInp = "" Then WScript.Quit
On Error Resume Next
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
      (sFNInput)
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
sID=objExcel.Cells(intRow, 1).Value
sUser=objExcel.Cells(intRow, 2).Value
sGroup=objExcel.Cells(intRow, 3).Value

      ' If user entered value matches (row 2 matches value before comma in row 1)
            sRes = InStr(1,sID,sInp,1)
If sRes <> 0 Then

      'return row 3
      sResult=sGroup

            ' entered value goes in Field 1 of my application
Field1=sInp

' return value goes in Field2 of my application
      Field2= sResult
End If
intRow = intRow + 1
Loop
objExcel.Quit

aikimark

I've placed your code into a snippet and formatted for readability.

sInp=Inputbox("Enter Identity text")
If sInp = "" Then WScript.Quit
On Error Resume Next
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
      (sFNInput)
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
    sID=objExcel.Cells(intRow, 1).Value
    sUser=objExcel.Cells(intRow, 2).Value
    sGroup=objExcel.Cells(intRow, 3).Value
            
    ' If user entered value matches (row 2 matches value before comma in row 1)      
    sRes = InStr(1,sID,sInp,1)
    If sRes <> 0 Then 

        'return row 3
        sResult=sGroup

        ' entered value goes in Field 1 of my application
        Field1=sInp

        ' return value goes in Field2 of my application
        Field2= sResult
    End If
    intRow = intRow + 1
Loop
objExcel.Quit

Open in new window

* Rather than using your Do While loop, try using the Excel Range.Find method. It is much faster.
* Not sure what you are doing with the result. What are Field1 and Field2.

tracyms

ASKER

Thanks!

* Rather than using your Do While loop, try using the Excel Range.Find method. It is much faster.
Can you put that in my script above? - yes, I could look it up and try to decipher how to use it and where to put it (which I will look it up), but I'm sure you could do much faster.

* Not sure what you are doing with the result. What are Field1 and Field2.
Field1 and Field2 is where the data goes in my application fields from the spreadsheet results

aikimark

Please post a workbook with sample data.

tracyms

ASKER

I've attached it. Thanks!
Test-groupname.xlsx

aikimark

Since this data has three columns, which differs from your initial description, please explain what the user might type into an inputbox and what should be returned by a lookup function?

tracyms

ASKER

aikimark,

I've embedded a screen shot of what the user types in and what's returned. The script I posted has comments (in bold) about how it's used/what it does. I modified a the script posted by NVIT - shown here -

Does that help?

tracyms

ASKER

Oops the link didn't show up Link

tracyms

ASKER

By the way, it doesn't have to have 3 columns. The user would type in an ID and the full name would be returned. I used 3 columns because I tweaked the script posted by NVIT and that's how it worked.

aikimark

This question is in the VBScript zone. I'm not sure where your multi-textbox window comes from. I assume that is your field# reference.

Or is this an Access/VBA runtime environment?

tracyms

ASKER

The mulit-textbox window are the fields in our internal application (custom made before I started) that reads vbscript. It's where the data will go/placed when its found in the spreadsheet. For the purposes of getting what I need, you can use those fields as if they were fields in the spreadsheet. I was hoping the link I posted would help as I basically tweaked the one below. Does this help?

' Prompt user for text.
' Search Excel Spreadsheet.
' Send result to .CSV file

sFNInput="C:\local\Test-groupname.xlsx"
sFNOutput="C:\local\Test-found.csv"

Const ForReading = 1
Const ForAppending = 2
Const ForWriting = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

If objFSO.FileExists(sFNOutput) Then
	objFSO.DeleteFile sFNOutput
End If

sInp=Inputbox("Enter Identity text")
If sInp = "" Then WScript.Quit

Set objWorkbook = objExcel.Workbooks.Open (sFNInput)

' Extract and write header row
sColID=objExcel.Cells(1, 1).Value
sColUser=objExcel.Cells(1, 2).Value
sColGroup=objExcel.Cells(1, 3).Value
sColTitle=objExcel.Cells(1, 4).Value
sColMgr=objExcel.Cells(1, 5).Value
sColRoleobj=objExcel.Cells(1, 6).Value
sHdr=sColID & " | " & sColUser & " | " & sColGroup & " | " & sColTitle & " | " & sColMgr & " | " & sColRoleobj
Set objTextFile = objFSO.OpenTextFile (sFNOutput, ForWriting, True)
objTextFile.WriteLine(sHdr)
objTextFile.Close

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""
    sID=objExcel.Cells(intRow, 1).Value
    sUser=objExcel.Cells(intRow, 2).Value
    sGroup=objExcel.Cells(intRow, 3).Value
    sTitle=objExcel.Cells(intRow, 4).Value
    sMgr=objExcel.Cells(intRow, 5).Value
    sRoleobj=objExcel.Cells(intRow, 6).Value
		
		sRes = InStr(1,sID,sInp,1)
    If sRes <> 0 Then 
    	sResult=sID & " | " & sUser & " | " & sGroup & " | " & sTitle & " | " & sMgr & " | " & sRoleobj
    	WScript.Echo sResult
			Set objTextFile = objFSO.OpenTextFile (sFNOutput, ForWriting, True)
   		objTextFile.WriteLine(sResult)
			objTextFile.Close
    End If
    intRow = intRow + 1
Loop

objExcel.Quit

Open in new window

aikimark

reads vbscript

That doesn't make any sense.

While I appreciate you making progress on this, from the different versions of the code you post, it seems to be changing the problem you/we are trying to solve.

ASKER CERTIFIED SOLUTION

tracyms

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

@Tracy

If and when you create a related question, be sure to include a link to any prior related questions. I didn't realize until your recent post that NVIT had posted code that you were using and the context of the problem.

tracyms

ASKER

I need to re-do what I'm asking.