General-purpose routine for writing a string to a text file
A number of the other routines here collect information into a string variable then display it as a messagebox.
That's handy for quickies, but what if you want a more permanent record of the info your macro has collected?
Write it to file! These two subroutines accept a filename and a string and writes the string out to the file, and then open the file for editing/viewing/printing in Notepad.
Sub WriteStringToFile(pFileName as String, pString as String) Dim intFileNum as Integer intFileNum = FreeFile ' change Output to Append if you want to add to an existing file ' rather than creating a new file each time Open pFilename For Output As intFileNum Print #intFileNum, pString Close intFileNum End Sub Sub SendFileToNotePad(pFileName as String) Dim lngReturn as Long lngReturn = Shell("NOTEPAD.EXE " & pFileName, vbNormalFocus) End Sub
So for example, you could do this:
Sub Test() ' Create a test string Dim strTestString as String strTestString = "Now is the time for, alas, poor Yorick, to Dunsinane woods to come. With gibberish like this." ' And a name for the file we're going to write Dim strTestFile as String strTestFile = "C:\TEMP\TESTFILE.TXT" ' be sure you have a C:\TEMP folder or it won't work ' Write it to a file Call WriteStringToFile(strTestFile, strTestString) ' and now let's see the file Call SendFileToNotePad(strTestFile) End Sub
See How do I use VBA code in PowerPoint? to learn how to use this example code.