Hi Rolf,
Thanks for responding.
I made the changes as described. Now I am getting an error "Duplicate
declaration in current scope." for statement --> Dim path as string
Please help.
Suyog
On Oct 2, 1:49 am, RolfJ <r...@pacificsound.us> wrote:
> Got it. Try declaring fso this way:
>
> Dim fso As Object
> Set fso = CreateObject("Scripting.FileSystemObject")
>
> On Oct 1, 10:34 pm, suyog_linux <suyog.s...@gmail.com> wrote:
>
>
>
> > Hi Rolf,
>
> > Thanks for responding.
>
> > Please find the answers below :
>
> > 1. I am getting the error which says:
> > "User defined type - not defined" This error is related to first
> > Dim statement in the code above.
>
> > Dim fso As Scripting.FileSystemObject
>
> > 2. Obviously it is critical that you specify the range of cells you
> > want to copy (in the code you showed it is set to "B28:F28". Is that
> > correct?
> > Ans:- Yes
>
> > 3. Also the code assumed that your daily reports contain the date you
> > want to copy on the first worksheet. Is that the case?
> > Ans: Yes
>
> > 4. The code also assumes that all your daily reports, and only your
> > daily reports (!), are located in the folder "C:\test". Is that
> > correct?
> > Ans: Yes
>
> > 5. The code also assumes that the format of your weekly report is
> > identical to the format of the daily reports. Is that the case?
> > Ans: Yes
>
> > Thanks,
> > Suyog
>
> > On Oct 1, 8:03 am, RolfJ <r...@pacificsound.us> wrote:
>
> > > You need to be a little bit more specific in HOW this code is not
> > > working for you. Obviously it is critical that you specify the range
> > > of cells you want to copy (in the code you showed it is set to
> > > "B28:F28". Is that correct? Also the code assumed that your daily
> > > reports contain the date you want to copy on the first worksheet. Is
> > > that the case? The code also assumes that all your daily reports, and
> > > only your daily reports (!), are located in the folder "C:\test". Is
> > > that correct? The code also assumes that the format of your weekly
> > > report is identical to the format of the daily reports. Is that the
> > > case?
>
> > > On Oct 1, 12:43 am, suyog_linux <suyog.s...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I am trying to append multiple excel workbooks into. Basically to
> > > > merge daily reports into a weekly one.
>
> > > > I found the code below somewhere on internet but it is not working for
> > > > me.
>
> > > > Can someone please look and let me know how I need to change this in
> > > > order to make it work ?
>
> > > > Sub AppendData()
>
> > > > Dim fso As Scripting.FileSystemObject
> > > > Dim folder As Scripting.folder
> > > > Dim file As Scripting.file
> > > > Dim rngEntry As Range
> > > > Set rngEntry = Range("a1")
> > > > Set fso = New FileSystemObject
> > > > Set folder = fso.GetFolder("C:\test\")
> > > > Dim wbkMaster As Workbook
> > > > Dim shtMaster As Worksheet
> > > > Dim rngMaster As Range
> > > > Dim wbkData As Workbook
> > > > Dim shtData As Worksheet
> > > > Dim rngData As Range
>
> > > > ' change path and file name to suit
> > > > Set wbkData = Workbooks.Open(path)
> > > > Set shtMaster = wbkMaster.Worksheets(1)
>
> > > > For Each file In folder.Files
> > > > If LCase(Right(file.Name, 4)) = ".xls" Then
> > > > Dim path As String
> > > > path = file.path
> > > > MsgBox path
> > > > Set wbkData = Workbooks.Open(path)
> > > > Set shtData = wbkData.Worksheets(1)
>
> > > > ' get end of master
> > > > Set rngMaster = shtMaster.Range("A65536").End(xlUp).Offset
> > > > (1)
> > > > 'MsgBox "Address = " & rngMaster.Address
> > > > ' get all data cells
> > > > Set rngData = shtData.Range("B28:F28")
> > > > ' copy data across
> > > > rngData.Copy rngMaster
>
> > > > MsgBox "Appended " & rngData.Rows.Count & " rows of data
> > > > to Master data", vbInformation
>
> > > > ' simply close data
> > > > wbkData.Close False
> > > > ' save and close master
> > > > wbkMaster.Close True
> > > > End If
> > > > Next file
> > > > ' release objects
> > > > Set rngData = Nothing
> > > > Set shtData = Nothing
> > > > Set wbkData = Nothing
> > > > Set rngMaster = Nothing
> > > > Set shtMaster = Nothing
> > > > Set wbkMaster = Nothing
> > > > End Sub- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
-~----------~----~----~----~------~----~------~--~---
