Macro - Consolidate Column B from multiple workbooks

[souza107]

I am getting an error message. I have attached both pictures of the message itself and then when I click on debug. Let me know what you think.

[Sisyphus]

Hi,
Off the cuff, I would say that you may not have set the FilePath or FileType constants (at the top of the module) correctly. Check the spelling carefully. Make sure that the path you have set is available and that you have the Master sheet in view (preferrably no other Excel workbooks loaded) before you call the macro.

If that doesn't bear fruit enter the following line in the code just below the line that was highlighted in the screenshot you posted:

Code: Select all

Debug.Print FilePath & FilePrefix & "*." & FileType

Then run the code. When the error occurrs press Debug.
The above code will have printed something in the Immediate Window of the VBE window. If the Immediate Window isn't visible, Click View/Immediate Window in the VBE window.
What you see printed in the Immedaite Window is what causes the problem. If it helps you, good. If it doesn't paste it into a post so that I can have a look at it.

You may like to post a copy of the "Control Panel" part of your code for good measure.

Sorry for your trouble. I tested the code again this morning and it runs without problem on my machine.

[souza107]

I copy and pasted the file path to make sure it was correct but let me know I have copy and pasted below. Also when I c/p the code nothing game up with the active window? I did make sure that I was in the active window before I ran the code.

Does it matter if the "Sheet Names in my actual report are different from the sample? I looked through the code and did not see a place where sheet names were entered. The sheet names are consistent with all my reports just different from the template

Option Explicit

Const FilePath As String = "/Users/Souza/Desktop/OOS"
Const FileType As String = "xlsx"
Const DataCol As String = "B"
Const FirstDataRow As Long = 2

[Sisyphus]

Hi,
No, your path is not the straight and narrow one!
Firstly, observe the difference between slash (/) and backslash (\). On most PCs the backslash is the path separator. Secondly, a path starts with a drive letter. If you run Win7 your path should start like with "C:\Users", most likely.
Thirdly, There should be a folder by the name of OOS on your desktop. This can't be a file name, must be a folder.
Fourthly, the path must end with a backslash. Many programs anticipate that the user might forget this and add the backslash in the program. I did anticipate but didn't add - see what the laziness got me!
Totally, try this one:

Code: Select all

"C:\Users\Souza\Desktop\OOS\"


[souza107]

I am on a Mac running excel 2011.

So instead of "C\" it is" Macintosh HD:\" I have also created the folder on desktop called "OOS"

I tried multiple ways for the file path. None of which worked.

Const FilePath As String = "Macintosh HD:\Users\Souza\Desktop\OOS\"
Const FilePath As String = "Macintosh HD\Users\Souza\Desktop\OOS\"
Const FilePath As String = "Macintosh HD:/Users/Souza/Desktop/OOS/"
Const FilePath As String = "Macintosh HD/Users/Souza/Desktop/OOS/"

I have another report I do that I made a pivot table for and exported it to another workbook. This auto populated and file path for the original file. It looks like the following.

'Macintosh HD:Users:Souza:Desktop:[12 Unit Tracker All.xlsx]Units'!$A$4:$CS$1149

I then Tried
Const FilePath As String = "Macintosh HD:Users:Souza:Desktop:OOS:"

This did not work either. Stumped here. Any ideas?

[Sisyphus]

Hi,
My research indicates that the path separator on the Mac is a colon, and that is what you seem to have in the address that you quoted as working. Try this, then:

Code: Select all

"Macintosh HD:Users:Souza:Desktop:OOS:"

Unfortunately, that won't end your troubles which, by the way, result from your failure to say that you are working on a Mac. The Mac doesn't support the wild card used in my code to identify file names. You will need to replace the procedure GetFileNames with this one:

Code: Select all

Private Function GetFileNames(ByRef FileNames() As String) As Boolean

    Dim FilePrefix As String
    Dim NextFile As String
    Dim i As Integer
   
    FilePrefix = GetFilePrefix
    If FilePrefix = vbNullString Then Exit Function
   
    NextFile = Dir(FilePath, MacID(FileType))
    Do While NextFile <> vbNullString
        If Instr(1, NextFile, FilePrefix, vbTextCompare) = 1 Then
            ReDim Preserve FileNames(i)
            FileNames(i) = FilePath & NextFile
            NextFile = Dir("")
            i = i + 1
        End If
    Loop
    GetFileNames = Not (Not FileNames)
End Function


This code, written for a Mac, can't run on my PC. Hence it isn't tested. If it gives you trouble, tell me what kind of trouble. If you feel like solving the problem yourself, here it is:-
I don't know the MacID for Excel xmls files. My code is trying to use FileType (entered at the top) but I doubt that it is the correct one. You can read up on the subject at this link:

http://office.microsoft.com/en-us/access-help/dir-function-HA001228824.aspx

but the only way to really solve the problem is to find the MacID. It should be in your Mac somewhere or in whatever help they provide on or offline.
Once you find the correct MacID for Excel files set it at the top of the module and the code should then run.

[Sisyphus]

Hello Souza,
I had another try at finding some information on the MacID. I failed, but I got some fragments.

1. A strong suspect is "XLS8". So, you may try this if your "XLSM" doesn't work.
2. I also found a post saying that the path separator (colon) at the end of FilePath may not be required. On a PC the separator is necessary because a file anme is appended. In fact, in the code I wrote for your Mac this doesn't happen. So, it is better than even money that you should take it off.

Try these things. If they don't work you need help from some one who has a Mac and I suggest that you start a new thread, something like "Using the Dir() Function on a Mac" to return a list of files in a directory. Or simply "What is the MacID for XLSM files?"

[Sisyphus]

Hello Souza,
You may wish to exchange the procedure "StoreName" in your code against these two:

Code: Select all

Private Function StoreName(ByVal Wbname As String) As String

    Dim Sp() As String
    Dim S As String
    Dim i As Integer
   
    ' expected format is similar to "YYMMDD STOCK - Store Name.xlsx"
        ' The word "STOCK" must be present
       
    Sp = Split(Wbname, Application.PathSeparator)
    S = Sp(UBound(Sp))
    i = InStr(1, S, "STOCK", vbTextCompare)
    If i Then
        S = Trim(Mid(S, i + Len("STOCK") + 1))
        If Left(S, 1) = "-" Then S = Trim(Mid(S, 2))
    End If
    StoreName = TruncExtn(S)
End Function

Private Function TruncExtn(ByVal Fn As String) As String

    Dim i As Integer
   
    Do
        i = i + 1
        If Asc(Right(Fn, i)) = Asc(".") Then Exit Do
    Loop While i < Len(Fn)
    If i < Len(Fn) Then Fn = Left(Fn, Len(Fn) - i)
    TruncExtn = Fn
End Function


To do so, find the line

Private Function StoreName(ByVal Wbname As String) As String

then select everything until the next

End Function

and delete it, both lines inclusive.
Then paste the above two procedures at the bottom of your code sheet.

The original code has a logical error in it which was eliminated in the above improvement. The effect of the difference is that with the new code you would be allowed to have periods in your file names while the old one would produce unexpected results if you do.