XLSM issue I cannot fix - Error 52...STUCK

An XLSM was created to consolidate files and generate an email it is no longer working. I don't know enough to know how to fix it. There's an Error 52 that is occurring and I cannot figure out how to fix it.

It works when I run it with the July files but not with the August file and I can't seem to tell what's making it choke on SA646_AUG18 as opposed to why it runs with SA646_JUL18
Tracy JohnsonAsked:
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:
Which button do you click when you get the error?
Roy CoxGroup Finance ManagerCommented:
The emails should not be genuine. If they are real then  I would recommend requesting attention to have the example removed and create another example with fake emails.

Can you explain the operation in a little more detail.
Tracy JohnsonAuthor Commented:
I deleted the file and reloaded the file with the tab removed of the emails.

The problem happens when you are in the file and click on the "Generate Monthly Rep Statements" button.

Here is the error:
Run-time error '52':
Bad file name or number

Here's the code from the debug: (screenshot attached)
        SrtRow = J
        K = J
            K = K + 1
        Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Roy CoxGroup Finance ManagerCommented:
There's lots of unnecessary code that I noticed, like this from the above code

Set WBREPS = ActiveWorkbook

Open in new window

this works just as well, but your code isn't causing the error.
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
           ActiveWorkBook.SaveAs Filename:=sRepFile

Open in new window

I would suggest you check what is in the cell referred to here.

WSCURR.Cells(J, "A") & "-" & sMonth

Open in new window

In fact what is J referring to, it's a row number but is the cell empty at that point?
Tracy JohnsonAuthor Commented:
I am not versed enough in this to know. It works sometimes and not others any ideas?

I didn't write this.
Martin LissOlder than dirtCommented:
If you don't know how to do what Roy suggests, or perhaps even if you do, then here is an article I wrote on debugging. Don't let the title put you off. Most of it also applies to VBA.
Tracy JohnsonAuthor Commented:
Roy- I can't speak to the code. It looks at three different files and within each file uses the SUBTOTALS worksheet from the workbook. It is set to break out each change in PriSlp and go from there and from each workbook it views create a file and/or worksheets as necessary for each different salesperson.

What I noticed when it runs is that it fails it is after it finishes processing 775 on the COMMISSIONS (section which is SA646) and it's at the Grand Total line I believe. It works in the file SA646_JUL18 (SUBTOTALS tab) but on the SA646_AUG18 (SUBTOTALS tab) it fails. I don't understand why that would be.

I will look at your article Martin Liss but I don't know that I will be able to figure it out.
Tracy JohnsonAuthor Commented:
It's odd because it appears that it is done creating the files for the COMMISSION and needs to move on to BOOKINGS as all the files are created in the holding folder that it generates.
Roy CoxGroup Finance ManagerCommented:
When I debugged the code there were no errors. The error occurs when it is run.

I've put an error handler in that will hopefully show a message box with the value in the cell that I think is the problem. It is possibly empty or contains invalid characters.

Try using this workbook, see if you get a message beginning:

"This is the value: "
Ejgil HedegaardCommented:
The code fails when it check if the file "PRIME-SALESPER: 010-0818.xlsx" exist.
I guess it is the SA612-AUG18 file.

Colon : is not allowed in filenames, so probably you have a different name in the file for July, without the colon.
Tracy JohnsonAuthor Commented:
Hmmm. I will try these. I came up with a work around in the SA646 file that runs first by adding 800 with nothing in it and then it kept running to the SA612 and the OP512 file.
Roy CoxGroup Finance ManagerCommented:
Any updates Tracey?
Tracy JohnsonAuthor Commented:
Actually how I was able to make this work is that I added that line 800. I haven't had an opportunity to do anything else. My technical scope is quite limited.
Tracy JohnsonAuthor Commented:
I came up with a work around in the SA646 file that runs first by adding 800 with nothing in it and then it kept running to the SA612 and the OP512 file.

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
Tracy JohnsonAuthor Commented:
I cannot believe that I missed the comment about the : in the field name....that is exactly my problem.

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

From novice to tech pro — start learning today.