Forum Discussion

Copper Contributor

Sep 30, 2019

Solved

Searching fo Excel macro to convert multiple TXT files to multiple Excel Workbooks ...

Hi, I wish to locate an Excel macro that will batch convert multiple TXT files to multiple Excel workbooks not single workbook like this: http://rondebruin.nl/win/s3/win007.htm Thanks for a...

Greg_Mouning
Oct 18, 2019
@Sergei Baklan

Alas I found what I needed from the following weblink:

https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html

Just needed to make a few minor changes to VBA: Convert CSV files to XLSX code for TXT files:
xCSVFile = Dir(xSPath & "*.csv")
changed to
xCSVFile = Dir(xSPath & "*.txt")

ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
changed to
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".txt", ".xlsx", vbTextCompare), xlOpenXMLWorkbook

-Greg

Greg_Mouning

Copper Contributor

Oct 01, 2019

EricStarker

Thank you Eric. I'm not very familiar with this new Microsoft Communities forum. 😞

SergeiBaklan

Diamond Contributor

Oct 02, 2019

Greg_Mouning

Greg, please check this thread https://stackoverflow.com/questions/31606394/convert-csv-to-xlsx-using-command-line if helps

Greg_Mouning
Copper Contributor
Oct 02, 2019
SergeiBaklan

Thanks for your reply Sergei! I will review this link and let you know if it resolves my inquiry.
Best,
Greg
- Greg_Mouning
  Copper Contributor
  Oct 18, 2019
  @Sergei Baklan
  
  Alas I found what I needed from the following weblink:
  
  https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html
  
  Just needed to make a few minor changes to VBA: Convert CSV files to XLSX code for TXT files:
  xCSVFile = Dir(xSPath & "*.csv")
  changed to
  xCSVFile = Dir(xSPath & "*.txt")
  
  ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
  changed to
  ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".txt", ".xlsx", vbTextCompare), xlOpenXMLWorkbook
  
  -Greg