Error Message - Macro to Unlink MS Queries from Workbook


Joined
Jun 18, 2014
Messages
1
Reaction score
0
I have several MS Queries feeding into an Excel 2010 workbook. When the user opens the workbook the worksheets pull through data from an Access database via MS Queries.

What I need is a macro so that the user can just click on a button and unlink ALL the worksheets at once.

I've tried recording the keystrokes to Unlink Table for each worksheet in turn. However, when I run the macro I get an error message

Run time error `9`

Subscript out of range

The actual code is:

Sub RemoveTableLinks()
'
' RemoveTableLinks Macro
'
'
Sheets("BACS_UPLOAD_SHEET").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_BACS_UPLOAD").Unlink
Sheets("Adults Authorisation Sheet").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Auth_ASCH").Unlink
Sheets("Childrens Authorisation Sheet").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Auth_CYP_1").Unlink
Sheets("Journal Report").Select
ActiveWindow.SmallScroll Down:=-3
Range("A6").Select
ActiveSheet.ListObjects("Table_Jrnl_ASCH_Rech_1").Unlink
Range("A8").Select
ActiveSheet.ListObjects("Table_Jrnl_SP_Rech_1").Unlink
Sheets("Provider Credits").Select
Range("Table_Provider_Credits[[#All],[Creditors_Ref]]").Select
ActiveSheet.ListObjects("Table_Provider_Credits").Unlink
Sheets("Provider Totals Report").Select
ActiveWindow.SmallScroll Down:=-3
Range("A2").Select
ActiveSheet.ListObjects("Table_Provider_Totals").Unlink
Sheets("Provider Comparison Report").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Prov_Comparision_1").Unlink
Sheets("Cost Centre Report").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Cost_centres_1").Unlink
Sheets("Client Group Totals").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Client_Grp_Totals").Unlink
Sheets("Service Totals").Select
Range("A2").Select
ActiveSheet.ListObjects("Table_Service_Totals").Unlink
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Summary").Select

End Sub


Thanks,



 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top