?
Solved

Break all links via VBA and save file

Posted on 2010-08-28
15
Medium Priority
?
2,204 Views
Last Modified: 2012-05-10
Hi,

I have linked some charts from an excel workbook, I would like to have a button (non printable) that would break all links and save the filename as todays date, is this possible?

Thanks!
0
Comment
Question by:sanjshah12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 10

Expert Comment

by:MaduKp
ID: 33552363
Below thread will be help to get an idea about removing links before save

http://www.eggheadcafe.com/software/aspnet/32671218/button-to-break-links-delete-all-vba-code--save-as.aspx

0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 600 total points
ID: 33552396
You can use a macro like this, but a non-printable button is more problematic.

You could use a Macrobutton and set its font attribute to Hidden, and make sure that the option to view hidden text is set and the option to print hidden text is not set.

Ideally the button should be on a toolbar, and not the document. Which version of Word are you using?
Sub UnlinkAndSaveAsDate()
    ActiveDocument.Fields.Unlink
    ActiveDocument.SaveAs "C:\MyFolder\" & Format$(Now, "YYYY_MM_DD") & ".doc"
End Sub

Open in new window

0
 

Author Comment

by:sanjshah12
ID: 33607271
Graham, thanks for your code, I've added the following code to break the links, but could you please give me some more information/direction on how to create a non printing button.

I'm using Word 2003

Thanks.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33698707
For a button - how about one in the menu area?

1. Assuming a macro like UnlinkAndSaveAsDate exists in a normal module of normal.dot, (to make sure it's always available) then

2. Right click the menu area and select customise
3. toolbars | New make available in normal.dot
4. OK | Drag the appropriate menu into the menu area
5. Commands | MAcros
6. Find UnlinkAndSaveAsDate and drag it onto your new toolbar
7. Right click the button and in the name field make it more meaningful to you

Chris
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33700950
I have created an Excel Addin (.xla file) which adds the ability to export the data from a spreadsheet to a file suitable to be included into a PHP script. We use this to quickly get the data into the accounts system.

I've attached the addin and removed the payload.

Depending upon how many users you want to give this facility to, you may want to look at using something like AD or GP to set a network level directory for addins. Otherwise you can copy the file to the user's addins folder and tell Excel to open it.

Upgrading an addin is just a case of making sure no one is using it and overwriting 1 file. No need to revisit each user.



Export-Spreadsheet.xla
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33700981
Doh. And now I realise you are talking about Word.

I'd guess you should be able to convert the macros in the xla to something that word can handle and then save that in the startup folder of Word.

What is interesting is that Word doesn't seem to have a global Startup - one to share amongst all users - as well as one for the user.

0
 

Author Comment

by:sanjshah12
ID: 33701154
Thanks RQuadling,  for your messages, I'll keep that file in case I need it later.

Thanks Chris for your steps to create a button on the toolbar, doe sthis mean unlike Excel where you can create a button on the fly via VBS this is not possible in Word?

Thanks
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33701529
You can do the same in Word VBA as I do in Excel VBA. I've just never done it.

I'd have to do some work on when to run the "add button" code and when to run the "remove button" code.

You don't want the button there when you close word as the menus are remembered.

I think it is something like Auto_Open and Auto_Close macros.

In reading about Word Addins, they are .dot file (yep, like templates - so stupid!!!!).

http://www.msoffice-tutorial-training.com/word-add-in.html

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33702514
Yes indeed you can create them on the fly ... i.e for a specific document if that is what you want just say.

Chris
0
 

Author Comment

by:sanjshah12
ID: 33703108
Yes, let me explain better, I have a document already prepared if I can add a button to this document that breaks all links (I can do this now from Graham's post above). Now when the user clicks the button I would like to remove the button and after executng the macro.

Hope this makes sense.

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1400 total points
ID: 33703318
The following for example will create the menu when the document is opened and remove it either when the document closes or the button is pressed - whichever occurs first.

Chris
'thisWorkbook CodePAge
Private Sub Document_Close()
    killBar
End Sub

Private Sub Document_Open()
    buttonAdd
End Sub

'Code Module
Sub buttonAdd()
Dim btn As CommandBarButton
Dim crbmenu As CommandBar

    With CommandBars("Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
        .Caption = "My Menu"
        .Visible = True
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "My Button"
            .OnAction = "UnlinkAndSaveAsDate"
            .Enabled = True
        End With
    End With
End Sub
Sub UnlinkAndSaveAsDate()
'    ActiveDocument.Fields.Unlink
'    ActiveDocument.SaveAs "C:\MyFolder\" & Format$(Now, "YYYY_MM_DD") & ".doc"
    killBar
End Sub

Sub killBar()
    On Error Resume Next
    CommandBars("Menu Bar").Controls("My Menu").Delete
    On Error GoTo 0
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 33703407
You could consider creating a shortcut key combination to run the macro.

Click KeyBoard... on the Customise dialogue.

Select Macros in the Categories box, and highlight your macro in the right-hand box. Enter your keystrokes, starting with Ctrl or Alt, keeping the key(s) depressed until a character or a function key is hit. If you choose a shortcut already in use, the command will be shown after the 'Currently assigned to': label.
0
 

Author Comment

by:sanjshah12
ID: 33703418
Chris,

That's perfect - thanks for your help.

0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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