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]

Run the code again to the STOP command an do some simple debugging.

Hover mouse over the position variable pos. If this is zero there is no underscore in the file name.

      '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

Compare the old file name with the new filename, i.e., hover mouse over:

      '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

Else
Press F8 to step through the code on line at a time checking variable values as the code passes over each line.


If you still have problems after debugging post the code you are using.

 

[Dazzawm]

Ok Bertie I will try again when back at work tomorrow and advise.

 

[bertie]

Fair enough. The reason I'm saying press F8 to step through the code is because there is an error trap. If the code suddenly jumps to the end you will know which line causes the error.

 

[Dazzawm]

Ok I did the debugging as you suggested.

1. When I hovered the mouse over 'pos' it said 13 which is odd as I only tried it using one file in the folder.
2. When I hovered over pos = InStr(wbSource.Name, "_") it had the the original name.
3. When I hovered over newFileName = Right(wbSource.Name, Len(wbSource.Name) - pos) it had the name without the underscore and name before which was good.
4. When I continued with F8 it saved the file with the original name again with the underscore!?

The code below is what I am using:-

Sub SaveAsCsvToUploads()
'http://www.mrexcel.com/forum/excel-questions/836297-code-command-open-xlsm-files-close-save-csv.html
   Dim sPath As String
   Dim sFile As String
   Dim wbSource As Workbook
   Dim wbTarget As Workbook
   Dim newFileName As String
   
   sPath = "C:\Users\manager\Desktop\Darrens Catalogues Copy\" '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
      
      Stop
      newFileName = "\\110.90.1.215\apps\UPLOAD\" & Replace(wbSource.Name, ".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 file
      Set wbSource = Nothing
      Set wbTarget = Nothing
      sFile = Dir()
   Loop
   
errHandler:
   Application.DisplayAlerts = True
End Sub

 

[bertie]

wbSource is the current xlsm file we are processing, we expect the underscore in this filename.

pos = InStr(wbSource.Name, "_")

The pos variable is not a file count, it holds the position of the underscore character within source workbook file name, i.e, in your example the underscore was the 13th character in the file name.

pos = InStr(wbSource.Name, "_")

This line builds the new file name from everything to the right of the position, e.g., 13th, character.

      newFileName = Right(wbSource.Name, Len(wbSource.Name) - pos)

Your debugging indicates this is working as expected.

When you add the new folder there are two backslash characters at the beginning of the string. Try placing the full path, including drive letter.

newFileName = "\\110.90.1.215\apps\UPLOAD\" & Replace(wbSource.Name, ".xlsm", ".csv")

For example

newFileName = "C:\110.90.1.215\apps\UPLOAD\" & Replace(wbSource.Name, ".xlsm", ".csv")

A common mistake when testing code like this is not deleting any files created by the code before testing again. For example, are the files created by this run or left over from a previous one?

 

[Dazzawm]

I am a pest! The folder where it is to be saved is on a network and I don't know if that makes a difference, so I tried on a folder on my desktop and it still saved it with the underscore and full name?

When I test it again I do it with all new files (deleting previous test files).

 

[bertie]

OK, I think I see it in this line:

newFileName = "\\110.90.1.215\apps\UPLOAD\" & Replace(wbSource.Name, ".xlsm", ".csv")

In the code at my end this is the new file name.

newFileName = "\\110.90.1.215\apps\UPLOAD\" & Replace(newFileName, ".xlsm", ".csv")

 

[Dazzawm]

Thats cracked it thanks Bertie.