0

I'm stuck in a problem in VBA while consolidating data from other workbook to master work book and using file name and path name is variable which is changing dynamically in loop i searched but i only find hard coded path so i'm posting here following is my code.

Sub Append()

    'Append data from other files
    Path = "E:\NPM PahseIII\"
    Dim c As Range

    'find the first empty cell in ColA
    Set c = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
    Filename = Dir(Path & "*.xlsx")

    Do While Filename <> ""
        If InStr(Filename, ".") > 0 Then
            Filenamenoext = Left(Filename, InStr(Filename, ".") - 1)
        End If

        c.Value = Filenamenoext
        Set c = c.Offset(1, 0)
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        Windows("Master sheet").Activate
        Selection.Consolidate Sources:=Array("'Path & [Filename]Sheet1'!$B$2:$B$5"), 
        Function:=xlSummary

        Workbooks(Filename).Close
        Filename = Dir()
    Loop

End Sub

The first problem is that this program gives me the error

Object doesn't support this property or method

at this line 

Selection.Consolidate Sources:=Array("'Path & [Filename]Sheet1'!$B$2:$B$5"), 
Function:=xlSummary

secondly i want that when first time data is appended by running the code again if there is no change in the other files data then code should not append duplicate again.

braX
  • 11,506
  • 5
  • 20
  • 33
Hidayat Ullah
  • 147
  • 9
  • Sure the section `Array("'Path & [Filename]Sheet1'!$B$2:$B$5")` is wrong. The "Path" and "Filename" should be referring to the variables, not literally the words "Path" and "Filename" - try `Array("'" & Path & "[" & Filename & "]Sheet1'!$B$2:$B$5")` – jamheadart Dec 14 '18 at 07:47
  • @jamheadart by changing to your code it's give me error 'Consolidate method of range class failed '? – Hidayat Ullah Dec 14 '18 at 07:57

1 Answers1

0

Note that in that string "'Path & [Filename]Sheet1'!$B$2:$B$5" the Path and Filename is not considered as a variable but as a hard coded string!

You must use something like

"'" & Path & "[" & Filename & "]Sheet1'!$B$2:$B$5"

if you want to use the variable values.

Also according to the documentation of the Range.Consolidate method the constant of the xlConsolidationFunction is not Function:=xlSummary but Function:=xlSum.

Also note that Selection is very undefined and can be anywhere in that worksheet. I recommend to read How to avoid using Select in Excel VBA and reference your ranges, workbooks etc without using .Activate and .Select or Selection. at all.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks Error is gone but data from other files is not consolidating.? – Hidayat Ullah Dec 14 '18 at 08:02
  • Are you sure that the consolidate function is correct for your approach? Did you read the documentation about what it actually does? I think instead of that you must copy the data into your master sheet and then use remove duplicates in the master sheet. – Pᴇʜ Dec 14 '18 at 08:05
  • how do i use copy and remove duplicate?also in my current code i need to specify range in the master document for consolidation? – Hidayat Ullah Dec 14 '18 at 08:07
  • @HidayatUllah Well that is a way to broad to answer in a comment. You must read [Range.Copy method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.copy) and [Range.RemoveDuplicates method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.removeduplicates) and try it on your own. If you get stuck include your code in a question and give a [mcve] with good example data. – Pᴇʜ Dec 14 '18 at 08:14