VBA File Conversion then delete original

[magistercaesar]

Good morning everyone! First time posting on this forum. Can anyone help me with this code? What it does is it allows me to place a folder path name on column A of a spreadsheet, and then it will look for all of the .xls files and save them as .xlsb. However, I also need it to delete the original .xls file. An edit to the code or even an additional macro that I could run after this would be great appreciated. Thanks all.

Sub Convert_xlsb()
'
'Convert_xlsb Macro
'


'
Dim strPath As String

Dim strFile, strConvFile As String​

Dim wbk As Workbook​

Dim i As Integer​

Dim Dun As Boolean​

i = 0​

Dun = False​

Do Until Dun​

i = i + 1​

If ThisWorkbook.Worksheets(1).Cells(i, 1) <> "" Then​

' Path must end in trailing backslash​

' IE C:\Test\​

strPath = ThisWorkbook.Worksheets(1).Cells(i, 1).Value & "\"​

strFile = Dir(strPath & "*.xls")​

Do While strFile <> ""​

If Right(strFile, 3) = "xls" Then​

Set wbk = Workbooks.Open(Filename:=strPath & strFile)​

strConvFile = Replace(strFile, "xls", "xlsb")​

wbk.SaveAs Filename:=strPath & strConvFile, FileFormat:=xlExcel12​

wbk.Close SaveChanges:=False​

End If​

strFile = Dir​

Loop​

Else​

Dun = True​

End If​

Loop​

End Sub

 

[Dendro]

You can use the following line

Kill path & filename

However there are much easier ways to accomplish this, where you don't need to open the files. There are different kinds of software for this. I am using some freeware to do this. PM me if you want to know more about these programs since this doesn't belong in an excel forum.

 

[VBA Geek]

1) pass to the Sub the path of your folder, make sure it ends with the "\"

Warning: KILL will delete your files without sending them to the Bin

Sub dhChangeFormatAndDel(sFld As String)
Dim sFile As String
sFile = Dir$(sFld & "*.xls")
While Not sFile = vbNullString
    If Mid$(sFile, InStrRev(sFile, ".") + 1) = "xls" Then
        Name sFld & sFile As sFld & Left$(sFile, InStrRev(sFile, ".")) & "xlsb"
    End If
     sFile = Dir
Wend
On Error Resume Next
    Kill sFld & "*.xls"
On Error GoTo 0
End Sub

 

[VBA Geek]

actually you do not need the below lines of code because the files will be ranamed to xlsb in the same folder, did you want to rename them and move them elsewhere and then kill the oirignal xls files?

On Error Resume Next
Kill sFld & "*.xls"
On Error GoTo 0

[/CODE]

 

[Rick Rothstein]

As others have mentioned, the Kill statement is a permanent deletion. If you add a module to your project and put this code in it...

Private Type SHFILEOPTSTRUCT
  hWnd As Long
  wFunc As Long
  pFrom As String
  pTo As String
  fFlags As Integer
  fAnyOperationsAborted As Long
  hNameMappings As Long
  lpszProgressTitle As Long
End Type

Private Declare Function SHFileOperation _
                Lib "Shell32.dll" _
                Alias "SHFileOperationA" _
               (lpFileOp As SHFILEOPTSTRUCT) As Long
  
Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10

Public Sub Recycle(Filename As String)
  Dim FOP As SHFILEOPTSTRUCT
  With FOP
   .wFunc = FO_DELETE
   .pFrom = Filename
   .fFlags = FOF_ALLOWUNDO Or FOF_NOCONFIRMATION
  End With
  SHFileOperation FOP
End Sub

Then instead of using the keyword Kill in your own code (as has been suggested), substitute the keyword Recycle in that code line and the specified file will be deleted and sent to the Recycle Bin instead.

 

[Dendro]

...substitute the keyword Recycle in that code line and the specified file will be deleted and sent to the Recycle Bin instead

Thanks, i didn't know this!

 

[Rick Rothstein]

...substitute the keyword Recycle in that code line and the specified file will be deleted and sent to the Recycle Bin instead

Thanks, i didn't know this!

Just to be clear... that substitution only works if the code posted in Message #5 is copied to a (general) Module. I don't want someone coming along later and just reading your reply as written and thinking Recycle is a built-in command when it isn't.

 

[magistercaesar]

I tried using your code, but it's not working. How does it work? The only thing I have is the original module that I posted and an additional module for your code. Thanks