• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

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
0
tracyms
Asked:
tracyms
  • 12
  • 11
1 Solution
 
aikimarkCommented:
You can do this with ADODB connection and recordset objects.  You only need to change the parameters of the connection object.
0
 
tracymsAuthor Commented:
?
0
 
aikimarkCommented:
tracy

Did you bother to search for "ADODB" before you posted your comment?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tracymsAuthor Commented:
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
 
NVITCommented:
> 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
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
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
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
Please post a workbook with sample data.
0
 
tracymsAuthor Commented:
I've attached it. Thanks!
Test-groupname.xlsx
0
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
tracymsAuthor Commented:
Oops the link didn't show up Link
0
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
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
 
tracymsAuthor Commented:
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
 
aikimarkCommented:
@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.
1
 
tracymsAuthor Commented:
I need to re-do what I'm asking.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now