Selasa, 10 Juli 2012

Re: [ExcelVBA] Consolidation of Excel spread sheet

 

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]

__._,_.___
Recent Activity:
----------------------------------
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

----------------------------------
.

__,_._,___

Tidak ada komentar:

Posting Komentar