VB code to access a database and populate new excel sheet

[sykinc]

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!

 

[Smitty]

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]

I just tried doing this function; however, it says that "The data source contains no visible tables"

 

[Smitty]

Have you set up a query in you database to pull the data that you need?

Smitty

 

[sykinc]

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]

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:

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]

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

 

[sykinc]

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]

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]

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.