Open files and copying tha data in excel

December 27, 2011 at 10:56:48
Specs: Windows XP
Hi everyone. I have a problem trying to find a macro to open several files (excel files) to copy some information into another excel.

The details are:

- I have multiple (58) files all in the same directory (all of them excel files). The names are "informe_autorizaciones001";"informe_autorizaciones002" and so on to 58
- Each of those files are identical in format, those are resoult tables from different locations, so the numbers inside are different. These files only have one sheet containing the information I need
- I would need to copy each of those files into a different sheet on the excel i run the macro from (integration file)

That is the basic. If it would be possible to rename each sheet in the integration file with the information appearing in cell B4 of each file that would be extra nice

Thanks everyone for your time and your help!!

Greets
Alex


See More: Open files and copying tha data in excel

Report •


#1
December 27, 2011 at 12:27:35
I'm confused by a couple of your statements:

"These files only have one sheet containing the information I need"

Does that mean that each file only contains a single worksheet or that each file has multiple sheets but you only need the data from one of the sheets in the file?

"I would need to copy each of those files into a different sheet on the ... (integration file)"

Do you mean that you need to copy the sheet from each file into the integration sheet? You can't copy a file into a sheet since a file is a Windows object. You can however copy a sheet from within an Excel file into another Excel file.

"If it would be possible to rename each sheet in the integration file with the information appearing in cell B4 of each file that would be extra nice"

First, you can't say "cell B4 of each file". B4 is a cell reference of a single cell on a sheet in a workbook. The workbook itself, could be called a "file", but it is incorrect to say that B4 is a cell in a file since a file could contain more than one sheet.

That said, it is possible to rename a sheet based on the value in a cell as long as that cell doesn't contain characters that are not permitted in a sheet name.

For example, if B4 contained 12-27-2011 Data, then that sheet could be renamed based on the contents of the cell. However, if the cell contained 12/27/2011 Data, then the contents could not be used as a sheet name since slashes are considered invalid characters as sheet names.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 28, 2011 at 01:37:57
Hi DerbyDad, thanks for your corrections. As English is not my native language I still have some mistakes which I need to correct.

What you said is completely right, each file has only one worksheet, where the information I need is. And therefore, when I said "I would need to copy each of those files" again, as you said, it would be copying that sheet.

Thanks for the labelying infomation, I think it only contains letters, but I'll check just in case.

Thanks for your time and help!


Report •
Related Solutions


Ask Question