Changing the font in multiple files etc.

[sreilly]

Hello,

I'm hoping that you all might be able to help me. I'm trying to help solve an issue at my workplace. We are a publisher and we often receive Excel files from the authors we work with who would like the charts and graphs they've created to be included in their books. Our current workflow involves manually moving information from Excel to Adobe Illustrator where the files are sized and prepared for print. This is a time consuming and error ridden process. One of the big difficulties involves Calibri. As I'm sure many of you know, this font does not always travel well. If you copy the chart in Excel and paste it into Illustrator the type is turned into a series of random and unusable symbols. So here's the first thing I hope might be possible: running a script to change the font in multiple files simultaneously. I usually change the font to Arial. This is complicated by the fact that the current files we're working with produce a dialogue box when they're first opened "The workbook you opened contains automatic links to information in another workbook", so any automatic function would ideally be able to suppress this dialogue box or automatically select "Ignore Links".

I thought I had discovered a way to circumvent some of the work. What I would do was use automator to create versions of all the excel files as PDFs. Then I would use an application I have that will automatically outline the type in a series of PDFs. This would allow me to move the chart into illustrator without losing the appearance of the type. However, when I turn Excel files into PDFs the charts and graphs in the Excel files always get ruined. They get cut in half and placed on two separate pages of the PDF. Is there any way to stop this from happening? The fact that you can only open one PDF page at a time in Illustrator makes this nearly useless.

Anyways, thanks for taking the time to read my post. If you have any advice for solving my issues or where else I might ask for help I'd really appreciate it. For what it's worth, if there is someone that can definitely take on and solve my problem I would be willing to pay for the service.

Regards,
sreilly

[JBeaucaire]

TO make this really simple, can you record a macro of you doing all this to ONE file? The we can take that macro from you and convert it into a looping tool.

1) Open a blank workbook
2) Turn on the macro recorder
3) Now open one of your source files
4) Make all the changes and save it
5) Turn off the recorder
6) post the resulting macro here inside CODE tags.

[sreilly]

Hi JBeaucaire,

Thanks for the response. Please see my code below.

Please Login or Register  to view this content.

[JBeaucaire]

Looks like that macro is only of step #4 above, can you do it from step 1? I'd get a lot more useful info from the First few steps, too. Thanks.

[sreilly]

Hi,

As far as I can tell that's pretty much all the code that's being generated. Perhaps I'm not looking in the right place. I've followed the same steps again but have opened the document via the File->Open option in Excel rather than just clicking on the item in Finder. Is this information helpful?

Please Login or Register  to view this content.

[JBeaucaire]

Yes, very helpful, notice all the additional helpful code at the start? So something like this, I'd think:

Please Login or Register  to view this content.

[sreilly]

Thanks!

Unfortunately I'm a little unsure how to run this. I have it saved as a Macro in a Workbook. How do I direct it to the files I need to change? If I just open one file and try to run it, it seems like the script runs because a dialogue box opens which says "Done, a total of files were updated" but the font in the document remains unchanged.

[JBeaucaire]

Reading through the comments in the macro you should spot the fPATH at the top of the macro. That is the folder the macro is trying to find files inside to update.

1) Decide on a single folder where you want this to happen for all
2) Place all the .xlsx files in that folder
3) Update the macro to type in that folder address, that will be the "processing" folder.

You said you'd already installed the macro into a blank workbook, that's great. No need to open anything, just run the macro and it should process all the files found in that folder that end with .xlsx

[sreilly]

I've double-checked and your instructions are how I have things set up but I'm still getting the same results as before.

[JBeaucaire]

So the macro is unable to find any files in that folder, that implies.

[sreilly]

I haven't moved the files since I posted the code upthread. Those files are on a server which I thought perhaps could be causing an issue. I copied both the files and the worksheet with the macro to a new folder on my desktop just called "Copy" and changed the file path in the macro to reflect the change. Same issue.

[JBeaucaire]

May I request you post the edited macro so I can review the changes, check that off the list of causes?

[sreilly]

Please Login or Register  to view this content.

Image showing code with file path. http://imgur.com/a/oTLKQ

[JBeaucaire]

Doh! Mybad.

Fix this line:

Please Login or Register  to view this content.

[sreilly]

I've made the edit, still the macro seems to run but doesn't do anything.

Please Login or Register  to view this content.

[JBeaucaire]

I added a line of code in red above, add that in and run it, see what the first file found says.

[sreilly]

"the first filename found was ___________"

[JBeaucaire]

There you go, no filenames are being found in the given folder.

[sreilly]

Right, any chance you can help me figure out why that is? You can see the location of the folder at the bottom of this screenshot.

http://imgur.com/sHdWSO1

[xlnitwit]

Hi,

Since you are testing the extension anyway, I would recommend that you omit the MacID part from the Dir call and simply use

Please Login or Register  to view this content.

[sreilly]

Hi xlnitwit,

Thanks for the suggestion. Making this change had the following impact: when I ran the macro the dialogue box said that the first filename found was _DS._Store__. When I clicked 'ok' the first file in the folder opened but then I got a runtime error- "Run-time error '-2147483645 (800000003)' The specified value is out of range."

[xlnitwit]

Which line of code is highlighted by the debugger?

[sreilly]

With ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font<

[xlnitwit]

Does the active sheet contain a chart object called Chart 1?

[sreilly]

The workbook with the macro saved in it does not. The first file in the folder I'm trying to run the macro in does. I just checked this by running the vba recorder and seeing what it recorded when I selected the chart. The line produced was " ActiveSheet.ChartObjects("Chart 1").Activate".

[xlnitwit]

When the error occurs, there should be a workbook open and active. Does its active sheet have a chart by that name?

[sreilly]

When I run the macro with the first file in the folder open I first get the same dialogue box about ds.store. Then I'm told that file 1.1 is already open so the macro is trying to open the files from the folder. If I select that, yes, I do want to reopen that file then I get the same run time error.

Edit: and yes, the chart in that file is "Chart 1".

[sreilly]

*bump*

Can anyone help me out?

[sreilly]

Hi,

Is anyone out there able to offer some advice?

Thanks