VBA to save all .CSV files in a folder as .XLSM not working?

Good morning,

I've included my VBA code below which I am running to attempt to programmatically navigate to a folder and then open all .CSV files (and also ignoring all non-.CSV files) and perform a Save As function so that the files are saved as .XLSM. I have hundreds of individual .CSV files in this folder to convert to .XLSM files (and receive additional files on a daily basis that must also be converted) and if properly executed, it will save me a ton of time and will mitigate errors caused by manually performing this operation.

I can't seem to figure out the problem here. I've stepped through the code and I can clearly see each of my .CSV files in the targeted folder being opened and then properly closed, but the targeted files remain .CSV files instead of being changed to .XLSM files. I do not see any error messages when I run this code.

Any assistance is greatly appreciated!

Option Explicit
Sub ConvertToXLSM()
'
' Uses code from John Walkenbach's Power Programming book
'
Dim i As Long
Dim NumFiles As Long
Dim FileName As String
Dim FileNames() As String

' Get name of first file in backlog directory
FileName = Dir(ThisWorkbook.Path & "/*.csv")

NumFiles = 1
ReDim Preserve FileNames(1 To NumFiles)
FileNames(NumFiles) = FileName

' Get other file names, if any
Do While FileName <> ""
FileName = Dir()
If FileName <> "" Then
NumFiles = NumFiles + 1
ReDim Preserve FileNames(1 To NumFiles)
FileNames(NumFiles) = FileName
End If
Loop

' Save each file as a .xlsm file, overwriting any existing .xlsm Files
Application.DisplayAlerts = False
For i = 1 To UBound(FileNames)
If FileNames(i) <> ThisWorkbook.Name Then
Workbooks.Open FileName:=ThisWorkbook.Path & "\" & FileNames(i)
ActiveWorkbook.SaveAs FileName:=Left(FileNames(i), Len(FileNames(i)) - 4) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close
End If
Next i
Application.DisplayAlerts = True

End Sub

Hi,

firstly, you need to edit your post to add [ CODE ] tags around the code.

Please Login or Register  to view this content.

Secondly, when you're stepping through the code, what happens when this line is executed:

Please Login or Register  to view this content.

Try commenting out the line

Please Login or Register  to view this content.

and check what's happening on save.

Ahhh... great idea to un-Comment the DisplayAlerts… it says that the .XLSM file already exists and offers a Yes/No/Cancel dialogue box (screen shot attached) to choose whether or not to over-write. Out of curiosity, I chose 'Yes', and the script completed. However, the targeted files were NOT changed from .CSV to .XLSM? I looked in neighboring folders and do not see these newly-updated .XLSM files - this is confusing. It appears to be 'working', but the 'Save As' process is not completing even if I choose 'Yes'. Also, why does it think that the .XLSM file already exists when it does not? There is a .CSV file that I'm looking to open and then Save As an .XLSM file and that .XLSM vXLSMSaveAsErrorMsg_Jan19.pngersion definitely does not already exist. Thank you again!

Originally Posted by patdools

However, the targeted files were NOT changed from .CSV to .XLSM? I looked in neighboring folders and do not see these newly-updated .XLSM files - this is confusing.

Look in your standard Documents folder for the missing files.

Sometimes "ThisWorkbook.Path" can fail or get confused. (especially if you're using it with a workbook on a network drive).

Excel will then dump the new files it makes in the 'default' folder.

Thank you CK76 and PaulSP8 - fantastic guidance! The documents were in fact being written to my Documents folder, so I stopped using 'ThisWorkbook.Path' and inserted the path of the target directory itself. Also, using the Workbook object instead of 'ActiveWorkbook.SaveAs' was another key change to my code that helped complete the solution. I'm even reading the .CSV files from one directory and writing the copies of the .XLSM files to another, which keeps everything cleaner for my purposes. Thank you again!

Glad we could help
And thanks for the update.

If your issue is resolved. Please mark the thread as solved, using thread tools found at top of your initial post.