Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete files in VBA (97 SR2)

    I have a 'temporary' file of sorts, really all it is, is a copy of my spreadsheet.

    I know how to save the file, but is there any way to delete it?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    <big>Kill filename </big>should do it for you

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Delete files in VBA (97 SR2)

    DrkRealm

    Kill it DrkRealm. Just kill it... Shoot it right in the center of its first sheet, just kill it with your VBA commands...

    No, just kidding, the keyword is Kill...

    Kill "TestFile" where TestFile can be the path + name of a file.

    But if you have set any references to that file, you need to unref. them by doing:

    Set RefToKilledFile = Nothing

    HTH...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> I'd love to.. on a more permanent basis...

    Who knew it was so simple? <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Hi Andrew,

    I dislike Kill a bit, since it is irrevocable. I found this API routine to place files directly into the recycle bin, like when using explorer to delete files:

    Option Explicit

    'Courtesy: David Wiseman
    'From the MSDN articles:
    '
    'Manipulating Files with the SHFileOperation Function in
    'Visual Basic 4.0
    'Deborah L. Cooper September 1996
    '
    'and

    'Tip 176: Sending Files to the Recycle Bin in Visual Basic 4.0
    'December 5, 1995

    Type SHFILEOPSTRUCT
    hWnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAborted As Boolean
    hNameMaps As Long
    sProgress As String
    End Type

    Public Const FO_DELETE = &H3
    Public Const FOF_ALLOWUNDO = &H40
    Public Const FOF_NOCONFIRMATION = &H10

    Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
    (lpFileOp As SHFILEOPSTRUCT) As Long

    Public Function DeleteToRecycleBin(sFileName As String) As Long

    Dim szFileName As String
    Dim SHFileOp As SHFILEOPSTRUCT

    szFileName = sFileName & vbNullChar

    With SHFileOp
    .wFunc = FO_DELETE
    .pFrom = szFileName
    .fFlags = FOF_ALLOWUNDO Or FOF_NOCONFIRMATION
    End With

    DeleteToRecycleBin = SHFileOperation(SHFileOp)
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Thanks for that one Jan.

    Kill, as the name suggests, is a bit final ok.

    Andrew

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Jan Karel,

    Sorry, I don't know the answer here, I'm just posing the question.

    Is there a way to use the File Scripting Object to execute a file delete, which just puts a file into the recycle bin?

    It involves a reference to the file scripting objtect, which might cause distribution problems- but might be simpler to use than an API call (and it gives you lots of other alternatives), without the finality of a "kill".
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Hi Geoff,

    Hmm, dunno really. I have little to no experience with the File scripting Object.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete files in VBA (97 SR2)

    It looks simple, just DeleteFile, but why can't I get any help? Where's the documentation for this file scripting object? I found find file example which was nice, so I added a reference to Microsoft Scripting Runtime and entered the following code:<pre>Dim fso As New FileSystemObject
    fso.DeleteFile</pre>

    I pressed F1 after DeleteFile and I still have a blank help screen. Seems like poor help, Bill Gates! <img src=/S/bash.gif border=0 alt=bash width=35 height=39> So I'm mad and am not going to try to see if DeleteFile uses the recycle bin. If anyone has used DeleteFile, it would be great if you posted some info. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Sam,

    Try <pre>Dim strFileName As String
    Dim oFso As FileSystemObject
    Set oFso = New FileSystemObject
    strFileName = "D:testfile.txt"
    oFso.GetFile(strFileName).Delete</pre>

    However, it does not seem to send anything to the Recycle bin.

    Andrew

  11. #11
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Jan,

    I tired using your code in Excel 2000-SP1 and it deletes my test file I created but does not put it in the recycle bin?

    Sub DeleteFile()

    'Example use of Function DeleteToRecycleBin()

    'Add a new workbook
    Set newBook = Workbooks.Add
    With newBook
    .SaveAs Filename:="DeleteTest.xls"
    End With

    'Close workbook
    Workbooks("DeleteTest.xls").Close SaveChanges:=False

    'Delete workbook
    DeleteToRecycleBin ("DeleteTest.xls")

    End Sub

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete files in VBA (97 SR2)

    That's exactly what I had in mind, but why the blank help screen? It happens on both my Win2K & WinNt systems. Is there a book that describes the File Scripting object model? Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Maybe the deletetorecyclebin is unsure which file you want deleted. Try doing it like this:

    Sub DeleteFile()

    'Example use of Function DeleteToRecycleBin()
    dim sFullName as string
    'Add a new workbook
    Set newBook = Workbooks.Add
    With newBook
    .SaveAs Filename:="DeleteTest.xls"
    sFullName=.FullName
    End With

    'Close workbook
    Workbooks("DeleteTest.xls").Close SaveChanges:=False

    'Delete workbook
    DeleteToRecycleBin (sFullName)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    Sam

    Not sure what your Help problem is, but I just get a meassage that help file VBENLR98.CHM cannot be found.

    Check this link <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?URL=/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSystemObject.htm>MSDN</A> for a little assistance.

    Andrew

  15. #15
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete files in VBA (97 SR2)

    <hr>why the blank help screen? <hr>
    could this be something to do with the secondary subject in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=29891&page=&view =&sb=&o=&vc=1>this</A> thread?

Page 1 of 2 12 LastLast

Posting Permissions

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