Solved

Revitalizing inoperative URL shortcut links in docx files after batch conversion from .wps

Posted on 2013-01-16
30
408 Views
Last Modified: 2013-04-04
This question is a continuation of a previously-closed question http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_27983156.html relating to batch conversion of .wps files to .docx    The batch conversion code which was developed in response to the previous question did a wonderful job of finding and converting .wps files to .docx.   However, URL hyperlink shortcuts which were embedded in the original .wps files did not work under Windows XP, after conversion to .docx.  Experts who participated in the original question have kindly agreed to participate here, with the intention of developing another batch process for revitalizing these URL shortcuts.
0
Comment
Question by:ddantes
  • 17
  • 12
30 Comments
 

Author Comment

by:ddantes
ID: 38783762
Terry:  you asked for a more complex sample docx file, with embedded shortcuts and text.  Please see the attached file.   It has multiple URL shortcuts, then text, then another shortcut.

GoHawaii.docx
0
 
LVL 38

Expert Comment

by:BillDL
ID: 38785974
Just marking this so that I get notifications by email for new comments.
0
 

Author Comment

by:ddantes
ID: 38798994
Terry:  A moderator asked me if this question should still be active.  No pressure, but can you let me know if you are still engaged with the question?  Thanks.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38816187
Hi David yes I am still working on this a bit at a time. Thanks for the new document in the new question it is certainly a challenge. In particular, extraction of the title. I've been on site for the last week but back home now. Will have a response this evening!
...Terry
0
 

Author Comment

by:ddantes
ID: 38816231
Thank you for the update.  I was a little concerned whether you are OK, which is more relevant than the solution!
0
 
LVL 16

Expert Comment

by:terencino
ID: 38816269
Ha thank you! There has been heavy rain at site lately and I thought my flight out was going to be cancelled, but all went well.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38826635
Ok at last I have something for you to test, this has been a challenge I can tell you! The real issue of course is that I can't access the required properties via VBA, instead they are scraped out of the text of the OLEObject picture, using trial and error. This works for all the URLs in the supplied document. It is just for testing still, and won't modify any files, however it has not error control so may crash under a number of circumstances.

Let me know how you go
...Terry
Replace-URLs.doc
0
 

Author Comment

by:ddantes
ID: 38828072
First messageSecond message Hi Terry:  Thank you for the test module, and I'm sorry for the exhaustive effort involved.  A couple of messages appeared when I applied the module to the supplied file, and I've attached screenshots.  Also, when navigating to the file which is to be processed, the first time the file is selected, nothing much happens -- a momentary flash on the screen, and then the dialog box for selecting the file still appears.  The second time the file is selected, a process definitely happens, with text and unknown characters being highlighted and scrolled.  And then the first message appears.  Clicking OK, the second message appears.  After clicking "End", the file to be processed behaves as it did before, i.e., the embedded URL shortcuts do not open the target.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38828754
Hi David I should have explained, the first dialog asks for the file you wish to update eg the GoHawaii.docx, and the second dialog asks for the picture you will use to replace the URL picture eg Example-Link-Image.png . When it has those two files it will operate as expected. I hope.

You could hard code the files if you like by changing the following lines :
Sub ReplaceURLs()
Dim NewURL As InlineShape, WPSLinks(50, 5) As Variant, URLCount As Integer, OriginalDoc As Document, testdoc As FileDialog
' Put test file name as Target file
Dim url1 As Range, url2 As Range, URLPosition As Range
' Select file to open (just one for the moment as this is in test mode)
TargetFile = FilePick("Select converted file containing Works URLs")
If TargetFile = vbCancel Then Exit Sub
' Location of picture to be used in place of icon
NewURLPicture = FilePick("Select picture to replace URL icon")
If NewURLPicture = vbCancel Then Exit Sub

Open in new window

..to something like...
Sub ReplaceURLs()
Dim NewURL As InlineShape, WPSLinks(50, 5) As Variant, URLCount As Integer, OriginalDoc As Document, testdoc As FileDialog
' Put test file name as Target file
Dim url1 As Range, url2 As Range, URLPosition As Range
' Select file to open (just one for the moment as this is in test mode)
TargetFile = "C:\GoHawaii.docx" 'FilePick("Select converted file containing Works URLs")
'If TargetFile = vbCancel Then Exit Sub
' Location of picture to be used in place of icon
NewURLPicture = "C:\Example-Link-Image.png" 'FilePick("Select picture to replace URL icon")
'If NewURLPicture = vbCancel Then Exit Sub

Open in new window

0
 

Author Comment

by:ddantes
ID: 38828821
Understood.  I'll test the module again with a two-stage process, then post...
0
 

Author Comment

by:ddantes
ID: 38828948
Perfect!   What a glorious result.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38829184
Whew! Can you also try it out with another couple of documents? There are likely to be more URLs that won't be converted, and if you can find a document that doesn't work can you send a sample of that?
0
 

Author Comment

by:ddantes
ID: 38829275
test.zipThere are more than two hundred converted docx files with embedded URL shortcuts and icons, distributed among dozens of folders.  I've made copies of all of them and placed the copies in a single folder.  So I'm wondering if there is a way to use your module to process those copied files, and then I could aggregate the ones which produce an error?  If not, I will work on testing them one at a time, but that will take some time.  In the meanwhile, I tried several files, and found around 50% of them had an issue.  I've uploaded these.
0
 

Author Comment

by:ddantes
ID: 38916625
Terry:  Do you still want to pursue this question?
0
 
LVL 16

Expert Comment

by:terencino
ID: 38916765
Hi David, sorry I missed your last post, I'll check them out and get back to you shortly
...Terry
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 16

Expert Comment

by:terencino
ID: 38922957
David, the documents that had errors do not have the BASEURL= parameter embedded in the picture. The macro looks for that and if it is not found there is no error handling. However, for all except one (A1.docx which has no embedded package or hyperlink) they do have the URL= parameter so I can check for that in the next update.

I can update the routine to cycle through all the documents in the folder and flag those that don't produce the expected outcome.
...Terry
0
 

Author Comment

by:ddantes
ID: 38923435
Hi Terry:  If you are OK with doing that update, that's wonderful.  Let me know what you need from my end.
0
 

Author Comment

by:ddantes
ID: 38938618
Terry:  Just making sure you aren't waiting for some input from me.  Thanks.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38940276
Hi David, apologies for the delay, I'm hoping to have an update for you tonight.
...Terry
0
 

Author Comment

by:ddantes
ID: 38940342
No rush, Terry.  I just wanted to make sure I didn't need to send you anything in order for you to take the next step.  Thanks.
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38947147
Finally I have something for you David. This has taken me down some roads I never expected, like learning how useful Regular Expressions are. First open up the userform in VBE and in the code for CommandButton2_Click change the location of the new icon for the URLs from C:\new_icon.png to wherever you have it. Note also that this version saves the changes to the files that it finds, so make sure you have an another copy of the original files in the folder you are processing! It just seemed cleaner to do it this way. The code is getting quite long so I won't post it here, but hopefully there aren't too many other URLs that are different in the remainder of your files. For example there is one file that doesn't have an embedded OLE Object, it is just a picture, no hyperlink. Files like that get skipped, also those where I can't retrieve a URL, or a title. There is an indication however in the list box. Please try this out and let me know how you go.
...Terry
Fix-WPS-URLs-2.doc
0
 

Author Comment

by:ddantes
ID: 38947624
Response to pressing "Process"Hi Terry:  Thank you for your persistence!   I changed the path to the icon, as instructed, and then I tested the module on a folder with a single, converted docx file.   There were a couple of unexpected behaviors.  First, after pressing Find Files, and navigating to the folder containing the test file, there was a dialog box saying: "No items match your search."  But if I clicked "OK", then the docx file appeared in the list box.  Next, after clicking "Process" , a dialog box appeared (please see screen shot).
0
 

Author Comment

by:ddantes
ID: 38947692
Further test results:  I added a few more docx files to the test folder, and then the dialog box which appeared in the first test did not appear.  It seems there must be more than one file in the search location, in order to avoid that dialog appearing.   All but one of the files were converted, and the saved files had the appropriate URL icon and the links work!   There was a runtime error related to a single docx file which did not contain a hyperlink.  Initial tests are very promising.  I'll post again after further testing...
0
 
LVL 16

Expert Comment

by:terencino
ID: 38947829
Ha I meant to fix that. Should press Find files... button first, then Process button, and the initial message was for when the Process button was pushed first. But there should just be one button that perfroms all the functions.

Can you send the file that had the error and I'll have a look at that, I would prefer you don't get a run time error!
0
 

Author Comment

by:ddantes
ID: 38947853
Twitter.docxMicrosoft-AdCenter.docxJust for clarification, I did press Find Files first.   After navigating to the folder containing the docx files, there was the message "No items matched your search", and the folder appeared to be empty.  But clicking "OK" added the docx files to the list box.   Two files which triggered a run time error are attached.
0
 

Author Comment

by:ddantes
ID: 38975518
Hi Terry:  My wife and I are about to leave on vacation, returning home on Mar. 24.  So if you post something, there could be a delay before I can respond.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38975790
Hi David, I have been buried in work lately so have not addressed this as yet, however I will have a response for you on your return. In the meantime, have a wonderful vacation!
0
 

Author Comment

by:ddantes
ID: 39033554
Hi Terry:  We had a great time, but it's good to be home.  Please post when time allows.
0
 
LVL 16

Expert Comment

by:terencino
ID: 39047214
David glad you had a good holiday, I have just come back home myself. Unfortunately I haven't any more ideas on how to proceed with the remainder of the recalcitrant files. So I have to admit defeat. My apologies it has taken so long. Maybe a thought will strike later on and I can come back to it but I think it is probably time to close this question off.
It has been an interesting journey!
All the best
...Terry
0
 

Author Comment

by:ddantes
ID: 39048388
No worries, Terry.  The module you invented is already way beyond the call of duty.  When there are exceptions, I can manually replace the icon.   You've been a great help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

747 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