Combining Macros to make one Macro.
 

I have several workbooks, each has many formulas which get data from on
other workbook.

Example: Workbook #1 gets data from workbook 1a.
Workbook #2 gets data from workbook 2a.
Workbook #3 gets data from workbook 3a.
Workbook #4 gets data from workbook 4a.
Workbook #5 gets data from workbook 5a.

The "a" workbooks are created with data automatically by an expor
function from Access 97.

I have created a macro for each workbook which unshares, unprotects
breaks links, and resaves the workbook.

These macros are all saved in my personal.xls file.

Each macro uses a different ctrl+shift+letter shortcut key to run. I
is really inconvenient to try to remember which shortcut key goes wit
the particular form I am using at the time.

What I would like to do is to somehow combine these macros into jus
one macro which will check to see which form I have open and then ru
the appropriate instructions while only using one shortcut key.

Below is the VBA of each macro:

Sub UndoLinksDataInput()
'
' UndoLinksDataInput Macro
' Macro recorded 6/25/2004 by B80671
'
' Keyboard Shortcut: Ctrl+l
'
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect
ActiveWorkbook.BreakLink Name:="C:\BBTDATA\Data Input.xls", Type:
_
xlExcelLinks
ActiveWorkbook.Save
End Sub


Sub UndoLinksBNumber()
'
' UndoLinksBNumber Macro
' Macro recorded 6/25/2004 by B80671
'
' Keyboard Shortcut: Ctrl+m
'
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect
ActiveWorkbook.BreakLink Name:="C:\BBTDATA\Search by B Number.xls"
Type:= _
xlExcelLinks
ActiveWorkbook.Save
End Sub


Sub UndoLinkSerialNumber()
'
' UndoLinkSerialNumber Macro
' Macro recorded 6/25/2004 by B80671
'
' Keyboard Shortcut: Ctrl+n
'
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect
ActiveWorkbook.BreakLink Name:="C:\BBTDATA\Search by Seria
Number.xls", _
Type:=xlExcelLinks
ActiveWorkbook.Save
End Sub


Sub UndoLinkUsersName()
'
' UndoLinkUsersName Macro
' Macro recorded 6/25/2004 by B80671
'
' Keyboard Shortcut: Ctrl+o
'
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect
ActiveWorkbook.BreakLink Name:="C:\BBTDATA\Search by User
Name.xls", Type _
:=xlExcelLinks
ActiveWorkbook.Save
End Sub


Sub UndoLinksDate()
'
' UndoLinksDate Macro
' Macro recorded 7/8/2004 by B80671
'
' Keyboard Shortcut: Ctrl+t
'
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect
ActiveWorkbook.BreakLink Name:="C:\BBTDATA\Search by Dat
Form.xls", Type _
:=xlExcelLinks
ActiveWorkbook.Save
End Sub


Also, when the ActiveWorkbook.ExclusiveAcces occurs, it asks fo
confirmation. If there were a way to automatically tell it yes, so i
would not pause at that point, that would be great.

This has been torturing me for quite some time now. Any help would b
greatly appreciated.

Jess

--
Message posted from http://www.ExcelForum.com

Combining Macros to make one Macro.
 

The macros only vary by the name of the linked file. Assuming they all have
just one linked file you can break it by position rather than by name:

ActiveWorkbook.BreakLink _
ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)(1), _
xlLinkTypeExcelLinks

So you only need one macro.

Use this to block the prompt:

Application.DisplayAlerts = False
ActiveWorkbook.ExclusiveAccess

--
Jim Rech
Excel MVP