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

Highlighted

@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

Highlighted
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
rusty on ms excel formulas, please help...
saj1011 in Excel on
0 Replies
Help with Formula
Machala Sentance in Excel on
1 Replies
problems creating a diagram in excel
Jagob in Excel on
1 Replies
SharePoint 2016 / sharepoint online
Share24x7 in SharePoint on
0 Replies
Generate Invoice Pricing - Multiple Criteria
TarrahLA in Excel on
1 Replies
Convert text in a cell to number
kaibisnes in Excel on
2 Replies