Batch image change in Word/Excel Docs. 3

[gregmosu]

Does anyone have any information on how to change an image in numerous Word and Excel documents w/a batch process instead of individually changing the image in every document?

Thanks,
Greg

 

[SkipVought]

Hi,

Turn on the macro recorder.

Make the changes

Turn off the macro recorder

Observe/change the code as required -- post back as required.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[gregmosu]

Hi Skip,

Thanks for the reply. I've used the macro recorder to learn how to do various things w/Excel, but how would I use a macro to change this image in numerous word/excel docs w/out having to open each one and run the macro? Can I access the macro and run it from a seperate script?

-Greg

 

[SkipVought]

How would you go about doing it manually?

Please explain the process.

Is there a SINGLE image embedded in multilple places?

Are there multiple images?

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[gregmosu]

Skip,

There are about 30 documents(word and excel). Each document has the same image at the top... so basically there are 30 documents that all have the same image at the top. I want to be able to replace that image w/a new one w/out having to open each document and do it manually.

-Greg

 

[SkipVought]

Since you havn't posted this in the VBA Forum Forum707, I'll give you a qicky solution

1. macro record in both excel & word, changing the image as you desire.

2. In the Windows Explorer, open ALL the Excel workbooks.

3. run this macro with your code copied in

application.displayalerts=false
for each wb in workbooks
  for each ws in wb.worksheets
     ws.shapes(1).delete
     'put your code here substituting ws for activesheet

  next
  wb.save
  wb.close
next
application.displayalerts=true

4. open all the word documents

5. run this code

application.displayalerts=false
for each dc in documents
   dc.shapes(1).delete
     'put your code here substituting dc for activedocument

  dc.save
  dc.close
next
application.displayalerts=true

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[gregmosu]

Well, this really didnt start off as a question about vba or macros, but it looks like it'll do what I want. I was actually hoping to find an xml generator like Oxygen that would recreate all the documents from xml and xslt templates, but that must not be a popular solution...?

THanks,
Greg

 

[fumei]

Hi gregmosu.

It started off with a question on how to batch an image change in Word and Excel. You can change an image manually, right? Batching a process usually involves some sort of scripting, a batch file is really a macro. Or a macro is a batch file...whatever.

If you want to ask about XML generation, then you should ask about that. How were we to know that you wanted an XLM solution, when you never mentioned it?

To further along Skip's suggestions. You do not really need to have all the documents open at the same time. If you have lots, this may be a performance issue. You can open them consequtively.

You do not state if this image is in the header/footer. If it is, there may be other issues.

Gerry

 

[gregmosu]

To start with, I'm a web developer who got thrown into this project. I dont even use office products.. I just happen to be on the bench right now, so they asked me to look into this. I dont know that XML is the answer, and I dont know that macros are either. The image I'm refering to is the customer's logo that sits at the top of each page. They want to be able to run some script that will swap out the current logo w/any other logo they choose... w/out having to open up each document and running a macro.

So far, I've tried using a macro that runs when the file is opened, but thats no good because I cant have it searching for an image everytime the doc. is opened.. Is there a way to call a macro from a seperate script w/out having the document open? The only other experience I have w/macros was back in college when I used one in excel to create a graph... so I really know next to nothing about them.

Sorry if you guys are irritated, but I really didnt know where else to post this. I posted a similar question in the xml forum, and got no reply. I would have posted this in the vba forum, but didnt really think a macro was the answer.

-Greg

 

[earthandfire]

If you repost to Forum 329, the VBScript forum, cross referencing to this thread, someone will be able to help you create a VB Script file that will run Skip's code against, for example, each .DOC and .XLS file in a specified folder.

A VB Script file can be executed by double-clicking it either in Explorer or the on the Desktop.

Hope this helps.

 

[fumei]

I don't believe a VBScript (nor VB, nor VBA for that matter) can action an image delete/image insert on a Word file without opening it.

If anyone knows of a way to do that PLEASE post!

The Word file has to be opened. OK.

Sub ChangePic()
Dim oFile
oFile = Dir("c:\temp\*.doc")
Do While oFile <> ""
  Application.Documents.Open FileName:="c:\temp\" & oFile
    With Selection
      .GoTo what:=wdGoToGraphic, Count:=1
      .Delete
      .InlineShapes.AddPicture FileName:="C:\temp\2.jpg"
    End With
  If ActiveDocument.Saved = False Then ActiveDocument.Save
  ActiveDocument.Close
  oFile = Dir
Loop
End Sub

will run through every Word doc in the folder, deleting the FIRST graphic, then inserting a specific image, saving the file, closing it, then repeating on the next file.

1. Again, if the image is in the header there may be other issues. You state it is at the top of the page. That sounds like in the header. Please confirm.

2. If there are other images to contend with, that is also an issue. If all the document have the same index number (be it first or twent-first) then you can deal with easly. If the image can be a variety, then you have a bit of a problem.

So far, I've tried using a macro that runs when the file is opened, but thats no good because I cant have it searching for an image everytime the doc. is opened..

Could you explain this please? Why would you want it to do this every time? Are to trying to actually change the image, or not?

Does anyone have any information on how to change an image in numerous Word and Excel documents w/a batch process instead of individually changing the image in every document?

This does not makes sense actually.

If you change the image in numerous files...does not each individual file get changed? Yes, it does. The batch process simply goes on to the next one without asking. But each individual file is, well, changed.

Again, if anyone knows how to perform action on the contents inside a Word file, without opening the file, I would love to hear about it. Contents - not document properties.

Gerry

 

[earthandfire]

fumei, I didn't suggest that the Word / Excel documents would not be opened (and closed), but by using automation ie CreateObject/GetObject and the FSO in a VBS file, Skip's code could automatically process the documents without any user intervention.

I think my suggestion therefore is still valid.

Hope this helps.

 

[gregmosu]

Hi guys,

I think the image is in the header.. at least its in the header box. Also, there is only one of these images at the top of each document that may need changed... and it will always be on the first page of each doc. I'm just trying to find a way for someone to swap out this image for a new image w/out having to open each document and do a delete and insert on the images.

Fumei, I'll give your code a try.

THanks,
Greg

 

[fumei]

Oh, sorry earthandfire. I was not suggesting that your post was invalid. My comment was in reference to gregmous' post, as posted. Which was a request for something that will process without opening the file. Plus his post mentioned not wanting to post to the VBA forum. In my mind, your post implied that posting to VBScript may solve either issue. So I made the reminder that one has to open the file (issue 1). And, I guess, that fact is applicable no matter what you use. Further, I would like to point out that FileSystemObject works fine, and yes that would help to run Skip's code automatically, with no user intervention. However, I am not quite getting the relevance of that because using Dir in my code also runs automatically, with no user intervention. Plus there is no need to add the Reference required to use FSO.

That being said, of course, if his requirements demand the greater useability of FSO then that is the way to go.

Thanks for your comment.

Gerry

 

[earthandfire]

fumei, sorry I was having a bad day then and in fact a bad week. I've been lumbered with some work on an old Access 97 database which has been a pain all week.

The only differences between your suggestion, which I agree works fine, and mine are:

A vbs script could simply be double clicked and would process all the files, whereas your suggestion requires opening a Word document (or Excel spreadsheet or both) to run the macro - which was what I understood gregmous did not want to do.

Again, sorry if I seemed a bit short.

 

[SkipVought]

I did not introduce FSO because this was posted in the Office forum and seemd like a one-time conversion.


If I were doing it, depending on the scope and frequency, I'd probably go the FSO route.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[gregmosu]

Thanks guys for all your help, input & patience. I'm sure my total ignorance of office products hasnt made this easy for any of you.

I'm still having a few issues w/your code fumei...

Heres what I have so far, and the compiler is complaining about 'wdGoToGraphic' not being declared.

        Dim oFile
        oFile = Dir("c:\temp\*.doc")
        Do While oFile <> ""
            Dim WordApp As New Microsoft.Office.Interop.Word.ApplicationClass
            WordApp.Documents.Open(FileName:="c:\temp\" & oFile)
            With WordApp.Selection
                .GoTo(what:=wdGoToGraphic, Count:=1)
                .Delete()
                .InlineShapes.AddPicture(FileName:="C:\temp\2.jpg")
            End With
            If WordApp.ActiveDocument.Saved = False Then WordApp.ActiveDocument.Save()
            WordApp.ActiveDocument.Close()
            oFile = Dir()
        Loop

Once I get this to compile, I just need to run this executable to change the documents... theres nothing else I need to do to the word docs, correct?

Thanks,
Greg

 

[fumei]

Hi Skip - well gaaaa

http://wwwwwlee,

you Texans are pretty darn observant. depending on scope and frequency, yup, yup, yup. And lordy, I hope you know I am joshin'.

earthandfire - hey dinna fess yourself. I ken "bad day", and have made MUCH worse posts than yours, writing stuff that was clearly out of line...and dumb to boot. Yours was not dumb at all, so pah, it = Nothing.

gregmous:

1. Dim WordApp As New Microsoft.Office.Interop.Word.ApplicationClass

Oh oh. This was the first of this coming out. Yo people! Does this have full reference into Word? I mean all of it?

2. This Dim and making of an instance is repeated through every loop, as in:

Do While [i]blah blah[/i]
Dim WordApp As New Microsoft.Office.Interop.Word.ApplicationClass
'   other stuff...blah blah
Loop

The object is not destroyed. I don't know if it is a good idea to have the this within the loop.

3. "Executable"? What executable? This is a procedure that CAN execute. It is not an Executable. Or have you now moved this to VB without telling us? Please clarify.

Gerry

 

[gregmosu]

Sorry.. yeah, I'm trying to create a little console app. w/VB.net in Vis. Studio. I was assuming it would create an executable that I could run from the command prompt. I found a little tutorial from google, and kinda incorporated what you gave me into it.

I take it I'm heading down the wrong path???

-Greg

 

[SkipVought]

Gerry,

Don't tell anybody, but I'm one of those damn Yankee transplants. I spent my first 38 years wandering in the "wilderness" of the northeast, and moved from Arlington Mass to Arlington, Texas. I got here as fast as I could! ;-)

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}