PDA

View Full Version : Tool to automatically feed database



TimNL
04-21-2015, 06:44 AM
Hi all,

At my job, we keep an excel sheet as a database with all deals we have done. Per deal, we make a separate excel sheet with all kinds of parameters. A couple of them, let's say 8 per deal (excel sheet), needs to be copied to that database, in order to monitor the total deal value.

That copying is now all done by hand: open sheet, copy value, paste in database. Repeat 7 times. You can image, that takes up quite a lot of time, and is prone to errors. Therefore I'd like to automate that using a macro. I googled how to do this, and I now know how to select the right cells and copy them in the right format to the database. However, I struggle with two things, which appear as really higher level VBA to me. I hope someone could help me with that :). To explain:

1) it's ease to say "open sheet AB1234.xls and copy cel G12". But, I need some window popping up, where I can select any sheet I'd like (that is, the new deal), and that the code will copy cell G12 from that particular sheet. In the most ideal case, I would like to be able to select multiple sheets. So that when I have, let's say, five deals, I can select all 5 and have them imported in one go. (if for now I would need to repeat the import 5 times, I'd be already very happy with that too).
2) it's also easy to say "copy cell G12 from sheet A and paste in cell D23 of sheet B". However, I need every deal to be pasted in the next row. So let's say that my database occupies row 1 to 100, I'd like my code to recognise that the next empty row is row 101, and paste the new deal there. Logically, the deal after that will need to be pasted in row 102 and so on.

Do you guys have any ideas on this? Thanks a lot, would be much appreciated! Best regards, Tim.

Ps, I use Excel 2010 and Mcrosoft Visual Basic for Applications 7.0

sjeter
04-22-2015, 07:04 PM
Is each deal in a different Excel file, or just separate sheet of same file?
I found a lot of info by doing a Google search "VBA excel vba select multiple files open" for using a File Dialog to select multiple files.

You can use this code line to find the last used row in your database spreadsheet.

LastRow = Range("D"& Rows.Count).End(xlUp).Row

TimNL
04-23-2015, 06:29 AM
Thanks a lot sjeter, I'll look into that!