👉 If want to write all the Excel data back to a text file, check out this article on how to write data to a text file using a VBA macro.Ĭopy Multiple Lines of data from Text file to Excel 👉 Open a Word document from Excel and copy entire Excel worksheet to the Word doc with the click of a button using VBA. Next, I am running a for loop to read each word (commas, full stops etc.) in the array and write it each cell in my Excel worksheet. Using the object’s properties, I am reading the contents of the file (the first line only) and populating an array with the data. I am using the OpenTextFile() method to open the file and then initialized a “Textstream” object (objTS). ReadLine ' Copy data in a string variable.ĪrrData = Split(sData, ' ') ' Split the string and populate the array.įor iColCnt = 0 To UBound(arrData) ' Loop through the array and copy each word in a cell. AtEndOfLine ' Read data till the end of the line. OpenTextFile(sFolder & '/my-test-file.txt') ' Open the file.ĭo While Not objTS.

Word copies the macro from the left Macros panel to the right Quick Access Toolbar panel.Dim objFso As New FileSystemObject ' Create FSO object.ĭim objTS As TextStream ' For TextStream object. On that screen, locate your macro, select/highlight it, then click Add. Click Button and the Word Options/Customize Quick Access Toolbar screen opens. Step 2: Add a macro button to the Quick Access ToolbarĪ. Even though you can overwrite many of these shortcuts, it’s much easier to attach your macro to a button. Note: Most of the shortcut keys are already used by the system. Next, in the Assign Macro To panel, click Button or Keyboard (shortcut) for the method used to access and run the macro. For the Store Macro In field, choose All Documents to run this macro in all of your Word documents, or select the current document (displayed by filename) to use this macro in the current document only.

Descriptions are just notes that summarize the macro’s function.Ĭ.Macro names cannot conflict with the program’s reserved commands or keywords such as Print, Save, Copy, Paste.Macro names can be 80 characters maximum.You cannot use spaces, non-alphanumeric characters, or periods. Macro names must begin with a letter and use either letters or numerals.

Copy word table to excel using macro