Solved

Editing a Word Macro

Posted on 2013-01-02
37
643 Views
Last Modified: 2013-02-07
I've found a Word macro to batch-convert Microsoft Works (.wps) files to Word 97 (.doc).  However, I need a way to convert .wps to Word 2007 (.docx).   Because there are many files (more than a thousand) and they are distributed among dozens, or hundreds, of folders, it isn't practical for me to consolidate them into a single folder, process them, and then redistribute them to their correct folders.  If you can edit the following macro, such that it converts the files to .docx in Word 2007 format, that would resolve my dilemma.

Sub Batch_Save_WPS_as_DOC97()
Dim bConv As Boolean
Dim strFileName As String
Dim strDocName As String
Dim strPath As String
Dim oDoc As Document
Dim Response As Long
Dim fDialog As FileDialog

bConv = Options.ConfirmConversions
Options.ConfirmConversions = False
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , "Save all as DOC"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
End With

If Documents.Count > 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If

strFileName = Dir$(strPath & "*.wps")

While Len(strFileName) <> 0
Set oDoc = Documents.Open(strPath & strFileName)

strDocName = ActiveDocument.FullName
intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 1)
strDocName = strDocName & ".doc"
oDoc.SaveAs FileName:=strDocName, _
FileFormat:=wdFormatDocument97
oDoc.Close SaveChanges:=wdDoNotSaveChanges
strFileName = Dir$()
Wend
Options.ConfirmConversions = bConv
End Sub
0
Comment
Question by:ddantes
  • 16
  • 14
  • 7
37 Comments
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38738782
Only some very minor changes here, slight update:
Sub Batch_Save_WPS_as_DOCX()
Dim bConv As Boolean
Dim strFileName As String
Dim strDocName As String
Dim strPath As String
Dim oDoc As Document
Dim Response As Long
Dim fDialog As FileDialog

bConv = Options.ConfirmConversions
Options.ConfirmConversions = False
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , "Save all as DOCX"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
End With

If Documents.Count > 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If

strFileName = Dir$(strPath & "*.wps")

While Len(strFileName) <> 0
Set oDoc = Documents.Open(strPath & strFileName)

strDocName = ActiveDocument.FullName
intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 1)
strDocName = strDocName & ".docx"
oDoc.SaveAs FileName:=strDocName, FileFormat:=wdFormatDocumentDefault
oDoc.Close SaveChanges:=wdDoNotSaveChanges
strFileName = Dir$()
Wend
Options.ConfirmConversions = bConv
End Sub

Open in new window

0
 

Author Comment

by:ddantes
ID: 38738806
That works perfectly!  I'm awarding points, but if you don't mind, is there a way to adjust the macro such that it processes the contents of subfolders as well as the main folder which is selected?  If appropriate, I can post this as a separate question.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38738814
No problem you actually did ask for that. I was thinking we could use FileSystemObject to hunt down folders containing WPS files, then pass the folder name to your macro above for processing. Are all the files in subfolders on one network share?
0
 

Author Comment

by:ddantes
ID: 38738819
Thank you.  All the files are in subfolders within one local hard drive partition, and within one folder on that partition.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38738942
Here you go give this a try. First test on a folder at a very low level and see if it works as expected. All the code is in the userform, and there is a reference to Microsoft Scripting Runtime for the FileSystemObject component.
Let me know how it goes
...Terry
ConvertWPS.doc
0
 

Author Comment

by:ddantes
ID: 38738979
Wow, that's some awesome work!   It processed files in a folder and subfolder, and they opened fine in Word 2007.  This will make my job easy.   One last question:  How can I see the code in the template you created?
0
 
LVL 16

Expert Comment

by:terencino
ID: 38739030
Sure, to see the code just Alt+F11 to open VBE, then under Project (ConvertWPS) click on the plus sign near the forms, double-click on UserForm1 and hit F7 to show the code. You can see the code that was Batch_Save_WPS_as_DOCX is reduced quite a bit
0
 

Author Comment

by:ddantes
ID: 38739089
Great.  Thanks again & Happy New Year!
0
 

Author Comment

by:ddantes
ID: 38766023
Error opening embedded URL shortcutDear terencino:  I just discovered that URL shorcuts which were embedded in the .wps files do not open, when clicked in the .docx files.   Is there a way to make these active again?  There is an error message (see screenshot).  Thanks.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38766087
Very strange, can you send me the file with just the bad link in it and I will try and troubleshoot that
0
 

Author Comment

by:ddantes
ID: 38766091
Test.docx

Thank you for making yourself available to check this.  To my relief, this isn't happening on all my office machines.  There is a Windows 7 machine where the shortcuts work.  But on a Windows XP SP-3 machine, they don't work.  Microsoft Office Compatibility Pack is installed.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38766366
They work on Windows 8 as well. It looks like the URL has been converted to a package shell object. Can you also send me the WPS with the same links?
WPS-DOCX Links
0
 

Author Comment

by:ddantes
ID: 38767981
wps is not among the acceptable file extensions to upload.  Please download the wps file from www.mauitradewinds.com/Experts
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38768480
Hi David, I hope you don't mind but I checked back through your previous questions and found this one.  I'm just posting a link to your new question so that terencino will be alerted to it and may be able to devise a method of converting packaged objects into standard images hyperlinked to external sites.  That kind of batch processing with VBA (macros) or VBScript files is beyond my level of expertise.

New Question:
http://www.experts-exchange.com/Q_27992053.html

I wish I had read back through your questions before posting there.  It would have saved me some time ;-)
0
 

Author Comment

by:ddantes
ID: 38768524
Thank you.  After posting this question, it occurred to me to ask terencino for advice as well. That question had been closed, with points awarded, so I didn't know if he would be available. Fortunately, he is willing to participate.  So, I'll award points on the question which you had been addresssing and wait for terencino's response.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38769419
Hi David and thanks to Bill for the comprehensive analysis in the interim!
This might be a multi-step process to preserve the hyperlinks.
Do you still have Works 2000? If so apparently there was a Batch Conversion Wizard to Word 2000, check the details at Works Suite: How to Convert Works Word Processor Documents to Word 2000
0
 

Author Comment

by:ddantes
ID: 38769474
Hi terencino:  I still have Works 2000.  But I already used your wonderful batch conversion tool to convert my wps files to Word 2007 docx files.  Are you suggesting that I convert them again, this time to Word 2000?   The embedded URL shortcuts are working under Windows 7, and that satisfies most of my requirements.  Ideally, they would work also under Windows XP, but I'm hesitant to convert the Works 2000 files to Word 2000 instead of Word 2007.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38769480
Sorry I sent that too early. I meant just to test the conversion of just one test Works file with the links, to Word 2000. Then can you check the hyperlink in Word 2000 file in XP. Can you also send it to me?

Also just FYI, if EE won't let you upload a file format, you can use WinZip or similar to put it in a zip file and upload that.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:ddantes
ID: 38769488
Understood, and thanks for clarifying.  I'll try converting Test.wps to Test.doc (Word 2000) and see how the links perform.   I did try WinZip, but there was a message that the zip archive contained a file type which is not permitted.  I'll post after the conversion test.
0
 

Author Comment

by:ddantes
ID: 38769501
Word could not convert test.wps  No luck with the batch conversion.  Sorry.
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38769538
Hi David

Unfortunately a disallowed file type inside a ZIP file is still detected here.

To attach a file that is not one of the accepted file types, all you have to do is change the file extension to one that is accepted.  Change  FileName.doc or FileName.wps to FileName.txt.  Don't open in NotePad and resave as an actual text file or anything, just Right-Click the file > "Rename" > and change the 3 characters after the dot in the file name.  I usually retain the original file extension in the name, but change the original dot to a dash, example "FileName1.wps" renamed as "FileName1-wps.txt".

It could be that you aren't actually seeing the file extensions when you view the files in Windows Explorer and are simply identifying them by their icons.  To show the file extensions do this:

Windows XP:
In Windows Explorer > Tools menu > Folder Options > View tab > Untick "Hide Extensions for known file types" > Click Apply button > close dialog.
OR
Start Menu > Settings > Control Panel > Folder Options > then the same as above.

Windows Vista and 7:
In Windows Explorer > click Organize drop down > scroll to and click "Folder and search options" > View tab > Untick "Hide extensions for known file types" > Click OK
OR
Start button > Control Panel > Appearance and Personalization > Folder Options > View tab > Advanced settings > Untick "Hide extensions for known file types" > click OK.

That allows you to change extensions.  You will be warned by Windows that you can screw things up by changing file extensions, but just OK the warning.  You will also see a red "file mismatch" warning here when you attach it, but you can mention in the caption that the file is a WPS or DOCX and that it has to be renamed back to that from TXT.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38769924
Thanks for that excellent explanation Bill!
David can you tell me what version Word on your XP machine (or is that now Windows 7!). If you still have it can you tell me if all the options were installed ( Add/Remove Programs, select Office > Change?
...Terry
0
 

Author Comment

by:ddantes
ID: 38770351
Hi Terry:  The laptop has two XP partitions -- one has Word 2007 and one has Word 2000, both with all options installed.  The Windows 7 partition has Word 2007.  The Office 2007 installations are virtually identical under Windows XP and Windows 7.
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38771461
Terry

Just for your info I looked into the following possible workaround using something like VBScript, but I think it would be impossible to effect because of variances between the docx files that have differing content:

1. Temporarily rename the first docx file as zip and unpack it to its own folder.
2. Find all the references to the OLE objects in the relevant unpacked files and change them to standard images with hyperlinks, the correct syntax of the replacement lines being obtained from an example docx file created with an embedded image and hyperlink.
3. Zip the files back up and rename back to docx files with a suffix in the file name to identify it.
4. Perform the same process on the rest of the docx files in that folder and all sub-folders.

Unfortunately the references to the binary data (the embedded packaged *.URL files) occur in too many of the unzipped files to make parsing and replacing all the lines with standard hyperlinked images feasible.

It's easy to grab the full web address from the "oleObject1.bin" file and set it as a reusable variable using even the most basic find command:

for /f "tokens=2 delims=^=" %A in ('type oleObject1.bin ^| find /i "BASEURL"') do set HLINK=%A

Unfortunately using that URL and inserting it in all the correct places in the files that make up a docx file is way too clunky and a bit hit-and-miss to be a realistic proposition to pursue.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38778216
Sorry it took me so long to reply my PC died when I tried to configure for a multi-boot like David's, going back from Windows 8 instead of forward from XP...

Bill that is a great idea, I'm working on that now
0
 
LVL 16

Expert Comment

by:terencino
ID: 38781163
Ok this is definitely a path I have never travelled, and the mysteries of OLEObjects remain, as is the reason why it won't work in XP. However, I managed to extract the URL from the OLE objects, can you test this on your setup, using the same test file we have been using (just put the full path in the macro).

The code will open the file, find the second object, convert it to a picture, search within that for the URL, then colse & reopen the file, find the same object, delete it and add a normal hyperlink.

Let me know how it goes
...Terry

Sub find_objects()

' Put test file name as Target file
  TargetFile = "C:\Test.docx"
  Documents.Open FileName:=TargetFile
  ActiveDocument.InlineShapes(2).OLEFormat.ConvertTo ClassType:="Word.Picture"
  ActiveDocument.InlineShapes(2).OLEFormat.DoVerb VerbIndex:=wdOLEVerbPrimary
  With Selection
    .WholeStory
    .Find.ClearFormatting
    .Find.Text = "url="
    .Find.Execute
    .MoveRight Unit:=wdCharacter, Count:=1
    .EndKey Unit:=wdLine, Extend:=wdExtend
    WPSLink = .Range
  End With
  ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
  ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
  Documents.Open FileName:=TargetFile
  ActiveDocument.InlineShapes(2).Select
  ActiveDocument.InlineShapes(2).Delete
  Selection.InsertAfter WPSLink
  ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:=WPSLink
End Sub

Open in new window

0
 
LVL 38

Expert Comment

by:BillDL
ID: 38781411
....... and that's the reason that I realised it was beyond my level of knowledge ;-)
0
 

Author Comment

by:ddantes
ID: 38781452
Hi Terry:  Thank you for that code.  It did just what you expected.  The second URL shortcut was converted to a text hyperlink which opens the URL.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38781484
Well that is good news. What this means is (with an update to the code) you can run through all of the files as we did before, extract the hyperlinks, delete the old OLE package, insert a new icon (or picture or shape etc) in the same location and then add the link to that icon. Or just use text. The benefit is the links will work OK in XP, plus they are preserved in case you print to PDF. Also more easily accessible from code.

Let me know if you want to proceed.
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38781508
All I can say is that I'm very impressed with Terry's code so far and relieved for you (David) that you can get all your documents converted.  I'm pretty sure this has happened to others in the past and may well come up again, so hopefully this question will also provide a useful resource to others at some later time.
0
 

Author Comment

by:ddantes
ID: 38781626
Most definitely, I would like to proceed.  However, since this is all an afterthought to a closed question, would you like me to post a new question before we proceed?
0
 
LVL 16

Expert Comment

by:terencino
ID: 38781721
Sure that would be great and also reference the previous questions to maintain the thread. Also David with the hyperlink you might want to give some thought as to the icon or picture you would like in the document, and perhaps include a more complex document for me to experiment with!
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38782141
Sorry to interrupt again with a suggestion about the image icon to use for the hyperlink.

When opened in Word 2003 (which uses the Office 2007 file format converter for *.docx files)  the Test.docx file presented for download in this question and the other question:
http://www.experts-exchange.com/Q_27992053.html
the packaged objects show with the green/blue earth/globe icon associated with "the Internet" on Windows computers, as shown in Terry's earlier screenshot.

In fact, the actual images are in *.WMF format (Windows MetaFile) which supports transparency just like PNG and GIF files do.  The caption, which is integrated into the image and sits in the transparent area below the globe icon, is what dictates the width of the complete image.  Both packaged objects are the same height, so to retain the layout of the existing Word documents it would be a good idea to choose another image the same height in pixels.

It's difficult to know the exact pixel dimensions of the icons in the test file.  When you look at the object properties of the embedded packages in Word they show a height of 51 pixels, whereas when extracted to separate files (unzipped) the resultant *.WMF files are 52 pixels high when opened in MS Paint, 50 pixels high in Paint Shop Pro, 64 pixels high in Microsoft Office Picture Manager, and the Windows Explorer properties show them as 64 pixels high.

I'm not sure why Word seems to reduce the actual size of the embedded image data to show it on the page.  I can't see a couple of pixels throwing out the text layout of the document enough to mess it up, so I think you would be safe choosing an icon between 48 and 64 pixels high.

As an example, the following image icons are free for non-commercial use:
http://www.iconfinder.com/icondetails/65946/128/url_icon
Most of these ones will be also:
http://www.iconfinder.com/search/?q=url
http://www.iconfinder.com/search/?q=link
http://www.iconfinder.com/search/?q=internet

When you post a link to your new continuation question here, I will get a notification and I can certainly help out with preparing an image for you in your new question if you need help with this.  An example composite image from the resources above to inspire you (hopefully) ;-)
64 x 64 pixel PNG
0
 
LVL 16

Expert Comment

by:terencino
ID: 38782221
Good point Bill, the icons can really put things out of whack. That is a really nice array of icons you linked to, I will definitely have to upgrade a few of my own. I also noticed in the OLEObject.bin files there was another link to http://www.bbonline.com/favicon.ico - I wonder if this was in the original documents? It is quite small 32x32 pixels, same as the image on the IE tabs of the website. An upgrade would seem to be in order, for example, the house on the main logo is a good image, shown here resized to 32 pixels high. I'm not good at the graphics, Paint told me the image would lose its transparency if I saved as PNG, so some expertise would be required in that area!
BBOnline logo
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38782625
Generally a saved *.URL file stores the web path to the favicon.ico if one exists in the root of the webspace.  It is cached in the Temporary Internet Files folder (or equivalent browser cache for others) and an internet shortcut will continue to display that icon until the browser cache is cleared.  If not specified in the URL file's code against the    IconFile=    and    IconIndex=   lines, it will display the default for that operating system and browser.  For IE8 in XP, the icon displayed is the blue IE "e" on dog-eared white background which is the 1st icon in "%SystemRoot%\system32\url.dll", that is to say it is at Index No. Zero.  The icon is cached locally again when the user next browses to that site if specified as the favicon.ico in the URL file's code.

As far as I am aware an *.ICO file can't be inserted into a Word document (unless the actual *.URL file is being embedded as a binary object that is activated at runtime), because it's not really a true "image" file type.  An *.ico file is just a repository for separate icon resources of varying pixel dimensions that are referred to as ordinals.  Some platforms and browsers will load much larger favicon.ico files than 16x16 or 32x32, and some can load PNG files in place of *.ico files, which I believe is probably the reason for the larger dimension PNG files and *.ico equivalents on the pages I linked to.

In the *.WPS documents I think that a generic image has been embedded from Word/Works or Windows resources as the web link was embedded into it, and was stored as *.WMF data, or else this generic image of the globe was generated at conversion from WPS to DOCX.  It looks like the IconIndex No. 12 in iexplore.exe (v. 8 that is), but quite scrappy around the edges.

Image transparency is only really an issue if the image is something like a cut-out shape to be overlayed on a different coloured background and you don't want a white rectangle shape.  On the white page of a Word document it really isn't going to matter if a PNG gets merged to a single layer and the transparent areas become white.
0
 

Author Comment

by:ddantes
ID: 38783742
Thank you both for your thoughtful and innovative participation.  The icon in Bill's comment ID: 38782141 is fine with me.  However, I would still need a way to label the shortcut.  The new question is at
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_27997737.html
0
 

Author Comment

by:ddantes
ID: 38814648
Terry:  Checking to see if you are still engaged with this, and whether you have visited the new question?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now