Rod sends in today's question. He has to export several tab-delimited text files for a third-party application. He has the data for each file on a separate worksheet in a workbook and would like a macro to save those worksheets as individual text files. In Episode 894, I record a simple macro, then edit it to solve Rod's problem.
Transcript of the video:
Alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
We're going to talk about a quick Macro today.
You know, I want to point out...
I know a lot of you discovered the podcast through iTunes-- you've never been to MrExcel.
At MrExcel, we do have a whole series of great books in CD.
So if you're ever looking for an Excel book, we have books on Macros; we have books on Pivot Tables; we have books on Charting; and just regular books on Excel.
So Christmas is coming, make your wish list.
Now, Rod sent in today's question.
Rod has a series of different worksheet tabs; he needs to save each worksheet tab as a different tab delimited file.
These are all input into some third party system, and he has everything set up properly, but he'd like a Macro that could go through and save all of those tabs for him automatically.
Now, I'm going to make an assumption that he has put the tab names to reflect the file names, and we're going to record a quick Macro here.
Tools, Macro, Record New Macro, and we'll call it SaveAll, stored it in this workbook, click OK.
And we're going to do one thing in the Macro-- we're going to do File, Save As, and save it as a Tab delimited file-- Text (Tab delimited).
And I'm going to use a name like January, which happens to match the sheet name.
Click Save, okay, click OK, and then stop recording.
Now, we're going to go take a look at that Macro.
We're going to add a few lines of code.
Edit; I'm going to wrap that Macro in a loop; I'm going to say, For each ws Activeworkbook.worksheets.
And then at the end, put Next ws.
And what that's going to do is, it's going to force Excel to run that one line macro again, and again, and again; once for every sheet that happens to be there.
So what we can do is, ws.select-- that will select the individual worksheet.
Now, the one thing that we're going to have to be careful with here, is that we hard-coded, basically, in the Macro, that we're going to use a file name of January.txt.
So we're going to build our own file name here-- I'll call it ThisFN = and i'll use the path that we used before, but I'll concatenate the name of the worksheet, " & WS.name & ".txt.
And then, where the Macro had the file name hard-coded, I'm going to take that out and put my variable instead.
So, ThisFN, FileFormat: = xlText, create backup false.
Now, when I try to save, it warned me, and I'm wondering if it's going to warn every time that I try and save these sheets, that I'm going to be losing features-- since that's possible.
Let's just do, at the beginning of the Macro, Application.DisplayAlerts = False.
And that will prevent Excel from warning us, "Hey, you're about to lose information as we save this worksheet," twelve times.
So we'll come back here, Alt+F8, Save All, click Run.
You can see, very quickly, it goes through, selects each worksheet, saves them all.
And if I go out, take a look at my desktop, we should see right here on the right hand side, all 12 files created in just a few seconds.
So there's a great example: We used the Macro recorder to record the first one; added just a few lines of code; put it in a loop; customized the file save as name; and now, very quickly, we can create, in this case, 12 files.
In Rod's case it could be, you know, dozens of files-- hard to say.
So I want to thank you for stopping by, we'll see you next time.
Now, tomorrow, tomorrow is the 10th Anniversary of MrExcel.com.
Don't forget our webinar tomorrow, if you can go to MrExcel.com/webinar.html.
And we'll see you next time, thanks for stopping by.
We're going to talk about a quick Macro today.
You know, I want to point out...
I know a lot of you discovered the podcast through iTunes-- you've never been to MrExcel.
At MrExcel, we do have a whole series of great books in CD.
So if you're ever looking for an Excel book, we have books on Macros; we have books on Pivot Tables; we have books on Charting; and just regular books on Excel.
So Christmas is coming, make your wish list.
Now, Rod sent in today's question.
Rod has a series of different worksheet tabs; he needs to save each worksheet tab as a different tab delimited file.
These are all input into some third party system, and he has everything set up properly, but he'd like a Macro that could go through and save all of those tabs for him automatically.
Now, I'm going to make an assumption that he has put the tab names to reflect the file names, and we're going to record a quick Macro here.
Tools, Macro, Record New Macro, and we'll call it SaveAll, stored it in this workbook, click OK.
And we're going to do one thing in the Macro-- we're going to do File, Save As, and save it as a Tab delimited file-- Text (Tab delimited).
And I'm going to use a name like January, which happens to match the sheet name.
Click Save, okay, click OK, and then stop recording.
Now, we're going to go take a look at that Macro.
We're going to add a few lines of code.
Edit; I'm going to wrap that Macro in a loop; I'm going to say, For each ws Activeworkbook.worksheets.
And then at the end, put Next ws.
And what that's going to do is, it's going to force Excel to run that one line macro again, and again, and again; once for every sheet that happens to be there.
So what we can do is, ws.select-- that will select the individual worksheet.
Now, the one thing that we're going to have to be careful with here, is that we hard-coded, basically, in the Macro, that we're going to use a file name of January.txt.
So we're going to build our own file name here-- I'll call it ThisFN = and i'll use the path that we used before, but I'll concatenate the name of the worksheet, " & WS.name & ".txt.
And then, where the Macro had the file name hard-coded, I'm going to take that out and put my variable instead.
So, ThisFN, FileFormat: = xlText, create backup false.
Now, when I try to save, it warned me, and I'm wondering if it's going to warn every time that I try and save these sheets, that I'm going to be losing features-- since that's possible.
Let's just do, at the beginning of the Macro, Application.DisplayAlerts = False.
And that will prevent Excel from warning us, "Hey, you're about to lose information as we save this worksheet," twelve times.
So we'll come back here, Alt+F8, Save All, click Run.
You can see, very quickly, it goes through, selects each worksheet, saves them all.
And if I go out, take a look at my desktop, we should see right here on the right hand side, all 12 files created in just a few seconds.
So there's a great example: We used the Macro recorder to record the first one; added just a few lines of code; put it in a loop; customized the file save as name; and now, very quickly, we can create, in this case, 12 files.
In Rod's case it could be, you know, dozens of files-- hard to say.
So I want to thank you for stopping by, we'll see you next time.
Now, tomorrow, tomorrow is the 10th Anniversary of MrExcel.com.
Don't forget our webinar tomorrow, if you can go to MrExcel.com/webinar.html.
And we'll see you next time, thanks for stopping by.
