Macro to grab external data and paste values into a master spreadsheet

I was hoping someone could write a quick VB program for me (I’m not very good at coding). Here is what I need it to do:

There are two filenames on a sheet. These filenames are located in M1 and M2. The filenames can change, but the files they represent are located in the same folder/directory as the main Excel spreadsheet.

The macro needs to open the file located in M1. It then copies the data in columns A:D in that workbook and pastes the values in columns A:D of the main Excel spreadsheet (same sheet that we just pulled data from M1). There is only one tab in the external file, but that tab name/sheet name may be different for each external file. The macro should then close the external file.

This process should be repeated on the file located in M2. In this situation the macro copies the data in columns A:B and pastes values the data in columns F:G of the main Excel spreadsheet.

That is all I need the macro to do!

NOTE – When I open these external files, since they were generated by a non-Microsoft program, I always get prompted with the question “Excel found unreadable content in filename. Do you want to recover?”. The answer is always YES – the workbook always open and looks just fine. After answering YES Excel will prompt “Excel recovered your formulas and cell values, but some data may have been lost”. The answer to this prompt is always CLOSE.

NOTE2 – I’m importing external financial data from a 3rd party program into Excel. I have to perform this process on several different companies several times a month. It is a very repetitive process that I’m hoping to replace with a quick macro. I don’t want to actually link to the external files because they are fairly large and it several hinders the performance and size of the master spreadsheet. Thus, I just want to paste in the values.

Thank you very much for your help.

Hi,

This may get you started. Copy to a module.
You will need to change the "LotusTest" workbook name to the workbook you are using.
You will also need to change the workbook path of the workbook you need to open. I referred the code to "Sheet1" you may need to change this.

Please Login or Register  to view this content.

I'm about 80% there I think. I used your code and modified it to do what I needed. Here is the result:

Please Login or Register  to view this content.

Okay...the above works; however, it only works if I correct the errors in the original files that cause this issue:

"When I open these external files, since they were generated by a non-Microsoft program, I always get prompted with the question “Excel found unreadable content in filename. Do you want to recover?”. The answer is always YES – the workbook always open and looks just fine. After answering YES Excel will prompt “Excel recovered your formulas and cell values, but some data may have been lost”. The answer to this prompt is always CLOSE."

If I don't correct those errors first (which consists of opening the document and clicking save), then the macro fails on workbooks.open.

Is there any way to force an open without checking for errors? This is Excel 2007.

Thanks!!

If the button on the error msg is the default button then you can probably do a Application send key.
Try putting it after the workbook open.

Please Login or Register  to view this content.

in the code.


If that doesn't work perhaps another forum member may have a solution.

Well, the send keys didn't work so I tried something I should have done in the first place. I modified the source software to generate a CSV file instead of an XLS file. Now Excel doesn't complain and now Charles' code works just fine.

Thanks a bundle!

inez1982,

Welcome to the forum.
You need to start your own thread doing so may give you a better response.