VBA code save as excel instead of txt

[spill-the-beans]

Hello,

I'm trying to make some code that will let me select the active workbook (which is a txt file) and save the first worksheet page (the only page with data in that workbook) as an excel file, then close the file.

Would it be possible to code that so that no file names are specified? Seeing as I want the excel file the same name as whatever the worksheet is called. That way, I can just run the code on all open workbooks instead of having to click through them all. If anyone has any idea that could help, I'd really appreciate your advice.

 

[pelizdr]

well, Spill, I assume you are looking at a specific directory for the file name, all you should need to do is scan the folder for files ending in text, capture the file name, then in VBA open the file and do a SaveAs with the file name and extension, for example:

Excel.Application.Workbooks.Open ("c:\" & filename.txt & ")
Excel.Application.ActiveSheet.Name = filename
excel.Activeworkbook.SaveAs "c:\" & filename & ".xlsx"
Excel.Application.Quit

If you have multiple files in the same directory, you can wrap it in a For Each loop with the code above and it would open each text file and save as xlsx.

 

[spill-the-beans]

That sounds like a perfect solution. Sorry to be cheeky, but could you tell me how I should write the For Each loop in the code? I've not written them myself before.

 

[spill-the-beans]

I've just tried the code in the mean time, and realised that it would open the text files. Is there a way that I can just save the active workbooks with the loop? I would already have them open in excel. All I need to do is save the open text files as excel files, not all the text files in the directory. This is what I've got to work at the moment.

Sub SaveTxtFiles()


Dim Filename As String


Application.DisplayAlerts = False


Filename = ActiveSheet.Name
ActiveWorkbook.SaveAs "S:\Documents\DataFiles\Scenes\Adults\" & Filename & ".xlsx"


ActiveWorkbook.Close


Application.DisplayAlerts = True
End Sub