Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    How do i delete a named sheet.
    I recorded a macro and it returned the following, except that i cannot make it workL

    Sheets("Billing Report (per site)").Select
    ActiveWindow.SelectedSheets.Delete

    I am attempting to do this from Access.

    Can someone help me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say you have an Excel.Application object xlApp and an Excel.Workbook object xlWbk. You should be able to use

    xlApp.DisplayAlerts = False ' to suppress the warning
    xlWbk.Worksheets("Billing Report (per site)").Delete
    xlApp.DisplayAlerts = True

    Note: this won't work if Billing Report (per site) is the only sheet in the workbook, or if it is the only visible sheet in the workbook. A workbook must always have at least one non-hidden sheet (even if the workbook itself is hidden).

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='780947' date='22-Jun-2009 18:19']Let's say you have an Excel.Application object xlApp and an Excel.Workbook object xlWbk. You should be able to use

    xlApp.DisplayAlerts = False ' to suppress the warning
    xlWbk.Worksheets("Billing Report (per site)").Delete
    xlApp.DisplayAlerts = True

    Note: this won't work if Billing Report (per site) is the only sheet in the workbook, or if it is the only visible sheet in the workbook. A workbook must always have at least one non-hidden sheet (even if the workbook itself is hidden).[/quote]
    I tried that and it came up with an error (on the .delete command), viz:
    -2147417848 : Automation Error
    The object involved has disconnected from it's clients.

    The code i have in my prog is as follows:
    Dim xlApp As Object
    Dim xlWbk As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.workbooks.Open(theTemplateFile)
    ' Set mySheet = xlApp.workbooks.Open(theTemplateFile).sheets(1)
    .
    .
    . coding to do the job in here
    .
    .
    xlApp.DisplayAlerts = False ' to suppress the warning
    xlWbk.Worksheets("Billing Report (per site)").Delete <<<<<<<<< bombs here
    xlApp.DisplayAlerts = True

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I suspect that you have some code in the

    . coding to do the job in here

    part that refers to an Excel object without using xlApp directly or indirectly, for example by using ActiveWorkbook instead of xlApp.ActiveWorkbook, or ActiveSheet instead of xlWbk.ActiveSheet.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='781111' date='23-Jun-2009 10:04']I suspect that you have some code in the

    . coding to do the job in here

    part that refers to an Excel object without using xlApp directly or indirectly, for example by using ActiveWorkbook instead of xlApp.ActiveWorkbook, or ActiveSheet instead of xlWbk.ActiveSheet.[/quote]
    I copy sheets and insert rows, but i cant seem to find any references to those you have suggested.
    I also have the following code at the start of populating sheets:
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.workbooks.Open(theTemplateFile)
    Set mySheet = xlApp.workbooks.Open(theTemplateFile).Worksheets(" Billing Report (per site)")

    Does the mySheet command cause the problems?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're opening the workbook twice; there is no need for that. You can change

    Set mySheet = xlApp.workbooks.Open(theTemplateFile).Worksheets(" Billing Report (per site)")

    to

    Set mySheet = xlWbk.Worksheets("Billing Report (per site)")

    and then later on use

    mySheet.Delete

    to delete it.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='781129' date='23-Jun-2009 10:51']You're opening the workbook twice; there is no need for that. You can change

    Set mySheet = xlApp.workbooks.Open(theTemplateFile).Worksheets(" Billing Report (per site)")

    to

    Set mySheet = xlWbk.Worksheets("Billing Report (per site)")

    and then later on use

    mySheet.Delete

    to delete it.[/quote]
    I have done what you said, i also put these commands at the point i want to delete the sheet and still nogo.

    Set mySheet = xlWbk.Worksheets("Billing Report (per site)")
    mySheet.Delete

    It does not throw an error now but also does not delete the sheet.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post an attachment with the complete code?

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='781137' date='23-Jun-2009 11:35']Could you post an attachment with the complete code?[/quote]
    The code is massive. I will try to get some code together.

    Would protected cells stop me from deleting a sheet?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='781137' date='23-Jun-2009 11:35']Could you post an attachment with the complete code?[/quote]
    I have isolated the problem, i need to disable the question regarding deleting the sheet viz:
    xlApp.DisplayAlerts = False ' to suppress the warning
    mySheet.Delete
    xlApp.DisplayAlerts = True

    My next question on this is how do i save the workbook, the following is not right.
    mySheet.Parent.Close SaveChanges:=True

    Is it something like
    xlWbk.Close SaveChanges:=True

    I have just tested it, it now works, thank you for your help Hans.

Posting Permissions

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