PDA

View Full Version : Data import using vba and userforms/dialog boxes etc



cbs81
02-25-2008, 10:10 PM
Hi all

I need help with some vba code that will import data from one workbook to another workbook.. here is the scope in a bit more detail?

I Have two workbooks named:

Feb 08.xls; and
Mt Cotton.xls

Please see the attached VBA Help.zip

For simplicity, in cell A1 in the ?Cash Flow? tab there will be a path where the Feb 08.xls workbook exits. For example in cell A1

c:\Project folder\

Cell A2 will be : MTC

The Feb 08.xls workbook contains the actual data straight from a financial system. The Mt Cotton.xls is an analysis workbook created to analyse the data from the Feb 08.xls workbook.

The Feb 08.xls workbook contains several tabs, but the MTC tab is the tab that I want the VBA code to find and import.

Each month there will be a new workbook created containing the data eg: Mar 08.xls, Apr 08.xls and so forth.. The tabs will remain the same.

I would like a button to appear in the Mt Cotton.xls work book that says import data.

Once the user clicks on this button, I would like the user to be presented with a dialog box that asks the user:

Which month would you like to have imported?

Then the user will type in for this example Feb 08 in the form.

Then below there would be a button that says ?import?.

Once the user clicks on this import button, I would like the code to go into the directory:

c:\Project folder\

and find the workbook named Feb 08 As entered above in the form.

If there is no Feb 08 workbook, then a message appears and says:

?please check the month you would like imported as no source data currently exists?




Ok now for the import and sorting by vba if there is source data. This is the tricky bit.

Once the user clicks import and there is source data in the directory in this case there is Feb 08.xls, then I would like the code to go to the Feb 08.xls workbook then:


Find the tab named MTC as defined in Cell A2 of the Mt Cotton Workbook. (The tab name in cell A2 should be flexible)

Commence on Row D6 To the First Empty Row (in this case D41)

For each item in column D, Copy the data in each month up to Feb 08

Go to the Mt Cotton.xls workbook, Find the Tab named Cash Flow, Find the exact match of the cost item in column D in the Feb 08.xls workbook, find these corresponding titles in the Mt Cotton.xls cash flow tab, copy and paste the Values to the corresponding months for each of the cost items that match from the Feb 08.xls workbook to the Mt Cotton Workbook Cash flow tab.

If there is a non matching row in the Feb 08 workbook that does not match any rows in the Mt Cotton Workbook, I would like the code to copy this full row and paste special values in row 437 with data in the corresponding months..

Lastly, the Totals for each month as per the double line totals need to be imported into row 431 and allocated to the particular month relevant.
Please note that there may still be many rows on the cash flow tab that are empty.. that is ok, as long as All the data from the Feb 08 workbook is imported.

I know this appears to be a pretty hard little task but I am sure it is easy for the right candidate.

Any help is appreciated greatly?

Many thanks to all you VBA and Excel Gurus for reading my challenging post?

mdmackillop
02-26-2008, 12:43 PM
Her's something to get you started. I've used my own locations in the code, so save files to C:\AAA to test as is.

cbs81
02-26-2008, 10:09 PM
Hello Md,

Thank you very much for your excellent response... I sent you a private message to kindly ask if you would accept payment from me to complete this small project using your impressive knowledge and advanced excel and vba skills. You have already finished about 60-70% of the project with this one solution you already gave. I wanted use your excellent knowledge and expertise in devising a solution to this project. Please respond to your private message to let me know your thoughts and I can give you a more detailed spec document of what more needs to be done along with an attachment.

Just so I can send you the spec doc and more attachments, please email me at:

enquiries chandrabusiness-solutions.com (enquiries@chandrabusiness-solutions.com)

Many thanks my friend..

Aj