Combining Macros to make one Macro.

[jdtivoli]

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

 

[Jim Rech]

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:=xlLinkTypeExcelLinks)(1), _
xlLinkTypeExcelLinks

So you only need one macro.

Use this to block the prompt:

Application.DisplayAlerts = False
ActiveWorkbook.ExclusiveAccess