MS Excel appending files with large names

Per the code given; "MergeMultiWorkbooks," it starts down the list and gets to this "second" one;
(PSE) Scheduled Non-Spinning Reserve Sales Starting in WACM, which is basically the same as the first one;
(PSE) Scheduled Non-Spinning Reserve Purchases Ending in WACM, then generates this error:
"Run-time error '1004': Application-defined or object-defined error"
Any thoughts?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you post your workbook please?
Hi Chima,

the reason you get error in because once the VBA function left(  takes only the specific within limit characters then both of the worksheet names become the same and hence duplicate sheet name is not allowed in Excel.

for the complexity of the workbook names you have, i have came up with an Idea to use Abbreviations of workbook, instead of the whole workbook name.  so as an addendum to the macro i added a User defined Function ABBREVIATION that will take name workbook name and make it abbreviated for example for Scheduled Non-Spinning Reserve Sales Starting each first letter of words will be taken and converted to SNPRS and for Scheduled Non-Spinning Reserve Purchases Ending it will convert to SNSRPE , so if you use the below code replace the whole previous code with the below one and then it will work.

Sub MergeMultiWorkbooks()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFileName As String
   Dim iSheet, iSheetCount As Integer

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\TEST" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFileName = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFileName) = 0 Then Exit Sub

    Do Until strFileName = ""

            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFileName)
    iSheetCount = wbSrc.Worksheets.Count
    For iSheet = 1 To iSheetCount
            Set wsSrc = wbSrc.Worksheets(iSheet)
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            wbDst.Worksheets(wbDst.Worksheets.Count).Name = "Book" & ABREVIATION(strFileName) & "Sheet" & iSheet
    Next iSheet

           wbSrc.Close False

        strFileName = Dir()


    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Function ABREVIATION(ByVal s As String) As String
Dim sExclude As String

sExclude = "the|for|a|an|of" ' words to exclude

With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True

    .Pattern = "\b(" & sExclude & ")\b"
    s = .Replace(s, "")

    .Pattern = "\s*([a-z])[a-z]+\s*"
    ABREVIATION = UCase(.Replace(s, "$1"))

End With
End Function

Open in new window

chimaAuthor Commented:
Prof, thank you for your time and extreme effort.  The code got further down the list, yet not to the end.
The same error message verbiage was generated at the second file shown here;
file names
I apologize this is taking much of your time.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

chimaAuthor Commented:
Prof, I should add this; here's the result of the last "Function ABREVIATION(ByVal s As String)";
Let me have a look
i did not find any problem with the code.

to prove it, here is the workaround

create a folder in C drive  name the new folder TEST

create two workbooks and name one of them (Reserve Pool) (Reserve Pool Member) Spinning Reserve.xlsx

and the second one  (Seller) Non-Energy transaction Type) Non-Energy transaction Sales to (Buyers)

and place both of them in the TEST folder.
then download the Main.xlsm file and then save it somewhere then press Control + L the macro will run and create a new file with appended workbooks.  i have done the same and the result of the abbreviated worksheet names from the long workbook names are also attached  file name "Result File generated"

run the same steps as i have mentioned above and then let me know, if you face any error or problem, which is unlikely to happen.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chimaAuthor Commented:
Prof, thank you for your time on this.  I apologize that I did not test per your suggestion, yet I found this;
The code cannot handle this one hyphen as shown in the screenshot below;
mystery hyphneI ran this with three scenarios and it is that hyphen, there are other hyphens above and after, so it is a mystery to me.
chimaAuthor Commented:
I should say; otherwise it works and I am satisfied removing that one hyphen.
You have worked hard enough, I'll issue points shortly.  Unless you want to give it one more effort and I will run the code.  I wish I could do more.
chimaAuthor Commented:
Thank you very much.

please find attached.    this version will take care of the hyphens
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.