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

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


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]


12,'fat','red',[url removed, login to view]

4,'dead','red',[url removed, login to view]


1,'fat','red',[url removed, login to view]

1,'dead','red',[url removed, login to view]


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]


52,'fat','red',[url removed, login to view]

Taidot: SQL, Visual Basic, Visual Basic for Apps

Näytä lisää: visual age, up string, sql price, sql create table price, red black data, e value, vba time, me vba, excel 2013 vba, xlsx, vba e, vb excel, value, row b, fat bottom, data operation, create excel worksheet, excel database visual basic, rows insert, excel row column, excel data automation, data automation sql, sample visual basic excel, application vba, sql operation

About the Employer:
( 22 reviews ) Miami, United States

Projektin tunnus: #4228144