VBA File Conversion then delete original

magistercaesar

New Member
Joined
Jul 2, 2014
Messages
5
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Dendro

Active Member
Joined
Jul 3, 2014
Messages
336
You can use the following line
Code:
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

Code:
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,142
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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...
Code:
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

Active Member
Joined
Jul 3, 2014
Messages
336
...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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,142
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
...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

New Member
Joined
Jul 2, 2014
Messages
5
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
 

Forum statistics

Threads
1,136,286
Messages
5,674,856
Members
419,530
Latest member
undisclosed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top