Closed

VBA module for Excel .xlsx worksheet. Looks up value in SQL tables, based on data in spreadsheet - repost

This project was awarded to slavisatosic for $100 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Project Budget
$30 - $250 USD
Total Bids
13
Project Description

1. Create the simple MS SQL database with the 2 simple table and data listed at the bottom of these instructions.

2. Open the [url removed, login to view] file attached.

3. Write a VBA module that, when run, connects to the SQL database, loops through the first 100 rows of the worksheet, and for each row that has a value in the Age column:
a. gets the Age, Status, and Color values from the row in the worksheet
b. selects 'price' value from the SQL "Dogs" table WHERE the age, status, and color values match the .xlsx row
c. If 1 row is returned, inserts the SQL [url removed, login to view] value into the 'Allowed' column in that row in the worksheet
d. if >1 row is returned, inserts the string "multiple" in the 'Allowed' column for that row in the worksheet
e. If 0 rows are returned, then does the same Select operation from the SQL "Cats" table and inserts the value in the allowed column as described above.
f. If 0 rows are returned from the "dogs" and 0 from the "cats" table, then inserts nothing into the allowed column.

4. If this is even possible:
a. Write a second VBA module (or other VB application), that when an age, status, and color is entered in real-time in a row, the VBA module automatically and asynchronously performs the actions above for that row.
b. If this is possible or not, advise me in your bid

Database:
Create a throwaway MS SQL server database called 'automation'
Create a throwaway table called 'Dogs' with columns
'age' integer,
'status' varchar(20),
'color' varchar(20),
'price' numneric(8,2)

Insert arbitrary data:
22,'fat','blue',[url removed, login to view]
26,'fat','black',[url removed, login to view]
26,'','black',[url removed, login to view]
10,'','green',203.2
12,'fat','red',[url removed, login to view]
4,'dead','red',[url removed, login to view]
1,'','red',2406.8
1,'fat','red',[url removed, login to view]
1,'dead','red',[url removed, login to view]
10,'','blue',890.3
10,'fat','blue',[url removed, login to view]
10,'dead','blue',[url removed, login to view]
89,'','black',[url removed, login to view]
6,'','black',[url removed, login to view]

Create a throwaway table called 'Cats' with columns
'age' integer,
'status' varchar(20),
'color' varchar(20),
'price' numneric(8,2)

Insert arbitrary data:
55,'fat','blue',[url removed, login to view]
55,'fat','black',[url removed, login to view]
56,'','black',[url removed, login to view]
50,'','green',23.2
52,'fat','red',[url removed, login to view]

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online