|Let's clear something up first. I didn't mention this earlier, but each time you post your code, you include these lines:|
On Error Resume Next
When I paste your code into the VBA editor, both of the lines below Option Explicit cause a Compile Error: "Invalid Outside Procedure". Do they not cause that error for you? They should, since they are not commented out and VBA (at least my VBA) has no clue what to do with them.
Next, you didn't mention which line is causing the "Expected Statement" error. I don't know your level of VBA expertise (although it seems high) so please don't take this the wrong way. Are you familiar with the debugging feature of Single Stepping? This tutorial will explain some debugging techniques that you might find useful - assuming you don't already know all about them:
Perhaps using F8 to single step through your code would help you determine which line is causing the error and point you in the right direction.
As I mentioned earlier, since we don't have all of the parts and pieces of your environment, it's impossible for us to replicate your errors. Therefore, the best we can do toss out suggestions and see if they help. In other words, if you look at this thread, you'll see that we can easily set up a test workbook and try some code before offering a solution:
In your more complicated case, we would need all of your code, your workbooks, etc. in order to really troubleshoot your issue.
OK, that said, I am not all that familiar with using the CreateObject("Excel.Application") feature, so I may be way off base here...
In your earlier code, you reference the ActiveWorkbook:
In your latest version you don't:
xlApp.SaveAs Filename:= _
Again, since I am not very familiar with the use of CreateObject, I wonder if your attempt to use SaveAs against the xlApp object and not the xlApp.ActiveWorkbook is part of your issue. Have you tried that?
One last item, which is merely a posting tip:
You will notice that I edited the code in Response #4 to "shorten" some code lines so that they fit within the text window of this forum. That allows us to read the text above the code without having to scroll the window back and forth. The scrolling issue is not your fault, it's just an inconvenient "feature" of this forum. If you could preview your posts and make sure your code lines are short enough so that they don't force us to scroll the window back and forth, that would be really helpful. Thanks!
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.