Please add the "Microsoft DAO 3.6 library goto Tools> references and add this library after this, macro will run fine.. Let me know for further assistance.
Thank you. On Feb 9, 10:57 am, "Robinson Boreh" <rbo...@gmail.com> wrote: > Hi Rock, > > Thanks for your response. I am a newbie in macro and still need your > assistance. I tried running the macro but getting the following error, > please help > > > > -----Original Message----- > > From: Rock [ <mailto:rak_sha...@yahoo.co.in> mailto:rak_sha...@yahoo.co.in] > > Sent: Monday, February 08, 2010 4:55 PM > > To: Robinson Boreh > > Subject: Re: Extracting data from different excel workbooks > > Hi Robinson, > > I think, this set of codes can help you.. Please made the changes > accordingly. you need to change the sheet name and range. this set of code > will import the data from all the available worksheets from the specified > folder to a workbook without opening the workbooks. > > Sub Main > > Call AppendData > > End Sub > > > > Function GetFolder(ByVal DefaultPath As String) On Error Resume Next Set fd > = Application.FileDialog(msoFileDialogFolderPicker) > > jmp1: > > With fd > > .InitialFileName = DefaultPath & "\" > > .Show > > End With > > GetFolder = fd.SelectedItems(1) > > If GetFolder = Empty Then GoTo errHandler Exit Function > > errHandler: > > MsgBox "Please select a folder." > > GoTo jmp1 > > End Function > > > > > > Sub AppendData() > > Dim folderpath As String > > Dim cnt As DAO.Database > > Dim rst As DAO.Recordset > > Sheets(1).Select > > Range("A2").Select > > folderpath = GetFolder("C:") > > With Application.FileSearch > > .NewSearch > > .LookIn = folderpath > > .SearchSubFolders = False > > .Filename = "*.xls" > > .Execute > > For i = 1 To .FoundFiles.Count > > Set cnt = DBEngine.OpenDatabase(.FoundFiles(i), False, False, "Excel 8.0") > > Set rst = cnt.OpenRecordset("Sheet1$") > > ActiveCell.CopyFromRecordset rst > > Cells(Range("E1").End(xlDown).Row + 1, 1).Select > > rst.Close > > cnt.Close > > Next > > > > End With > > End Sub > > > > > > > > Regards > > Rakesh > > > > > > On Feb 8, 11:41 am, "Robinson Boreh" <rbo...@gmail.com> wrote: > > > Hi people, > > > I have many excel workbooks in one folder. The excel workbooks contain > > > data for various stock. I need to extract the date, share prices and > > > volume of safaricom and put them in one excel worksheet. Please assist. > > > Regards, > > > Boreh > > > Copy of 02-Sept-2009.xls > > > 291KViewDownload > > Robinson Boreh, > > Finance Department, Upper Hill, > > Tel: 2874243, 0711019243 Fax: 2719705 > > “Experience is what you get, if you don’t get what you actually wanted.” > > > > Outlook.jpg > 198KViewDownload > > Notebook.jpg > 3KViewDownload- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
