New error in previously working VB code

[worthm]

Hello,
I'm using MS Office 2010 and I don't know if this is the correct location for this post. Thanks in advance for any help.

Up until around Thanksgiving 2014 this code worked fine. Now it gives an error message when I run it. I am using a macro button to run a piece of code (see attached) that exports all sheets of the tab (except the current one) to a PDF file and it names each file with the same name as the sheet name. I have about 20 sheets and each saves to a PDF file.

The first attachment below shows the code and the highlighted piece is where the error apparently lies. The second attachment shows the error message. I am not a programmer.

I've copied the code from a helpful website and it is exactly as it was given to me. I've heard that in December 2014 Microsoft rolled out an update to all users which protects against a remote user access problem and that update may be causing this issue. I've already had my IT dept follow the suggested steps to delete all of the .exd files. The problem persists. Can anyone help me fix this issue? Im on the verge of rolling this dashboard out to about 100 users and Ive got to be able to export to PDF in one click. Thanks again for any help

[Aussiebear]

Not absolutely sure but if you follow this link it may help your IT department overcome the issue. Seems a recent update released by Microsoft may be responsible.http://excelmatters.com/2014/12/10/o...ivex-controls/

[Blade Hunter]

The code is correct, maybe a dead reference?

Click Debug on that window then in the window that opens up click the Stop button (looks like a square) then click tools / references

All the ones that are ticked (they will all be at the top) do any of them say "Missing"?

[SamT]

MS has been issuing Updates that break various Office components.

They don't effect the existing code, they effect applications like MS Forms and MS Excel that use the code.

IIRC, one was issued Dec14 and another in very early 2015

[worthm]

Thanks for the help Blade Hunter. I've inserted an image of the reference section but I don't see anything missing

[worthm]

Thanks Aussiebear, they've already followed the link, deleted all of the .exd files and the problem persists

[Aflatoon]

Can you run the code by clicking within it in the VBE and then pressing f5?

[worthm]

Aflatoon, this is what I get when I open the VB editor, click on module 3, and hit F5
VBE screen 1.jpgVBE screen.jpg

These look small but can you see them? What does this mean?

[Blade Hunter]

When you are in the VBE press CTRL-G to bring up the immediate window.

Paste this in the window that appears:

activeworkbook.exportasfixedformat type:=xltypepdf, filename:="V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\" & activesheet.name & ".pdf", Quality:=xlqualitystandard, includedocproperties:=true, ignoreprintareas:=false, openafterpublish:=false

then press enter

see if the file is created.

Also, does the file already exist there?

Also what is the name of the sheet it is trying to export? It may have a reserved character in it.

[worthm]

Blade
See if what file is created? Do you mean, does it correctly execute each tab into a PDF? If so, then it doesn't. The immediate window was empty when I hit Ctrl G.

Also, does the file already exist there? Not sure what you mean here

Also what is the name of the sheet it is trying to export? It may have a reserved character in it. It is exporting all sheets except the one called "welcome screen". I've attached a screenshot of all the sheet tab names. Do you want to look at the actual excel sheet? I can upload or email if it helps

[Blade Hunter]

Does this file exist:

V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\activesheet.name.pdf

Where activesheet.name is the name of the sheet you are exporting

See if what file is created?

That line of code I posted and told you to run in the immediate window (CTRL-G) should create a file in the correct folder with the same name as the active sheet.

[worthm]

These are examples of what it should create but it does not:

V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\David.pdf
V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\Amy.pdf
V:\Arizona CSRs\Dashboard\Team PDFs\Phoenix\Danielle.pdf

It should create 27 PDFs, one for each tab except the "welcome screen" tab

The code used to save each workbook tab as a PDF with the name of the sheet tab as the file name

See if what file is created?

That line of code I posted and told you to run in the immediate window (CTRL-G) should create a file in the correct folder with the same name as the active sheet
I keyed that piece of code in the immediate window but its not there now and not in the "welcome screen" tab either. What now? Thanks for your help

[Blade Hunter]

Put it in the immediate window again, hit enter then look for the file.

[Aflatoon]

You need to click somewhere in the code itself and then press f5.

[worthm]

I didn't understand what you meant before but now I get it. When I enter the text in the immediate field and hit enter, it creates a PDF in the designated location. It is one large PDF (1,092kb) with all of the worksheet tabs displayed one after the other. What now?

[SamT]

Humor me and replace those three lines with

Sht.exportasfixedformat type:=xltypepdf, filename:=MyPath & FileName

And if that doesn't change the situation, restore them with

Sht.exportasfixedformat type:=xltypepdf, filename:=MyPath & FileName, _
  Quality:=xlqualitystandard, includedocproperties:=true, _
  ignoreprintareas:=false, openafterpublish:=false

It is really poor form to interrupt an argument.

[worthm]

I am not a programmer.

So I tried both methods. The first didn't work and the second generated a sheet called "welcome screen" with only the contents of that screen and then crashed with the same error as before

error%20message.png

[SamT]

By "Three lines" I meant the three yellow lines

[worthm]

Yes that is what I did and the results are above