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!

 

[acw]

Hi

So you are accessing a source spreadsheet that has 256 columns of data and bringing in the relevant columns to your sheet which only has 6 columns? Is this correct?


Tony

 

[sykinc]

Yes, but at least six columns. In the future, I might find some information relevant and add a column to pull data. But, yes you've generalized the problem well.

 

[acw]

OK So the inbuild form will work on the sheet with the six columns (I think it will go up to 32 columns).

In the first instance, you will need to bring in the data that matches the relevant criteria to the "condensed" sheet. How do you want to nominate the selection criteria?? Fill in a couple of cells on the output sheet? Or have a button that will ask for your criteria???


Tony

 

[sykinc]

I would prefer to be able to enter the selection criteria, and go from there. What are you thinking of doing with the situation? Thanks for your help.

 

[acw]

Hi

What I'm thinking is that:
1) You enter your selection parameters into 2 cells on the output sheet.
2) Either by change event, or probably by button, initiate a macro which will use an advanced filter to bring data from the "data" sheet to the "working" sheet.
3) Using the inbuilt Form utility on the "working" sheet.

I'm not sure if you want to udpate data on the working sheet, or have it go back to the data sheet. If you only want to do it on the working sheet, then the form will work.

Is this a possibility?


Tony

 

[sykinc]

That sounds like it could work, although I don't know much about this Form function. I will only be updating the "working" sheet. The "data" sheet updates by itself daily (thus I need to open "working" sheet and refresh it). Thanks for your help, please let me know what I need to do.

 

[acw]

Hi

Can you please check your PMs.


Tony

 

[fitzroy]

i have had a very similiar situation if I understand it correctly. We were able to resolve it. Pls let me know if you still need an answer