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
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
Highlighted

@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
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies