VB code to access a database and populate new excel sheet

sykinc

New Member
Joined
Nov 11, 2005
Messages
17
Hi. I have a project that is time senstitive and I am completely lost. Here is the situation: I have a database with over 5000 High Yield bonds listed. Each bond is on a different row (some companies have multiple bonds). Accross the row is data for each bond i.e. cusip, company, industry, coupon (interest rate), ratings, current price etc. I am supposed to filter through this database and create a new sheet that lists "distressed" bonds that are a) below a certain rating, and b) below a certain price. This new sheet is supposed to be sorted by a) company and b) industry. This program should be able to access the database and update info (prices/ratings) on demand. But also, the program should be able to re-populate the sheet with up-to-date listings i.e. on any given day, it should include bonds that are newly distressed and take away bonds that have improved in rating or price. This is asking alot, but I would really like this program to start with a pop-up screen that asks for parameters to search by i.e. maximum rating, and maximum price. I bought a VB book, but I need to get this done fast and I am completely lost. Please help! I appreciate any advice or guidance. Thank you in advance!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Excel has an external database query feature that lets you pull from a database query or table.

Goto Data-->Get External Data-->New Database Query & follow the wizard from there.

Once you get it set up you can refresh the Data with Data-->Refresh Data.

Hope that helps,

Smitty
 

sykinc

New Member
Joined
Nov 11, 2005
Messages
17
I just tried doing this function; however, it says that "The data source contains no visible tables"
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Have you set up a query in you database to pull the data that you need?

Smitty
 

sykinc

New Member
Joined
Nov 11, 2005
Messages
17

ADVERTISEMENT

Hi. I tried setting that up, but it still didn't work. Is tha the best way to go about this problem? I'm really trying to get this automated with parameter entrys. Any ideas?
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Here goes.

MS access database in C:\Temp called Test.mdb. It has a table called MyData with fields Cusip, Company, Industry, Coupon, Ratings and Price. Coupon and Price are Double, while the rest are text. Values of the Ratings are Bad and Good - for want of anything else.

Userform (called userform1) with a textbox (textbox1) that takes the price and Combobox1 (to take the ratings) and a commandbutton.

Output is going to sheet2 in the activeworkbook.

Code:
Code:
Private Sub CommandButton1_Click()

 Dim rst As Recordset
 Set wrj = createworkspace("", "admin", "", dbusejet)
 Set db = wrj.OpenDatabase("c:\temp\test.mdb")
 Set rst = db.OpenRecordset("select * from [mydata] where [mydata].ratings = """ & ComboBox1.Value & """ and [mydata].price < " & Val(TextBox1.Value) & " order by [mydata].company, [mydata].industry")
 
 Set sh = Sheets("sheet2")
 sh.Range("a:f").ClearContents
 
 sh.Range("a2").CopyFromRecordset rst
 
 db.Close
 Set sh = Nothing
 Set db = Nothing
 Set rst = Nothing
End Sub

Private Sub UserForm_Initialize()
 ComboBox1.AddItem "Bad"
 ComboBox1.AddItem "Good"
End Sub

The initialise is simply to put the options into the combobox. The commandbutton will extract the data from the database and output to A2 in sheet2 of the current workbook.

HTH

Tony
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259

ADVERTISEMENT

Hello sykinc:
What type of DataBase are you using as the source ? Access , Oracle, Progress , MySQL , MS SQL ... ??
 

sykinc

New Member
Joined
Nov 11, 2005
Messages
17
I guess I mis-worded my question because it is not a database per se but a large excel spread sheet with approximately 4000 rows and columns that extend to the limit of excel.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Have you considered using the inbuilt Data, Form functions? This will put a form over an existing set of data, allowing you to search, delete, update etc.


Tony
 

sykinc

New Member
Joined
Nov 11, 2005
Messages
17
The problem is that its not my excel/psuedo-database that I am accessing. It is an excel sheet that gets updated everyday on the network drive. Also, when I go into the file on the network and try Data->Form, it informs me that the field is too large to use this function. I need a program that runs, grabs the data from this excel sheet in the network drive, and organizes it on my computer (or anyone else that uses the program). From then on, this program should just reference the network drive data and update info.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top