Delete Excel worksheets from Access.

Discussion in 'Microsoft Access Macros' started by Perry, Apr 3, 2010.

  1. Perry

    Perry Guest

    I tried to delete all the Excel worksheets from within Access except the
    first sheet but was unsuccessful. This is the code.

    Sub test1()
    Dim oXL, wk, sht1, cnt, ptr
    Set oXL = CreateObject("Excel.Application")
    Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    ptr = wk.sheets.Count
    Debug.Print "Before count:" & ptr
    For ptr = 2 To ptr
    wk.worksheets(ptr).Delete
    Next ptr
    Debug.Print "After count:" & wk.sheets.Count
    wk.Close
    Set oXL = Nothing
    End Sub

    Thank you for the help.
     
    Perry, Apr 3, 2010
    #1
    1. Advertisements

  2. Perry

    Perry Guest

    Sorry for the typo, the routine should read. It executed but sheets were not
    deleted.

    Sub test1()
    Dim oXL, wk, sht1, cnt, ptr, ptr1
    Set oXL = CreateObject("Excel.Application")
    Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    ptr = wk.sheets.Count
    Debug.Print "Before count:" & ptr
    For ptr1 = 2 To ptr
    wk.worksheets(ptr1).Delete
    Next ptr1
    Debug.Print "After count:" & wk.sheets.Count
    wk.Close
    Set oXL = Nothing
    End Sub
     
    Perry, Apr 3, 2010
    #2
    1. Advertisements

  3. Perry

    GJ_ Guest

    Op 3-4-2010 4:03, Perry schreef:
    > Sorry for the typo, the routine should read. It executed but sheets were not
    > deleted.
    >
    > Sub test1()
    > Dim oXL, wk, sht1, cnt, ptr, ptr1
    > Set oXL = CreateObject("Excel.Application")
    > Set wk = oXL.workbooks.Open(CurrentProject.Path& "\POReport#2.xls")
    > ptr = wk.sheets.Count
    > Debug.Print "Before count:"& ptr
    > For ptr1 = 2 To ptr
    > wk.worksheets(ptr1).Delete
    > Next ptr1
    > Debug.Print "After count:"& wk.sheets.Count
    > wk.Close
    > Set oXL = Nothing
    > End Sub


    Hi,

    Could it be the case, that your code deletes worksheet 2, (your former
    worksheet 3 will be worksheet 2 from now on), then your code skips to
    worksheet 3 (formerly known as ws 4) and deletes it, etc?
    Or were none of the sheets deleted at all?

    I would try something like this (code not tested):

    Dim oXL, wk, sht1, cnt, ptr, ptr1
    Set oXL = CreateObject("Excel.Application")
    Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    ptr = wk.sheets.Count
    Debug.Print "Before count:" & ptr
    While ptr > 2
    ptr = wk.sheets.Count
    wk.worksheets(ptr).Delete
    Wend
    Debug.Print "After count:" & wk.sheets.Count
    wk.Close
    Set oXL = Nothing

    End Sub
     
    GJ_, Apr 3, 2010
    #3
  4. Perry

    Perry Guest

    Thank you. It did not work. I tried many different ways without success.

    "GJ_" wrote:

    > Op 3-4-2010 4:03, Perry schreef:
    > > Sorry for the typo, the routine should read. It executed but sheets were not
    > > deleted.
    > >
    > > Sub test1()
    > > Dim oXL, wk, sht1, cnt, ptr, ptr1
    > > Set oXL = CreateObject("Excel.Application")
    > > Set wk = oXL.workbooks.Open(CurrentProject.Path& "\POReport#2.xls")
    > > ptr = wk.sheets.Count
    > > Debug.Print "Before count:"& ptr
    > > For ptr1 = 2 To ptr
    > > wk.worksheets(ptr1).Delete
    > > Next ptr1
    > > Debug.Print "After count:"& wk.sheets.Count
    > > wk.Close
    > > Set oXL = Nothing
    > > End Sub

    >
    > Hi,
    >
    > Could it be the case, that your code deletes worksheet 2, (your former
    > worksheet 3 will be worksheet 2 from now on), then your code skips to
    > worksheet 3 (formerly known as ws 4) and deletes it, etc?
    > Or were none of the sheets deleted at all?
    >
    > I would try something like this (code not tested):
    >
    > Dim oXL, wk, sht1, cnt, ptr, ptr1
    > Set oXL = CreateObject("Excel.Application")
    > Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    > ptr = wk.sheets.Count
    > Debug.Print "Before count:" & ptr
    > While ptr > 2
    > ptr = wk.sheets.Count
    > wk.worksheets(ptr).Delete
    > Wend
    > Debug.Print "After count:" & wk.sheets.Count
    > wk.Close
    > Set oXL = Nothing
    >
    > End Sub
    >
    > .
    >
     
    Perry, Apr 3, 2010
    #4
  5. GJ_ is correct: the problem is because the worksheets renumber themselves
    when you delete.

    A solution, though, is to delete from the end:

    For ptr1 = ptr To 2 Step -1
    wk.worksheets(ptr1).Delete
    Next ptr1

    Another is

    For ptr1 = 2 To ptr
    wk.worksheets(2).Delete
    Next ptr1


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Perry" <> wrote in message
    news:...
    > Thank you. It did not work. I tried many different ways without success.
    >
    > "GJ_" wrote:
    >
    >> Op 3-4-2010 4:03, Perry schreef:
    >> > Sorry for the typo, the routine should read. It executed but sheets
    >> > were not
    >> > deleted.
    >> >
    >> > Sub test1()
    >> > Dim oXL, wk, sht1, cnt, ptr, ptr1
    >> > Set oXL = CreateObject("Excel.Application")
    >> > Set wk = oXL.workbooks.Open(CurrentProject.Path&
    >> > "\POReport#2.xls")
    >> > ptr = wk.sheets.Count
    >> > Debug.Print "Before count:"& ptr
    >> > For ptr1 = 2 To ptr
    >> > wk.worksheets(ptr1).Delete
    >> > Next ptr1
    >> > Debug.Print "After count:"& wk.sheets.Count
    >> > wk.Close
    >> > Set oXL = Nothing
    >> > End Sub

    >>
    >> Hi,
    >>
    >> Could it be the case, that your code deletes worksheet 2, (your former
    >> worksheet 3 will be worksheet 2 from now on), then your code skips to
    >> worksheet 3 (formerly known as ws 4) and deletes it, etc?
    >> Or were none of the sheets deleted at all?
    >>
    >> I would try something like this (code not tested):
    >>
    >> Dim oXL, wk, sht1, cnt, ptr, ptr1
    >> Set oXL = CreateObject("Excel.Application")
    >> Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    >> ptr = wk.sheets.Count
    >> Debug.Print "Before count:" & ptr
    >> While ptr > 2
    >> ptr = wk.sheets.Count
    >> wk.worksheets(ptr).Delete
    >> Wend
    >> Debug.Print "After count:" & wk.sheets.Count
    >> wk.Close
    >> Set oXL = Nothing
    >>
    >> End Sub
    >>
    >> .
    >>
     
    Douglas J. Steele, Apr 3, 2010
    #5
  6. Perry

    Noah Meyer Guest

    Hi Perry,

    I would use Excel's built in "worksheets" collection to make sure you
    are deleting the correct sheets. Also, Excel is probably prompting
    you for confirmation before deleting each sheet. To disable this
    confirmation, set the DisplayAlerts property to False. Also, when you
    close the workbook you should explicitly save the workbook. Your code
    should look something like this:

    Sub test1()
    Dim oXL, wk, sht1, cnt, ptr, ptr1
    Set oXL = CreateObject("Excel.Application")
    Set wk = oXL.workbooks.Open(CurrentProject.Path &
    "\POReport#2.xls")

    oXL.DisplayAlerts = False

    Dim ws as oXL.Worksheet

    For Each ws In Excel.Worksheets

    If ws.Index <> 1 Then
    ws.Delete
    End If

    Next ws

    wk.Close True

    oXL.DisplayAlerts = True


    Set oXL = Nothing

    End Sub



    On Apr 2, 10:03 pm, Perry <> wrote:
    > Sorry for the typo, the routine should read. It executed but sheets were not
    > deleted.
    >
    > Sub test1()
    >     Dim oXL, wk, sht1, cnt, ptr, ptr1
    >     Set oXL = CreateObject("Excel.Application")
    >     Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
    >     ptr = wk.sheets.Count
    >     Debug.Print "Before count:" & ptr
    >     For ptr1 = 2 To ptr
    >         wk.worksheets(ptr1).Delete
    >     Next ptr1
    >     Debug.Print "After count:" & wk.sheets.Count
    >     wk.Close
    >     Set oXL = Nothing
    > End Sub
     
    Noah Meyer, Apr 5, 2010
    #6
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael The Red
    Replies:
    0
    Views:
    600
    Michael The Red
    Sep 10, 2003
  2. Guest

    Delete Excel Spreadsheet from Access Macro

    Guest, Mar 23, 2005, in forum: Microsoft Access Macros
    Replies:
    4
    Views:
    1,471
    Chris
    Mar 24, 2005
  3. Guest

    Can a macro import multiple Excel worksheets

    Guest, Jul 29, 2005, in forum: Microsoft Access Macros
    Replies:
    2
    Views:
    493
    Guest
    Aug 1, 2005
  4. Tiffany via AccessMonster.com

    Transfer Large Access table records to Many Excel Worksheets

    Tiffany via AccessMonster.com, Mar 17, 2006, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    205
  5. koo7891
    Replies:
    0
    Views:
    303
    koo7891
    Apr 22, 2007
Loading...

Share This Page