A code to import other xls data into one xls file

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.
0
Utf
1/17/2010 12:21:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1292 Views

Similar Articles

[PageSpeed] 56

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

"alish" <alish@discussions.microsoft.com> wrote in message 
news:E2A855DA-C23A-4C88-93AA-36A77AAD0741@microsoft.com...
> 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



0
Roger
1/17/2010 5:19:58 PM
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.

"Roger Govier" wrote:

> 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
> 
> "alish" <alish@discussions.microsoft.com> wrote in message 
> news:E2A855DA-C23A-4C88-93AA-36A77AAD0741@microsoft.com...
> > 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
> 
> 
> 
0
Utf
1/18/2010 5:26:01 AM
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

"alish" <alish@discussions.microsoft.com> wrote in message 
news:3BEFE04F-BC1B-4B8D-8B51-CE47F0B0F7F7@microsoft.com...
> 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.
>
> "Roger Govier" wrote:
>
>> 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
>>
>> "alish" <alish@discussions.microsoft.com> wrote in message
>> news:E2A855DA-C23A-4C88-93AA-36A77AAD0741@microsoft.com...
>> > 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



0
Roger
1/23/2010 9:57:53 AM
Reply:

Similar Artilces:

Find and replace data in an Excel chart object
Hi, In a workbook it is possible to find and replace data in cells and formulas. Is it possible in any way to find & replace data in series in a chart object? In have charts with more than 100 series on each chart and I would like to be able to change all series on a chart using an automated routine. Thanks Cawen - I give some insights into how to do this with VBA, and include a link to a utility you can try: http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Pelti...

Re: merging a pub file into a pub document?
GbH wrote: > I'm sure I've seen this somewhere in here or a similar newsgroup. > Someone clever has written a script that will import a publisher > document into an existing/open publisher document. > Please can somebody point me at it. > > -- Also why did my post seem to disappear off the server before I could read it? -- -- Wisdom and experience come with age, they say, but I wish I could remember the darn question Both of your posts show -- Don Vancouver, USA up here. "GbH" <Geoff_Hannington@IEE.ORGasm> wrote in message news:u7JmyS0d...

Can't delete file
I just did a system recovery on a Gateway with XP Pro from the restore partition on the hard drive. One of the restore options was to save the old windows files in a folder called Backup (date). So I did that. After restoring the OS I copied all the files I needed back into the new install from the backup folder. Then I wanted to delete the backup folder. Everything deleted OK except... C:\Backup\Windows\System32\Macromedia\Flash\flash8a.ocx No way in hell can I get it deleted. "access denied". I booted into Safe Mode where I could get to the security options and no mat...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Importing contacts
How do you go about inporting your contact list. Help needed From which program? You normally export from one program in CSV format, then import that. "wmcook" <wmcook@discussions.microsoft.com> wrote in message news:DA4E6745-2E3C-4D53-8824-195C74B93B43@microsoft.com... > How do you go about inporting your contact list. > > Help needed Export from Outlook Express to Windows Live. I have the Outlook Express CSV contact List a small USB drive. I just need to know how to inport them in Windows Live. I would apppreciate it, if you could outline ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

How to Import DVD into Apple iMovie for Further Editing
From: [url=http://www.applemacvideo.com/articles/how-to-put-dvd-into-apple-imovie.html#179]Put DVD into Apple iMovie[/url] One of the benefits about digital video is that the videos can be shared with family and friends. iMovie is created for this purpose, just several steps, you can create your own videos and publish them on web, like YouTube, or enjoy them on iPod, iPhone, and Apple TV. However, the formats which iMovie supported are very limited: 3GP,MOV and MP4. If you want to import DVD to iMovie, you need to convert DVD to iMovie compatible video first. A [url=http://www.applemacvide...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Import exceptions into Junk Senders list ?
Is there a way to import email address (all my contacts) into the Junk Senders Exceptions list? Or must I add email addresses to the Exceptions list one at a time? Thankyou. What version of Outlook? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. "wolfv" <anonymous@discussions.microsoft.com> wrote in message news:1816401c41f35$8bcd6cc0$a601280a@phx.gbl... | | Is there a way to import email address (all my contacts) | into the Junk Senders Exceptions list? | | Or must I add email addresses to the Exceptions list one | at a...

Can youp propagate changes to inserted text from file?
If you insert "text from file" in a document, but later make changes to the source file, can the changes to the source file be propagated to the document where you inserted the text? If you insert a link to the source file. Otherwise no. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>>...

extracting data #3
Extracting data from a single cell Hello I am using Excel 2002, I have 4000+ records each cell has words date and cost in them. I would like to extract the cost and the tim elements from the cells can any one help. Am I in the right forum ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Assuming the data starts in A2, in E2 put =TRIM(LEFT(A2,MATCH(TRUE,ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2)))+1,1)) ,0))) entered with ctrl + shift & enter, copy dow...

Schemas, imports and namespaces
I'm a real XML novice, but my ultimate goal here is to get a workable schema for the GEDCOM XML format as spec'ed out here: http://www.familysearch.org/GEDCOM/GedXML60.pdf It's a proposed XML format for genealogical records. They include a DTD in the spec but sadly its incomplete in that the spec allows for "html" (unlimited HTML or a subset they don't say) in certain elements to allow for formatting. There is also a sample GEDCOM file in the spec with some <html:BR /> elements. There is no tag for "html:BR" in the DTD so it fails on their i...

Transfering Outlook data to a new computer
I just exported the data in my Outlook 2000 folders in an old computer and then imported it to Outlook 07 in my new computer. The newly imported data does not have the original date attached as to when the message was received. Is there a way to keep the receiving date attached to the message? Thanks Paul R Paul R <prenfree@hotmail.com> wrote: > I just exported the data in my Outlook 2000 folders in an old > computer and then imported it to Outlook 07 in my new computer. The > newly imported data does not have the original date attached as to > when the message was r...

[Announce] Stylus Studio 2007 XML Enterprise Suite Released: XML Pipeline, XML Publishing and Data Conversion API's
Stylus Studio 2007 XML Enterprise Suite is now available for free trial download, featuring all-new tools for XML Pipelining, XML Publishing, powerful new Data Conversion APIs and so much more at new reduced prices. Read on for more information, or download it now at: http://www.stylusstudio.com/xml_download.html The Stylus Studio XML Pipeline The Stylus Studio XML Pipeline tool is the world's first true integrated development environment for XML programming because it provides a comprehensive way to model, edit, debug, and deploy the entire application, rather than just the individual bi...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

how to write data to file
I wonder if anyone knows a simple way to write a selection of fields from a single record out to a local file on the server. I then need to automate this in a simple way. click a button or workflow process. Thanks this is a job for the SDK..pretty simple but requires coding ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "DMcL" <DMcL@discussions.microsoft.com> wrote in message news:F3542CF5-1340-4167-9566-047B5AFDE409@microsoft.com... >I wonder if anyone knows a simple way to write a selection of fields from a > single...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Access file types?
I have created an Access database and have designed a form. When selecting "Form View" the forms remains in design view. I then get a new database which is an LDP file which I cannot delete and restricts using the mdp file. Why can I not view the designed form in Form View? It's LDB, not LDP; and MDB, nor MDP The LDB file is a locking database. It cannot be deleted while the main database is open. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Wayne Viles" <WayneViles@discussions...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

folder deleted while trying to delete one file in Outlook Express
I wanted to delete one file but instead it deleted the entire folder. It was not sent to the deleted folder nor to the recycle bin. Why did this happen? More important is there anyway that I can retrieve the files in the folder. Thanks in advance. Gil Hoellerich It happened cause you pressed delete and then confirmed it by pressing yes. I don't believe there is any way to recover it other than by restoring from backup. "G Hoellerich" <ghoellerich@NC.RR.com> wrote in message news:01de01c35a02$42738fd0$a101280a@phx.gbl... > I wanted to delete one file but instead it d...

Dynamic range from a different XLS file for a pivot table
Hello. I have several pivot tables in an Excel-1 file. They all aquire the data from another Excel-2 file where I have a List created, which enlarges everytime I add more data to it. I have created a dynamic name in Excel-2 as explained in http://contextures.com/xlPivot01.html. I would like to use the name of the data range in Excel-2 in the pivot tables as its source data in Excel-1. Unfortunately, when I try to use that name as an external source for the pivot tables, it doesn't appear. Do you know how could I do that? Maybe I could create some cell with the data range in Excel...

One of lifes little mystery?
IF function if you omit the false parameter, the function returns "FALSE" =IF(A1="Bob",TRUE) so why doesn't it return "TRUE" so you can omit the true parameter e.g =IF(A1="Bob") whilst everyone reading this post knows the answer (clue: it's either TRUE or FALSE) - excel doesn't? - it kicks up syntax error Doh! But you can use =A1="Bob" -- Kind regards, Niek Otten Microsoft MVP - Excel "JethroUK´┐Ż" <reply@the.board> wrote in message news:T9NVg.3464$w07.2842@newsfe6-win.ntli.net... | IF function | | if yo...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

open/import Lotus wk4 files into Excel 2007 w\ formulae intact
I do not access to Lotus Smartsuite and need to import an old Lotus wk4 spreadsheet into Excel with the formuale intact. It would be huge hassle to recreate it and I am not going to invest in Smartsuite. thanks hi 2007 does not support lotus. so you need to find someone with lotus or check in to open office. it's free. http://openoffice.org regards FSt1 "jrj19" wrote: > I do not access to Lotus Smartsuite and need to import an old Lotus wk4 > spreadsheet into Excel with the formuale intact. It would be huge hassle to > recreate it and I am not going to invest i...