Consolidate data from different files into one master file not working

[Estrella]

Hello

I have a changing number of workbooks under a directory and I would like to extract some data from their first sheet called "offer". The data is always going to be from the same cells inside each workbook B12 B4 B15 B14 and B9

In my master sheet, line 1 will be the headers.
Column A should contain the name of each file extracted, and in column B the information extracted from cell B12, in column C the information extracted from B4 etc etc.

I don't know if it matters but every workbook inside the directory is protected and needs a password at opening ( always the same )

I have tried various codes but none are working. Any help would be a life saver!

Many thanks

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Consolidate_Offers()
    [color=darkblue]Dim[/color] wb [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wsOffer [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Counter [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]'Starting row on the destination sheet[/color]
    NextRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    [color=green]' Prompt user to select a folder[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        [color=darkblue]If[/color] .Show <> -1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        strPath = .SelectedItems(1) & Application.PathSeparator
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    strFile = Dir(strPath & "*.xls*")
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
            [color=darkblue]Set[/color] wb = Workbooks.Open(strPath & strFile)
            [color=darkblue]Set[/color] wsOffer = wb.Sheets("Offer")
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        [color=darkblue]If[/color] [color=darkblue]Not[/color] wsOffer [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]With[/color] ThisWorkbook.ActiveSheet
                .Range("A" & NextRow).Value = wb.Name
                .Range("B" & NextRow).Value = wsOffer.Range("B12").Value
                .Range("C" & NextRow).Value = wsOffer.Range("B4").Value
                .Range("D" & NextRow).Value = wsOffer.Range("B15").Value
                .Range("E" & NextRow).Value = wsOffer.Range("B14").Value
                .Range("F" & NextRow).Value = wsOffer.Range("B9").Value
            [color=darkblue]End[/color] [color=darkblue]With[/color]
            NextRow = NextRow + 1
            Counter = Counter + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]If[/color] [color=darkblue]Not[/color] wb [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] wb.Close SaveChanges:=[color=darkblue]False[/color]
        strFile = Dir()
    [color=darkblue]Loop[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Offers copied: " & Counter, vbInformation, "Consolidate Offers Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Estrella]

Thank you, I get run-time error '438' object doesn't support this property or method
The debugger highlights:
' Prompt user to select a folder
With Application.FileDialog(msoFileDialogFolderPicker)

Do you know how it fix this?
Many thanks

 

[AlphaFrog]

Thank you, I get run-time error '438' object doesn't support this property or method
The debugger highlights:
' Prompt user to select a folder
With Application.FileDialog(msoFileDialogFolderPicker)

Do you know how it fix this?
Many thanks

What version of Excel do you have?

If the folder with all the Offer workbooks is always the same, you could replace the folder picker code with just a fixed file path...

[COLOR=#ff0000]'Replace this...[/COLOR]
    [COLOR=green]' Prompt user to select a folder[/COLOR]
    [COLOR=darkblue]With[/COLOR] Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]If[/COLOR] .Show <> -1 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        strPath = .SelectedItems(1) & Application.PathSeparator
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    
[COLOR=#ff0000]'With something like this (change the path to suit).[/COLOR]
    strPath = "C:\Offers\"

 

[Estrella]

Thank you, I have replaced with the folder path.
I use Excel 2011 for MAC
Now I have a runtime error 68 "device unavailable" pointing at:
strFile = Dir(strPath & "*.xls*")
I'm not sure what this code line is supposed to do?
Many thanks

 

[AlphaFrog]

I use Excel 2011 for MAC

For future reference, start out all your questions with this bit of info. It matters... in this case, a lot.

strFile = Dir(strPath & "*.xls*")
This is suppose to get the name of the Excel file(s) in the folder. It's currently configured for a PC and not a Mac. I don't really know MAC stuff that well. What is the full path and name of one of your files?

If there are any MAC VBAers reading this, feel free to jump in.

 

[Estrella]

Oops I had no idea, I thought VBA was universal across all programs! MAC is such a pain!

Here one of my file path ( the directory is located in dropbox )
/Users/Estrella/Dropbox/London/2. Sales/Sales Administration/1.Proposals/2012/9

and my code line:
strPath = "/Users/Estrella/Dropbox/London/2. Sales/Sales Administration/1.Proposals/2012/9"

Cheers
Estrella

 

[AlphaFrog]

I'm guessing here, so bear with me.

Try this (though it doesn't seem correct)...
strPath = "/Users/Estrella/Dropbox/London/2. Sales/Sales Administration/1.Proposals/2012/9/"

If that doesn't work, then as test, put the macro below in any workbook in your "offers" folder. Open that workbook and run the macro. Tell me exactly the full path and file name from the message box. Important: make sure the workbook with the macro below is located in your "offers" folder.

[COLOR=darkblue]Sub[/COLOR] Path_and_FileName()
    MsgBox ThisWorkbook.FullName
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Ron de Bruin]

Thank you, I have replaced with the folder path.
I use Excel 2011 for MAC
Now I have a runtime error 68 "device unavailable" pointing at:
strFile = Dir(strPath & "*.xls*")
I'm not sure what this code line is supposed to do?
Many thanks

See my Mac page for a example
VBA code in Excel 2011 for the Mac

 

[Estrella]

I'm guessing here, so bear with me.

Try this (though it doesn't seem correct)...
strPath = "/Users/Estrella/Dropbox/London/2. Sales/Sales Administration/1.Proposals/2012/9/"

If that doesn't work, then as test, put the macro below in any workbook in your "offers" folder. Open that workbook and run the macro. Tell me exactly the full path and file name from the message box. Important: make sure the workbook with the macro below is located in your "offers" folder.

[COLOR=darkblue]Sub[/COLOR] Path_and_FileName()
    MsgBox ThisWorkbook.FullName
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hi,
It's : Macintosh HD:Users: then the rest is same as I previously gave you, only ":" instead of /

I will try this code instead... Thank you for your help!