We help IT Professionals succeed at work.

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

86 Views
Last Modified: 2018-10-17
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
SA646_AUG18.xlsx
SA612_AUG18.XLSX
OP512_AUG18.XLSX
SA646_JUL18.xlsx
MonthlyRepCommissionStatementGenera.xlsm
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Which button do you click when you get the error?
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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.

Author

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
        Do
            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
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
error52debug.docx
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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.
WSCURR.Copy            
            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?

Author

Commented:
I am not versed enough in this to know. It works sometimes and not others any ideas?

I didn't write this.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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.

Author

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 Manager
CERTIFIED EXPERT

Commented:
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: "
MonthlyRepCommissionStatementGenera.xlsm
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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 Manager
CERTIFIED EXPERT

Commented:
Any updates Tracey?

Author

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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I cannot believe that I missed the comment about the : in the field name....that is exactly my problem.

Thanks!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.