Link to home
Start Free TrialLog in
Avatar of Tracy Johnson
Tracy JohnsonFlag for United States of America

asked on

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Which button do you click when you get the error?
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.
Avatar of Tracy Johnson


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
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?
I am not versed enough in this to know. It works sometimes and not others any ideas?

I didn't write this.
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.
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.
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.
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: "
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Any updates Tracey?
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.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I cannot believe that I missed the comment about the : in the field name....that is exactly my problem.