Re: [ExcelVBA] Consolidation of Excel spread sheet

That told him :)

Dr B

On 10/07/2012, Paul Schreiner <schreiner_paul@att.net> wrote:
> I think one thing you're going to have to understand is the terminology.
> Not just for communication, but for VBA as well.
>
> An excel FILE is called a Workbook.
> Each workbook will have a file name with an extension like: .xls, .xlsx,
> .xlsm,
> .xlsb
> depending on the version of Excel you're using.
>
> Each workbook must have one or more workSHEETS
> they LOOK like "tabs" in a filing cabinet, so they are sometimes called
> "tabs",
> but VBA will refer to them as "sheets".
>
> The term "spreadsheet" isn't actually accurate as it applies to Excel.
> the first "spreadsheet" was actually a paper Accounting ledger.
> The first purposes of software like Excel and Lotus123 were to computerize
> (and
> automate) these
> spreadsheets. Therefore, the term applied FOR THAT PURPOSE.
> So really, a "spreadsheet" is a specific result, not the tool used.
> That is to say that Excel isn't a "spreadsheet", but you can use it to
> create
> one.
>
> -----------
> If you're looking in the VBA help, and the subject has something to do with
> a
> workSHEET and you look in topics for a workBOOK,
> then you're going to spend a lot of time looking in the wrong place.
>
> If you use the wrong terms when asking questions from the group, you may
> find
> answers are
>
> not relevant because they answer the question that the WORDS asked, not what
> you
> INTENDED to ask.
>
> For instance, if you ask "how do I open a specific spreadsheet"
> well, I COULD answer: "use:  Sheets(sheetname).Select" where sheetname is
> the
> name of the worksheet.
> But really you were asking:  "How do I open a specific Workbook"
> which would be:  "Use: Workbooks.Open("pathname\workbookname.xlsx")"
> ----------------------------------------------------
> So your purpose:
> "how can I consolidate more than 15 excel spreadsheet dta into one
> workbook".
> SEEMS to be more accurate to say:
> "how can I combine 15 excel workbooks into one workbook"
>
> Next is the term "consolidate"
> This is not the same as "combine".
>
> Say you have 15 workbooks: with one workSHEET in each workbook.
> It is possible to COMBINE all sheets into one workbook,
> the result is one workbook with 15 worksheets.
>
> To CONSOLIDATE the workbooks,
> your end result would be to have one workbook, with one worksheet,
> that contains all of the data from the 15 workbooks.
>  ----------------------------------------------------------------
>
> So... it seems that what you're asking is:
> You would like to learn how to write a macro
> that, when executed, will open each Excel workbook in a specified folder
> and copy the sheets (or data from the sheets) into a single workbook.
>
> Basically, the "art" of programming is taking a task described in human
> words
> and translating it into computerized tasks.
>
> From the above description:
> "Write a macro":
>
> Sub Get_Data()
> End Sub
>
> Next: "in a specific folder"
>
> if the folder name is in Cell A1 of Sheet "Sheet1", then:
> PathName = Sheets("Sheet1").Range("A1").Value
>
> now, getting the list of files is a bit trickier...
> The "folder" is part of the Windows File System.
> So, we have to have VBA create a "FileSystem Object"
>
>     Set fso = CreateObject("Scripting.FileSystemObject")
>
> With this Filesystem object, we can get information about the folder
> (including
> the files within the folder):
>
>     Set Fldr = fso.getfolder(PathName)
>     Set Files = Fldr.Files
>
> This creates a VBA "collection" of files.
> We have to look through each file in the collection:
>
>             For Each File In Files
>             Next
>
> Now, what do you want to DO with each of the files?
> Well, you want to OPEN them:
>
>     Workbooks.Open (PathName & "/" & File.Name)
>
> get information about the file
>
>    NumberOfSheets =   workbooks(file.name).sheets.count
>
> close the file:
>
>    Workbooks(file.name).close savechanges:= false
>
> So: putting all of that together, you get:
>
> Sub Get_Data()
>     Dim PathName, fso, Fldr, Files, File
>     Dim NumberOfSheets, RNum
>     RNum = 1
>     PathName = Sheets("Sheet1").Range("A1").Value
>     Set fso = CreateObject("Scripting.FileSystemObject")
>     Set Fldr = fso.getfolder(PathName)
>     Set Files = Fldr.Files
>     For Each File In Files
>         RNum = RNum + 1
>         Workbooks.Open (PathName & "/" & File.Name)
>         '-------------------------------------------------------------
>         ' Do this stuff for each  Excel workbook
>         '-------------------------------------------------------------
>         NumberOfSheets = Workbooks(File.Name).Sheets.Count
>         ThisWorkbook.Sheets("Sheet1").Cells(RNum, "A").Value = File.Name
>         ThisWorkbook.Sheets("Sheet1").Cells(RNum, "B").Value =
> NumberOfSheets
>
> '---------------------------------------------------------------------
>         Workbooks(File.Name).Close savechanges:=False
>     Next
> End Sub
>
> (I added the rNum and Thisworkbook stuff to make it actually DO stuff...)
>
>
> Next, it's YOUR task to describe what it is you actually want to DO with
> each
> file,
> then translate that into computer-speak...
>
>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>
>
>
>
> ________________________________
> From: Nishu Kumar <contactmanish_panwar@yahoo.com>
> To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
> Sent: Tue, July 10, 2012 10:51:04 AM
> Subject: [ExcelVBA] Consolidation of Excel spread sheet
>
>
> Hi Group,
>
> Hope this mail finds you doing well!!
>
> Would like to grab this opportunity to learn as how can i consolidate more
> than
> 15 excel spreadsheet data into one workbook. Would also like to share one
> experience that was seen by me, one of the associate in our org has done
> automation project on the same which i have just said. so what the
> automation
> is, i am now try to explain you in the below bullets:
>
> 1. He opens a workbook (automated one) and mentioned the path in a
> particular
> cell (where all the excel sheets are residing with data).
> 2. He go to the another tab of the same workbook and press a button and
> then.....'booom, all the spreadsheets open one by one,
> closed automatically and
> stores the data in the automated workbook. Isn't so great...
>
> In just two steps consolidation is done...and this is exactly i want to
> learn
> through my group. Here is one thing i am very new to VBA and require great
> assistance from your side.
>
> Thanking in anticipation.
>
> Regards,
> Nishu
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
> ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>

--
Sent from my mobile device




===========================
http://uk.linkedin.com/in/drjohnbullas


------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/