A code to import other xls data into one xls file

[alish]

Hi there. Can anyone help me in creating a VB code in excel Module that
imports data like file name;direct cost; and year from several xls
spreadsheets [reports_1, reports_2, ...] (from different sheets and cells)
into one spreadsheet [summary] into the following columns accordingly:
(column 1 [file name]: file name; column 2 [direct cost]: dollar amounts,
column 3 [year]: year)? i already have the code that pulls file names from
the folder directory and pastes the data into column 1 [file name]. may be it
will be easier to have the code refer to that column 1. Please note that the
Reports_1, Report_2, etc spreadsheets maintain the same formats and the data
is ocated in the same cells (the same coordinates) and the same sheets with
the same names. thanks in advance.

 

[Roger Govier]

Hi alish

Take a look at the methods produced by Ron de Bruin
http://msdn.microsoft.com/en-us/library/cc793964.aspx

--
Regards
Roger Govier

Hi there. Can anyone help me in creating a VB code in excel Module that
imports data like file name;direct cost; and year from several xls
spreadsheets [reports_1, reports_2, ...] (from different sheets and cells)
into one spreadsheet [summary] into the following columns accordingly:
(column 1 [file name]: file name; column 2 [direct cost]: dollar amounts,
column 3 [year]: year)? i already have the code that pulls file names from
the folder directory and pastes the data into column 1 [file name]. may be
it
will be easier to have the code refer to that column 1. Please note that
the
Reports_1, Report_2, etc spreadsheets maintain the same formats and the
data
is ocated in the same cells (the same coordinates) and the same sheets
with
the same names. thanks in advance.

__________ Information from ESET Smart Security, version of virus
signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[alish]

Roger, I think the code will not work in my case as i have to gather or
import data from the different workbook (not worksheets in the same workbook)
into one summary workbook. The way it is arranged is that in C:\ there is a
folder "Main_Report" where I have the Summary_Report.xls. Every month i dump
several folders with several xls files. And when i hit the button in the
Summary_report with the VB codes assigned to it, shold import the file names
in column 1 (that has been taken care already) and other details into the
respective columns from a couple of work sheets from each xls file dumped in
the main folder.

Since i already have the codes to import the files names into the FileNames
column, may be the code that will import the data from the workbooks should
reference the file names, that way to make sure it pulls the correct data
from the correct file.

I know it is challanging and a unique case, but I would appreciate if you
help me with this situations. Thanks.

Hi alish

Take a look at the methods produced by Ron de Bruin
http://msdn.microsoft.com/en-us/library/cc793964.aspx

--
Regards
Roger Govier

Hi there. Can anyone help me in creating a VB code in excel Module that
imports data like file name;direct cost; and year from several xls
spreadsheets [reports_1, reports_2, ...] (from different sheets and cells)
into one spreadsheet [summary] into the following columns accordingly:
(column 1 [file name]: file name; column 2 [direct cost]: dollar amounts,
column 3 [year]: year)? i already have the code that pulls file names from
the folder directory and pastes the data into column 1 [file name]. may be
it
will be easier to have the code refer to that column 1. Please note that
the
Reports_1, Report_2, etc spreadsheets maintain the same formats and the
data
is ocated in the same cells (the same coordinates) and the same sheets
with
the same names. thanks in advance.

__________ Information from ESET Smart Security, version of virus
signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Roger Govier]

Hi alish

Take another look at the link I gave you.
Ron has a section there called Merging a Range from All Workbooks in a
Folder.
The Sub MergeAllWorkbooks() will do what you want.
It doesn't need a column with a list of the filenames.
You specify the folder where your files for importing are stored, and it
will loop through each of the files found there in turn and extract the
range of data you specify and import it all to one sheet in your Summary
workbook.

Instead of

Every month i dump several folders with several xls files.

dump the files you want imported into a single folder.
--
Regards
Roger Govier

Roger, I think the code will not work in my case as i have to gather or
import data from the different workbook (not worksheets in the same
workbook)
into one summary workbook. The way it is arranged is that in C:\ there is
a
folder "Main_Report" where I have the Summary_Report.xls. Every month i
dump
several folders with several xls files. And when i hit the button in the
Summary_report with the VB codes assigned to it, shold import the file
names
in column 1 (that has been taken care already) and other details into the
respective columns from a couple of work sheets from each xls file dumped
in
the main folder.

Since i already have the codes to import the files names into the
FileNames
column, may be the code that will import the data from the workbooks
should
reference the file names, that way to make sure it pulls the correct data
from the correct file.

I know it is challanging and a unique case, but I would appreciate if you
help me with this situations. Thanks.

Hi alish

Take a look at the methods produced by Ron de Bruin
http://msdn.microsoft.com/en-us/library/cc793964.aspx

--
Regards
Roger Govier

Hi there. Can anyone help me in creating a VB code in excel Module that
imports data like file name;direct cost; and year from several xls
spreadsheets [reports_1, reports_2, ...] (from different sheets and
cells)
into one spreadsheet [summary] into the following columns accordingly:
(column 1 [file name]: file name; column 2 [direct cost]: dollar
amounts,
column 3 [year]: year)? i already have the code that pulls file names
from
the folder directory and pastes the data into column 1 [file name]. may
be
it
will be easier to have the code refer to that column 1. Please note
that
the
Reports_1, Report_2, etc spreadsheets maintain the same formats and the
data
is ocated in the same cells (the same coordinates) and the same sheets
with
the same names. thanks in advance.

__________ Information from ESET Smart Security, version of virus
signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4780 (20100117) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4798 (20100122) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________

The message was checked by ESET Smart Security.

http://www.eset.com