Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?

Vbscript Lookup and Return

Posted on 2017-10-12
23
Medium Priority
?
59 Views
Last Modified: 2017-10-16
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
0
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
23 Comments
 
LVL 46

Expert Comment

by:aikimark
You can do this with ADODB connection and recordset objects.  You only need to change the parameters of the connection object.
0
 
LVL 1

Author Comment

by:tracyms
?
0
 
LVL 46

Expert Comment

by:aikimark
tracy

Did you bother to search for "ADODB" before you posted your comment?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:tracyms
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.
0
 
LVL 25

Expert Comment

by: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#a42309904
0
 
LVL 46

Expert Comment

by: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.
0
 
LVL 1

Author Comment

by:tracyms
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.
0
 
LVL 46

Expert Comment

by: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.
0
 
LVL 46

Expert Comment

by: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#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.
0
 
LVL 1

Author Comment

by:tracyms
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.How It Works


' 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
0
 
LVL 46

Expert Comment

by: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.
0
 
LVL 1

Author Comment

by:tracyms
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
0
 
LVL 46

Expert Comment

by:aikimark
Please post a workbook with sample data.
0
 
LVL 1

Author Comment

by:tracyms
I've attached it. Thanks!
Test-groupname.xlsx
0
 
LVL 46

Expert Comment

by: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?
0
 
LVL 1

Author Comment

by:tracyms
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?
0
 
LVL 1

Author Comment

by:tracyms
Oops the link didn't show up Link
0
 
LVL 1

Author Comment

by:tracyms
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.
0
 
LVL 46

Expert Comment

by: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?
0
 
LVL 1

Author Comment

by:tracyms
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

0
 
LVL 46

Expert Comment

by: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.
0
 
LVL 1

Author Comment

by:tracyms
I'm going to close this question. I didn't realize it would be this difficult. I don't see where I've asked to change so much, I've only taken what NVIT proposed and tweaked it. If it can't be done, that's one thing but I'm don't get this back and forth.

At any rate, I've decided to do a database lookup instead. While the request will be similar to what I want to do here, I hope the response will actually be helpful.
0
 
LVL 46

Expert Comment

by: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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question