Robert asks: Is there a fast way to take a 60-sheet Excel file and create a series of linked tables in Word? Today, using some code from Suat Ozgur, a macro to solve the problem.
Buy Suat's Javascript Book: Excel JavaScript UDFs Straight to the Point
Download the Macro: https://www.mrexcel.com/download-center/2019/06/macrototransfertoword.xlsm
Videos from MBAS:
Buy Suat's Javascript Book: Excel JavaScript UDFs Straight to the Point
Download the Macro: https://www.mrexcel.com/download-center/2019/06/macrototransfertoword.xlsm
Videos from MBAS:
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2284. Excel to Word with a VBA macro. Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. Today's question. I was at the Microsoft Business Application Summit in Atlanta this past weekend.
Robert was in my class and he said, "for every client he has, he has a workbook with anywhere from 40 to 60 Worksheets and he wants to present this to them in a Word document. Currently he copies a range on each worksheet, pasting to Word as a linked object and then potentially. Resizing to fit the width of the Word document.
The question is, is there some way to speed this up? I write Excel VBA all the time.
I'm not really versed in Word VBA, so I turned to Suat Ozgur from the MrExcel team and this is what we came up with. Came up with this macro. Keep this workbook open in the background, switch to the other Excel workbook. Each worksheet should have a named range called Report and if there's not a range called report, we skip that sheet. And Ctrl+Shift+W to transfer to Word. Right now you can download this workbook so you can try it yourself. It's down in the YouTube description down there.
And it should work with any workbook you have all right now Robert had 60 worksheets. I just have a few here, so we're going to select the range that we want to have copy to word and then back here on the formulas tab, go to Define Name. The really important: change from Workbook to This Sheet: Sheet1 and we'll call it report.
Make sure you spell it right.
It's not going to work. Report and click OK and then go into sheet to right now this page is really wide. Again. Define name and the scope is going to be Sheet2 and call it Report.
All right and then sheet 3.
This one's really tall, so we go all the way down to Row 130. Define Name. Name it Report and this is going to be scoped to Sheet3. Yeah, now I can see it's going to be really tedious to set this up with the 60 reports the first time, but once you get it set up how life should be pretty good. Now this one we want to skip - nothing on this one here. Let's take a sheet three and just make a copy of it. And of course that name should already exist. Now on sheet four and will change it just to prove that it worked.
Alright good alright, so let's give it a try.
OK, so we have this workbook open. We switched to the Workbook that we want to transfer to word with Switch Window.
And Control+Shift+W.
Hey alright I pause the video it took about 30 - 35 seconds. The longest part which is waiting for Word to open up. Once Word was open, though it was pretty fast, right? So here's the first page that looks good and then here this page is really wide so it made the table at narrower and now this one I'm not sure about this. I think it might have made the table wider. In this case, and this was the one that was really long. 112 pages or just goes on for pages and pages and pages. Does the Page break and then the last one I added where I put the 123. So hey, it works Alright well, hey, let's take a look at Suat's code here. So at the beginning it's a creates a new Word document and makes that application visible equals true and then for each sheet in active workbook dot worksheet.
So it works on the active workbook. We're going to activate the sheet. Usually you don't have to do this, but there's a bug in Office 365 Insider Fast right now.
And if there's no range called report, it skips that goes next sheet, otherwise selects that range, does a copy, and then with Doc with the Word Doc active window dot selection, it will insert a break if in fact it's not the first one we're pasting and then paste the Excel table, and then next sheet, and then finally down. Here we go back to the word doc for each table in docked tables, autofit behavior two which is fit the window. Next table, right? And then we get a little message box here. And finally activates the Word document. Alright, so download the workbook. Give this a try. You probably have things you have to do in Word after the fact. You had some nice cover pages and stuff like that, but hopefully by getting 40 to 60 pages created in Word real quick, it will save you a lot of time.
OK, my thanks to Suat Ozgur who is the author of this book? Excel JavaScript UDF's Straight to the Point.
The book available at the link up there in the "i". If you like what you see here, please subscribe and ring that bell.
Feel free to post any questions or comments in the comments down below the YouTube video also. Hey, for videos from the Microsoft Business Analytics summit, there's a link down in the YouTube description. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Robert was in my class and he said, "for every client he has, he has a workbook with anywhere from 40 to 60 Worksheets and he wants to present this to them in a Word document. Currently he copies a range on each worksheet, pasting to Word as a linked object and then potentially. Resizing to fit the width of the Word document.
The question is, is there some way to speed this up? I write Excel VBA all the time.
I'm not really versed in Word VBA, so I turned to Suat Ozgur from the MrExcel team and this is what we came up with. Came up with this macro. Keep this workbook open in the background, switch to the other Excel workbook. Each worksheet should have a named range called Report and if there's not a range called report, we skip that sheet. And Ctrl+Shift+W to transfer to Word. Right now you can download this workbook so you can try it yourself. It's down in the YouTube description down there.
And it should work with any workbook you have all right now Robert had 60 worksheets. I just have a few here, so we're going to select the range that we want to have copy to word and then back here on the formulas tab, go to Define Name. The really important: change from Workbook to This Sheet: Sheet1 and we'll call it report.
Make sure you spell it right.
It's not going to work. Report and click OK and then go into sheet to right now this page is really wide. Again. Define name and the scope is going to be Sheet2 and call it Report.
All right and then sheet 3.
This one's really tall, so we go all the way down to Row 130. Define Name. Name it Report and this is going to be scoped to Sheet3. Yeah, now I can see it's going to be really tedious to set this up with the 60 reports the first time, but once you get it set up how life should be pretty good. Now this one we want to skip - nothing on this one here. Let's take a sheet three and just make a copy of it. And of course that name should already exist. Now on sheet four and will change it just to prove that it worked.
Alright good alright, so let's give it a try.
OK, so we have this workbook open. We switched to the Workbook that we want to transfer to word with Switch Window.
And Control+Shift+W.
Hey alright I pause the video it took about 30 - 35 seconds. The longest part which is waiting for Word to open up. Once Word was open, though it was pretty fast, right? So here's the first page that looks good and then here this page is really wide so it made the table at narrower and now this one I'm not sure about this. I think it might have made the table wider. In this case, and this was the one that was really long. 112 pages or just goes on for pages and pages and pages. Does the Page break and then the last one I added where I put the 123. So hey, it works Alright well, hey, let's take a look at Suat's code here. So at the beginning it's a creates a new Word document and makes that application visible equals true and then for each sheet in active workbook dot worksheet.
So it works on the active workbook. We're going to activate the sheet. Usually you don't have to do this, but there's a bug in Office 365 Insider Fast right now.
And if there's no range called report, it skips that goes next sheet, otherwise selects that range, does a copy, and then with Doc with the Word Doc active window dot selection, it will insert a break if in fact it's not the first one we're pasting and then paste the Excel table, and then next sheet, and then finally down. Here we go back to the word doc for each table in docked tables, autofit behavior two which is fit the window. Next table, right? And then we get a little message box here. And finally activates the Word document. Alright, so download the workbook. Give this a try. You probably have things you have to do in Word after the fact. You had some nice cover pages and stuff like that, but hopefully by getting 40 to 60 pages created in Word real quick, it will save you a lot of time.
OK, my thanks to Suat Ozgur who is the author of this book? Excel JavaScript UDF's Straight to the Point.
The book available at the link up there in the "i". If you like what you see here, please subscribe and ring that bell.
Feel free to post any questions or comments in the comments down below the YouTube video also. Hey, for videos from the Microsoft Business Analytics summit, there's a link down in the YouTube description. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
