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
-~----------~----~----~----~------~----~------~--~---
