Hi Kris, First of all let me thank you for this excellent piece of work - the macro worked great [?]like a lightening bolt :).
It definitely solved my requirement (except the name of file in the first
row), yet to bother you a little more which I hope you would not mind, just
want to ask you:
a) is it possible to extract multiple column range, say, J18:L30 and put the
results in columns.
b) is it possible to extract full rows, say, 5 rows from each file and put
the results consolidated (50 rows on a new sheet from 10 files)
Although I don't need the solution for above 2 queries right away but I
would like to save them for future.
I once again would like to thank you for helping me out.
Warm regards,
Abhishek Jain
On Thu, Sep 24, 2009 at 10:17 PM, Kris <krishnak...@gmail.com> wrote:
>
> Hi AJ,
>
> Try this, not tested though.
>
> Sub kTest()
> Dim n As Long, fn As String, a, wb As Workbook, aWB As Workbook
>
> Const MyFolder As String = "F:\DISAUG\" 'adjust to suit
> fn = Dir(MyFolder & "*.xls")
>
> Set aWB = ActiveWorkbook
> With Application
> .ScreenUpdating = 0
> .DisplayAlerts = 0
> .EnableEvents = 0
> End With
>
> Do While fn <> ""
> Application.StatusBar = "Retrieving Data from '" & fn &
> "' ........."
> Set wb = Workbooks.Open(MyFolder & fn, UpdateLinks:=0)
> a = wb.Sheets("Sheet1").Range("j18:j35")
> n = n + 1: aWB.Sheets(1).Cells(1, n).Resize(UBound(a, 1)).Value =
> a
> wb.Close False
> Set wb = Nothing
> fn = Dir()
> Loop
> With Application
> .ScreenUpdating = 1
> .DisplayAlerts = 1
> .EnableEvents = 1
> .StatusBar = False
> End With
> End Sub
>
> Kris
>
> On Sep 24, 4:45 pm, Abhishek Jain <abhishek....@gmail.com> wrote:
> > Dear All,
> >
> > I am stuck at this. All I need to do is to extract a range, say, J18:J35,
> > from Sheet1 of all files placed in a single folder.
> >
> > Additional details (in case they are required) -
> >
> > > File names are like DIS01Aug.xls, DIS02Aug.xls and so on.
> > > Folder Name is DISAUG located at F:\
> > > Range J18:J35 contains a few merged cells.
> >
> > I have tried and found some softwares who extracts value of given cell
> > address but all of them were having one or the other problem. Have tried
> a
> > few macros too....but to no avail.
> >
> > There are like 150 files so opening each one and then manually copy-paste
> > can cause excessive hair loss to me :)
> >
> > I believe, you guys can help me out....a working macro would be ideal.
> The
> > results are required on a new workbook in columns - (data of 1st file in
> > Column A and Second in Column B and so on.) I would be amazed if the file
> > name can be put in the first row of each column.
> >
> > Thanks a lot in advance to you all excel masters.
> >
> > Awating a prompt reply from this group..as usual.
> >
> > Best regards,
> >
> > Abhishek Jain
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
<<inline: 004.png>>
