+ Reply to Thread
Results 1 to 5 of 5

Error Message - Macro to Unlink MS Queries from Workbook

Hybrid View

awalker2009 Error Message - Macro to... 06-18-2014, 04:02 AM
nathansav Re: Error Message - Macro to... 06-18-2014, 04:58 AM
TMS Re: Error Message - Macro to... 06-18-2014, 05:02 AM
awalker2009 Re: Error Message - Macro to... 06-18-2014, 07:05 AM
TMS Re: Error Message - Macro to... 06-18-2014, 07:13 AM
  1. 06-18-2014, 04:02 AM #1
    awalker2009 is offline
    Registered User
    Join Date
    06-18-2014
    Location
    Nottingham, UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Error Message - Macro to Unlink MS Queries from Workbook

    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 full 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


    The actual data starts at cell A2, except for the worksheet called Journal report. Row 1 is a heading I've added manually.



    Anyone got any suggestions?

    Thanks,
    Reply With QuoteRegister To Reply
  2. 06-18-2014, 04:58 AM #2
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Error Message - Macro to Unlink MS Queries from Workbook

    Where do you get the error?
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me
    Reply With QuoteRegister To Reply
  3. 06-18-2014, 05:02 AM #3
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,139

    Re: Error Message - Macro to Unlink MS Queries from Workbook

    You don't usually have to select anything to work with it, so try this:

    Option Explicit

Sub RemoveTableLinks()
'
' RemoveTableLinks Macro
'
'
On Error Resume Next
Sheets("BACS_UPLOAD_SHEET").ListObjects("Table_BACS_UPLOAD").Unlink
Sheets("Adults Authorisation Sheet").ListObjects("Table_Auth_ASCH").Unlink
Sheets("Childrens Authorisation Sheet").ListObjects("Table_Auth_CYP_1").Unlink
Sheets("Journal Report").ListObjects("Table_Jrnl_ASCH_Rech_1").Unlink
Sheets("Journal Report").ListObjects("Table_Jrnl_SP_Rech_1").Unlink
Sheets("Provider Credits").ListObjects("Table_Provider_Credits").Unlink
Sheets("Provider Totals Report").ListObjects("Table_Provider_Totals").Unlink
Sheets("Provider Comparison Report").ListObjects("Table_Prov_Comparision_1").Unlink
Sheets("Cost Centre Report").ListObjects("Table_Cost_centres_1").Unlink
Sheets("Client Group Totals").ListObjects("Table_Client_Grp_Totals").Unlink
Sheets("Service Totals").ListObjects("Table_Service_Totals").Unlink
'Sheets("Summary").Select
On Error GoTo 0

End Sub

    I can't test it as I don't have the workbook/links. But give it a go; see what happens.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 06-18-2014, 07:05 AM #4
    awalker2009 is offline
    Registered User
    Join Date
    06-18-2014
    Location
    Nottingham, UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Error Message - Macro to Unlink MS Queries from Workbook

    Thanks. That's solved it.
    Reply With QuoteRegister To Reply
  5. 06-18-2014, 07:13 AM #5
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,139

    Re: Error Message - Macro to Unlink MS Queries from Workbook

    Does it work? Actually removes the links? Because of the error handling, it shouldn't fail ... but has it removed the links?

    If it does, all well and good, and ...



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. XML Error Message qhen workbook opens
    By brucemc777 in forum Excel General
    Replies: 0
    Last Post: 03-26-2014, 01:55 PM
  2. Error Message when opening a workbook
    By fbiasi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2014, 04:59 PM
  3. [SOLVED] Error message: No more new fonts may be applied in this workbook
    By dpj1968 in forum Excel General
    Replies: 2
    Last Post: 01-18-2006, 08:40 PM
  4. [SOLVED] error message when saving excel workbook
    By Deb in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 04:05 PM
  5. Receive an error message when try to add a chart to a workbook
    By kysiow in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-21-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1