Accessing data from excel to use within a Publisher macro

I do not understand what you are wanting to do

Which file do you want to save? - is it the "heavy" workbook? one linked worksheet? A publisher file?
What is in ZZ1 (location?) and ZZ2 (file name?) - are there different values in each sheet?

What exactly are you wanting the macro to do? Please list every step very simply

1.Open Publisher File1
2. ??? what happens next
3. ???
4. Save Publisher file1 ? Save linked worksheet as new workbook?
5. Close Publisher file1 ?
6. Open Publisher File2
7. ??? what happens next
8. ???
9. Save Publisher file2 ? Save linked worksheet as new workbook?
10. Close Publisher file1 ?

How is each publisher file and worksheet "linked"?

Are all the Publisher files in the same folder? Should all files in folder be opened?

thanks

----------------------------------------------------------------------------------------------------------
My interpretation:
I have listed every individual step:
- each step must be in the correct sequence
- each step needs VBA code
- if anything is missing then insert an additional step for me
- if any step not correct, then amend it
- also please answer all the questions

Is this what you want?
- open the Excel workbook - 1 is this always the same workbook?
- update numbers for month
- charts & table are auto-updated
- save amended Excel workbook
- open standard Publisher document - 2 is this always same document
- update links -3 are these linked charts\tables?
- save latest Publisher document - 4 what is the name string? - is it a default file name PLUS month's date?
- create pdf file - 5 is this based on latest Publisher document?
- save pdf with name specified in cell ZZ1 - 6 in which sheet are ZZ1 and ZZ2?
- save pdf to folder specified in cell ZZ2
----------------------------------------------------------------------------------------------------------


Please correct:
Steps
- open the Excel workbook
- update numbers for month
- charts & table are auto-updated
- save amended workbook
- open standard Publisher document
- update links
- save latest Publisher document
- create pdf file
- save pdf with name specified in cell ZZ1
- save pdf to folder specified in cell ZZ2

Please answer:
Questions
1 is this always the same workbook?
2 is this always same (Publisher) document?
3 are these linked charts\tables?
4 what is the (Publisher document) name string? - is it a default file name PLUS month's date?
5 is this (pdf) based on latest (Publisher) document?
6 in which sheet are (cells) ZZ1 and ZZ2?

for each of the 30 publisher files there is a separate sheet in the workbook
Each sheet contains 2 cells with location and file name
Each worksheet is updated each month

Are you using the same 30 publisher files every month updating links then creating 30 new publisher files and then 30 new PDF files?
Do the 30 sheet names EXACTLY match the 30 publisher file names?

Is the Excel file updated several times per month? (Eg piecemeal by worksheet?)
YES

Do you then update relevant Publisher files...
- immediately?
SOMETIMES

- at some random time?
SOMETIMES

- to a given schedule?
NOT REALLY

(It does not matter until after we get the basics working,) but I am wondering whether VBA could helpfully track amendments to the 30 worksheets versus updates to Publisher documents so that (at any given moment) you could see whether any Publisher documents require updating.
(I am guessing that you keep some manual check running at the moment)

deleted by kev

I believe I have cracked it

The problem with updating links reverses if opening Publisher files from Excel
- the links do not update without being forced! (I find that ironic!)
- the benefit of this is that we don't get any messages interrupting the VBA

The method is
- in each worksheet create the name strings for that sheet's masterfile and new pdf and publisher files
- create one macro to do the updating
- add a Form Control button(not Active-X) to each sheet to run the update
- ensure each button has the same name (code required multiple times otherwise - once per button name)

Picture from sample worksheet:

NameStrings.jpg

Both bits of VBA are placed in a general module

Please Login or Register  to view this content.

Button code

Please Login or Register  to view this content.

Attached is the Excel masterfile that I used for testing
- it will not work for you until you amend the various formula but I think it will help you understand everything
- V4, V6 and V7 in each worksheet contain formulas to provide the name strings required
- those values are passed from the Button macro and used in the Update macro
- you need to amend those formulas to provide the strings that works with your file structure
- any file with the same name is overwritten
- do you need a warning to give you the option to back out if the file already exists?

My folder structure was simple
- I put everything in the same folder
- I guess you have 30 different folders (which is not an issue as this is set in each workskeet separately)
- I am using the same name for publisher and pdf files (only extension differs)

This is what I ended up with for NameA and NameB
-ignore the first item (earlier testing detritus):

CreatedPublisherFiles.jpg


If you need further help send me a PM to wake me up!

WARNING
The 30 publisher documents always contains inks to the Excel file
(each is a copy of the master publisher master containing links)
- it would be safer to break the link after the images have been updated
- replying YES to the update links message would result in a previous month file being updated to the latest Excel values which is NOT correct treatment
- I will look into this further and see what is possible

My early thoughts are:
1.There may be a command available to simply break the links, but the obvious one does NOT work in Publisher

Please Login or Register  to view this content.

(... but works in PowerPoint!? )

2. Open the Publisher files in Excel via a menu and VBA
- sounds silly, but that would work (see previous post)
- BUT that would be more tricky (but still possible, I think) if anyone other than you needs to open then

3. Build publisher files (via VBA) "on the hoof" each time, which involves telling VBA
- where to put the images
- pasting each image without linking
- BUT this makes the VBA code more complicated ESPECIALLY if different sheets have different instructions

Edit ...
I have started a new thread specifically targeting breaking links with VBA in publisher
- so far nobody has offered a solution
- I will not attempt anything else for 72 hours to allow time for response on that thread
- in the meantime Do you have any thoughts on this?

Thanks

So far there are no replies on my other thread

Have you tested the code in post#12?
Does it achieve what you want?

You do not need to move your files
(I was lazy - used 1 folder)
- you can set a different folder on each worksheet

If each publisher file is in same folder as its matching PDF, then the code should work
- I assume that you have 30 folders
- set the correct folder on each sheet

If Your files are organised differently tell me and we can probably change the VBA

BTW its OK to overwright the old data as the pdf is really all i need as a copy. so breaking the links is not needed

Do you need to save the 30 publisher files?
Why don't you not simply generate the 30 PDF's from the 30 master publisher files?
(you need to retain anything, you could simply archive a copy of the Excel file each month)