Code Or Command To Open XLSM Files And Close And Save As CSV

[Dazzawm]

Hi, is there a code or command that will open a load of xlsm files within a folder and then close and save them as csv files either in the same folder or a specified location? Thanks.

 

[bertie]

Hi Daz,

For future reference, formatting the file name is the sort of thing that would be beneficial to mention at the outset. It saves a lot of time revisiting and re-coding and doing things piecemeal.

The code to format the file name could be done using one line of code. I have done it this way to show you how we build up the string.

      'find the position of the underscore
      pos = InStr(wbSource.Name, "_")
      'strip out everything before the underscore
      newFileName = Right(wbSource.Name, Len(wbSource.Name) - pos)
      'add the folder path and re[lace XLSM with CSV
      newFileName = sPath & Replace(newFileName, ".xlsm", ".csv")

Again, replace the path variable with your output folder string.


Overwrite everything in the file? I don't understand? Why would you need to save the file as a csv? Why not create n new empty csv file from existing filenames?

Amended code below.

Option Explicit


Sub SaveAsCsv()
   Dim sPath As String
   Dim sFile As String
   Dim wbSource As Workbook
   Dim wbTarget As Workbook
   Dim newFileName As String
   Dim pos As Long
   
   sPath = "C:\temp\" 'REMEMBER TRAILING BACKSLASH
   sFile = Dir(sPath & "*.xlsm")
   
   'disable saving CSV alerts - use error trap to ensure application settings are reset
   On Error GoTo errHandler
   Application.DisplayAlerts = False
   
   'loop through the folder
   Do Until sFile = ""
      
      'open the workbook and build the new file name
      Set wbSource = Workbooks.Open(sPath & sFile)
      
      'find the position of the underscore
      pos = InStr(wbSource.Name, "_")
      'strip out everything before the underscore
      newFileName = Right(wbSource.Name, Len(wbSource.Name) - pos)
      'add the folder path and re[lace XLSM with CSV
      newFileName = sPath & Replace(newFileName, ".xlsm", ".csv")
      
      'ASSUME sheet to be saved as csv is first worksheet
      wbSource.Worksheets(1).Copy
      Set wbTarget = ActiveWorkbook
      wbTarget.SaveAs Filename:=newFileName, FileFormat:=xlCSV
      wbTarget.Close SaveChanges:=False
      wbSource.Close SaveChanges:=False
      
      'get next fileThis will probably need some editing at your end
      Set wbSource = Nothing
      Set wbTarget = Nothing
      sFile = Dir()
   Loop
   
errHandler:
   Application.DisplayAlerts = True
End Sub

 

[Dazzawm]

The reason for overwriting is changes are made daily to the files. They need to overwrite the files that are already in the folder so they can be uploaded onto another program overnight (which shouldn't have any affect on this code).

Will this code overwrite files that are of the same name when saved?

Thanks.

 

[bertie]

In the code we have disabled alerts, this will include the overwrite warning.
So, this line will save the file, overwriting any existing file.

wbTarget.SaveAs Filename:=newFileName, FileFormat:=xlCSV

NB existing data will be lost...forever!
Set up a few test folders and files and thoroughly test the code before deploying it.

 

[Dazzawm]

Will do! Thanks Bertie

 

[Dazzawm]

Everything is working Bertie apart from when I save it to the specified location is does not remove everything before the underscore?

 

[bertie]

Re-read post 11

 

[Dazzawm]

I have done as instructed in post 11, replacing sPath with the folder location.

 

[bertie]

I don't know what to tell you. The full code posted in post#11 works at my end.

Place a STOP command within the code and hover your mouse the the newFileName variable to see it's value.

      'find the position of the underscore
      pos = InStr(wbSource.Name, "_")
      'strip out everything before the underscore
      newFileName = Right(wbSource.Name, Len(wbSource.Name) - pos)
      
      Stop
      
      'add the folder path and re[lace XLSM with CSV
      newFileName = sPath & Replace(newFileName, ".xlsm", ".csv")

 

[Dazzawm]

I done that and it said the same name as the original file i.e everything include before the _

 

[Dazzawm]

I know I should have said about the renaming at the start but actually once this part is sorted I can use this code with a little bit of location changing on lots of different projects. As it turns out the renaming part is the most important one and I apologise for not telling you at the start.