How to Assign Recorded Macro to Command Button in Excel

How to record a macro to import a text file and attach a command button to the macro

  • Click on the Developer tab
  • From the command group Code click on record macro icon
  • A ‘Record Macro’ window pops up
  • Under the title ‘Macro Name’ we give the name ‘Import_Data’
  • Under the header ‘Description’ we write ‘Imports data from a text file’
  • We can also assign a shortcut key combination which can run the macro from the keyboard
  • Under the title ‘Store macro in:’ we leave the default value to ‘This Workbook’. The macro can now run from any worksheet of the workbook
  • Click on the OK button to close the ‘Record Macro’ window
  • Now click on the Data tab
  • Click on ‘Get External Data’
  • From the options offered click on ‘From Text’ to open a new window ‘Import text file’
  • Select the relevant file from the relevant folder
  • Click on import
  • A window called ‘Import Wizard Step 1 of 3’ pops up
  • Check the option ‘Delimited’ under ‘Choose the file type that best describes your data:’
  • Click on the next button
  • In step 2 deselect the default checked value ‘Tab’ and select the check-box next to ‘Comma’ because our dlimiter is a comma
  • After viewing the result in the ‘Data Preview’ area click on the Next button
  • Finally click on the Finish button
  • In the new Import Data window that pops up click on ‘Properties…’
  • In the ‘External data Range Properties’ window select the check-box against ‘Refresh every’ and set the value to 10 minutes. Of course, you can take your own decision in the matter. Also select the check-box ‘Refresh data when opening the file’ and click on the OK button
  • We are brought back to our ‘Import Data’ window. Here we select ‘Existing worksheet’, define the cell where the import of the data will begin and click on the OK button
  • We notice that the complete data has been imported into our Excel worksheet and occupies the cells A4:B12
  • Now we stop the recording of the macro by clicking on the Developer tab and selecting ‘Stop recording’ from the command group Code
  • Next we click on the Insert icon and select the command button from the ‘Form controls’
  • We click and drag on our worksheet to place a command button
  • In a new window called ‘assign macro’ that pops up we click on ‘Import_Data’ and click on the OK button
  • We right-click on the command button, select ‘Edit Text’ from the menu and change the text from button1 to Import data
  • We click inside the worksheet to deselect the command button
  • We delete the data from the worksheet
  • Microsoft Excel displays a message ‘The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition to the range? If you click No, the query will retrieve new data to the worksheet the next time the query is refreshed.’ Since we wish the query to display data again, we click on the ‘No’ button
  • Next we click our command button and the complete data is again imported into our Excel worksheet from the text file
  • We open our text file, make a few changes and save it.
  • If we now click on our Import Data command button on the Excel worksheet our data is updated.

View the training video:

Further reading:
Assigning a recorded macro to a Command Button

One thought on “How to Assign Recorded Macro to Command Button in Excel”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.