Return Value Given Code

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
CGooley
Newbie
Posts: 17
Joined: Tue Jun 25, 2019 6:13 pm

Return Value Given Code

Post by CGooley » Mon Jul 08, 2019 1:41 pm

Hello Everyone,

I am wondering about the best way to return a value given another paired value in a list of paired values.

For example:

companycode=A00123

In an excel file, I have:

Col 1. Col 2.
Code Company Name
A00123 Acme Industries
B00213 Fake Company
B00323 Onewordcompany

...and I would like to return a new string variable with the company name, given the company code. I am sure this is very easy to do, I am still just new to Macro Scheduler.

Should I use something like xl sheet to array? Would I be better off placing the in a text or .ini file and using a readln? My first idea would be to use if-statements directly in the program, but would like to avoid this as there are 600+ lines of paired data.

Please let me know what you think? Thank you!

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Return Value Given Code

Post by hagchr » Mon Jul 08, 2019 6:05 pm

Hi, There are many ways to solve this, see one example below. Using VBS, the excel file is opened and the range containing the company data is copied to the clipboard and assigned to a variable. Then, based on the company id in question, the name is looked up.

To use the example, you just need to change to your excel path/name and the correct sheet and range where you have your data.

In case you are not familiar with RegEx. It is an efficient way to search for data. In this case you want to find the row containing your company id and then jump forward in the row and (only) select the company name.

%CompanyCode%\s* will find the company id and any subsequent spaces/tabs in the line. Since you only want what comes after you can add the \K which means forget everything matched so far and start fresh. Then you add the .+ which means look for one or more (any) characters that follows. Since you only want to read until the end of the line you can add the (?-s) in the beginning of the line which is an instruction that . in .+ should match any characters except end of line, ie it will stop there.

This is just one example. You could probably use VLOOKUP directly in VBS to look it up, or create a text file in excel that you read into MS and look it up.... If you do not want to use RegEx you can eg use Separate> command with end of line as delimiter which would give you an array with one row per record. Then you can loop over the records and use Position> to check for the id and then MidStr> and Trim> to extract the name.

Pls come back if unclear.

Code: Select all

VBSTART

Sub LoadTable()
  strPath1 = "C:\Users\Christer\Desktop\TmpCompanyList.xlsx"

  Set objExcel = CreateObject("Excel.Application")
  objExcel.visible=True

  objExcel.Workbooks.Open strPath1

  '' Define Range where company data is found
  objExcel.sheets("sheet1").range("a10:b13").copy

  objExcel.Workbooks.Close
  objExcel.Quit
End Sub

VBEND

// Load the table with ids and names from Excel
// Data is copied to ClipBoard and then assigned to var tblCompanyData
VBRun>LoadTable
GetClipBoard>tblCompanyData,0
Wait>0.1

// Example ID to look up
Let>CompanyCode=B00213

// Look for the ID in the table
Let>tmp0=(?-s)%CompanyCode%\s*\K.+
RegEx>tmp0,tblCompanyData,0,m,nm,0

If>nm=0
  MDL>ID missing
EndIf

If>nm=1
  MDL>m_1
EndIf

If>nm>1
  MDL>More than one entry for this ID
EndIf



CGooley
Newbie
Posts: 17
Joined: Tue Jun 25, 2019 6:13 pm

Re: Return Value Given Code

Post by CGooley » Mon Jul 08, 2019 6:25 pm

Thank you for your help!

I figured this out today: not sure if your solution is a more robust option. I will look and see!

CSVFileToArray>filepath.csv,csvData

If>csvData_count>0
Let>row=0
Repeat>field1
Let>row=row+1
Let>field1=csvData_%row%_0
Let>field2=csvData_%row%_1
Until>field1=identifier_2
Endif

message>field2

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Return Value Given Code

Post by hagchr » Mon Jul 08, 2019 7:13 pm

I feared one of those mega sheets with a lots of data which would have "clogged" any array. But if your excel sheet is simple with just the table and not too many rows then I think your solution is better.

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts