SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-883137%22%20slang%3D%22en-US%22%3ESearching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883137%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20wish%20to%20locate%20an%20Excel%20macro%20that%20will%20batch%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20workbooks%20not%20single%20workbook%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Frondebruin.nl%2Fwin%2Fs3%2Fwin007.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Frondebruin.nl%2Fwin%2Fs3%2Fwin007.htm%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20any%20assistance%2C%26nbsp%3B%3CBR%20%2F%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887328%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287851%22%20target%3D%22_blank%22%3E%40Greg_Mouning%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello!%20You've%20posted%20your%20question%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%20Excel%20space%20-%20please%20post%20Excel%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887617%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41707%22%20target%3D%22_blank%22%3E%40Eric%20Starker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Eric.%20I'm%20not%20very%20familiar%20with%20this%20new%20Microsoft%20Communities%20forum.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888448%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287851%22%20target%3D%22_blank%22%3E%40Greg_Mouning%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreg%2C%20please%20check%20this%20thread%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F31606394%2Fconvert-csv-to-xlsx-using-command-line%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F31606394%2Fconvert-csv-to-xlsx-using-command-line%3C%2FA%3E%20if%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888787%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply%20Sergei!%20I%20will%20review%20this%20link%20and%20let%20you%20know%20if%20it%20resolves%20my%20inquiry.%3CBR%20%2F%3EBest%2C%3CBR%20%2F%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-918755%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20fo%20Excel%20macro%20to%20convert%20multiple%20TXT%20files%20to%20multiple%20Excel%20Workbooks%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlas%20I%20found%20what%20I%20needed%20from%20the%20following%20weblink%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA%3A%20Convert%20CSV%20files%20to%20XLSX%22%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4615-excel-batch-convert-csv-to-xls-xlsx.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4615-excel-batch-convert-csv-to-xls-xlsx.html%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20needed%20to%20make%20a%20few%20minor%20changes%20to%20%3CSTRONG%3EVBA%3A%20Convert%20CSV%20files%20to%20XLSX%20%3C%2FSTRONG%3Ecode%20for%20TXT%20files%3A%3C%2FP%3E%3CP%3ExCSVFile%20%3D%20Dir(xSPath%20%26amp%3B%20%22*.csv%22)%3C%2FP%3E%3CP%3E%3CSTRONG%3Echanged%20to%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ExCSVFile%20%3D%20Dir(xSPath%20%26amp%3B%20%22*.txt%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveWorkbook.SaveAs%20Replace(xSPath%20%26amp%3B%20xCSVFile%2C%20%22.csv%22%2C%20%22.xls%22%2C%20vbTextCompare)%2C%20xlWorkbookDefault%3C%2FP%3E%3CP%3E%3CSTRONG%3Echanged%20to%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EActiveWorkbook.SaveAs%20Replace(xSPath%20%26amp%3B%20xCSVFile%2C%20%22.txt%22%2C%20%22.xlsx%22%2C%20vbTextCompare)%2C%20xlOpenXMLWorkbook%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E-Greg%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Greg_Mouning
New Contributor

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 any assistance, 
Greg

5 Replies

@Greg_Mouning 

Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

@Eric Starker 

 

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

@Sergei Baklan 

 

Thanks for your reply Sergei! I will review this link and let you know if it resolves my inquiry.
Best,
Greg

Solution

@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

Related Conversations
Sharing an excel sheet public link
shunde3 in Microsoft Teams on
6 Replies
Importing a spreadsheet into a SP Online list
Jlee_Prosci in SharePoint on
2 Replies
Workbook change log info
Dean Gross in Azure Sentinel on
1 Replies
Excel Table Size in Macro
WayneEK in Excel on
6 Replies