I disagree that this answer is promoting bad coding practice. Using late binding like this is perfectly acceptable and is useful where you don't know what version of the Microsoft Scripting runtime your user has on their machine.
Show 2 more comments. Open ThisWorkbook. Path property. Bhanu Sinha Bhanu Sinha 1, 10 10 silver badges 9 9 bronze badges.
Pleas write the answer with some explanations and details. I prefer this method to the FSO method because it doesn't require external references and is quite short. Although I do suggest using FreeFile to get the file number instead of hardcoding it as 1. I prefer this approach as well for mundane text file writing. Regarding comment from phrebh about using FreeFile instead of a hardcoded 1 see wellsr.
Show 1 more comment. To elaborate on Ben's answer : If you add a reference to Microsoft Scripting Runtime and correctly type the variable fso you can take advantage of autocompletion Intellisense and discover the other great features of FileSystemObject. WriteLine "something" ' Close it, so it is not locked anymore fileStream. The file was created! Marcus Mangelsdorf Marcus Mangelsdorf 2, 1 1 gold badge 29 29 silver badges 37 37 bronze badges.
Thanks for writing a complete answer with helpful comments to code. I'm more than happy if you learned something from my post! This is critical for any code being redistributed — GregV. Write "your string goes here" Fileout. Is it possible to use the file chooser to set the path? Microsoft Office Development with Visual Studio.
Contributions by Lori B. Turner, Microsoft Corporation. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. If you want to eliminate that, simply comment the following two lines out:. We're still not done, because every time we run the procedure, the file is replaced without giving us the option of saving it under a new name.
We need to add code to create a unique filename every time we run the procedure. By declaring a new variable, SaveAsName, and then assigning a formatted version of the Now function, we can create a unique name for the file every time it is saved. Add these two lines after. Every time you run the procedure now, a new file with a unique name will be saved. Following these instructions will enable you to automatically copy data from Excel into Word.
Selecting data in Excel is not a new concept, and neither is copying. Saving it in a Word document with Excel VBA requires making use of the Environ function, as well as formatting of the filename. This will ensure that a new file is created every time the Excel VBA procedure is executed.
To copy an entire Excel spreadsheet and paste it into Word doesn't make sense. You could rather have done everything in Word from the beginning, saving you the whole effort of copying from Excel and pasting in Word. The point is, if you have an existing Word document or even a Word template, and you regularly need to export selected information from a spreadsheet in Excel to this template, this is the way to do it. The word automation acquires new meaning if you can link Excel with Word in this manner.
In this recipe, we will be pasting Excel data into a Word document at a specific bookmark. Open the Word document that was saved to the desktop. Insert a bookmark in the first open line after this sentence. Click on Insert Bookmark. The Bookmark dialog box will appear. Enter a name in the Bookmark name textbox, and then click on Add to close the dialog box:. Click on File Save As , and save the file as a Word template. The file extension is. Call the file EmployeeReportTemplate.
Depending on the operating system and the version of MS Office on your PC, the file will be saved in a specific folder, dedicated to template files:. Add the following line of text in the code window to open a specific file, and not just a blank document:.
Because we're making use of a template, we do not need any of the formatting we did for the previous document. I want to be able to run a macro in a Word document that will create an Excel document and then save that spreadsheet in a shared folder.
Anything which "belongs" to Excel must be prefixed with your objExcel application reference. Below is a simple and straight forward code that creates a new excel workbook and saves it into a path on your local disk.
In your case, the following would have worked because 'objDoc' is derived from the excel object reference. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 7 years, 10 months ago. Active 5 months ago. Viewed 9k times.
0コメント