April, 2023 - Excelgeek

How to Export Excel Data to Text Files in Excel

This post will explain how to export Excel data to text files in Excel using VBA code. This post will show you how to export one sheet to text files in Excel, which can be useful if you need to extract data from Excel and use it in another program that requires a text file format.

You can easily select the data you want to export, specify a file name and location for the text file, and then generate a text file that contains the data in the format you need.

Export one Single Worksheet to Text File

If you want to export a single Excel sheet to a text file, follow these steps:

Step1: Open the Excel workbook that contains the sheet you want to export.

Step2: Click on the sheet you want to export.

Step3: Click on the “File” tab in the ribbon menu.

Step4: Click on “Save As” in the left-hand menu.

Step5: In the “Save As” dialog box, choose the location where you want to save the text file.

Step6: In the “Save as type” dropdown menu, select “Text (Tab delimited) (*.txt)“.

How to Export Excel Data to Text Files in Excel1.png

Step7: Click on the “Save” button.

Export one Column or Selection to Text File with VBA Code

You can use VBA code to export a selection or one column to a text file in Excel. Here are the steps:

Step1: Press “Alt + F11” on your keyboard. This will open the Visual Basic Editor window.

Step2: In the Visual Basic Editor window, select “Insert” from the top menu and choose “Module” to create a new module.

Step3: Copy and paste the following VBA code into the new module. Save the workbook with the VBA code.

Sub ExportSelectionToTextFile_ExcelHow()
    Dim myFile As String
    Dim rng As Range
    
    'Get the range to be exported from the user
    On Error Resume Next
    Set rng = Application.InputBox("Please select the range to be exported:", Type:=8)
    On Error GoTo 0
    
    'If no range is selected, exit the sub
    If rng Is Nothing Then Exit Sub
    
    'Create a new workbook and copy the selected range to it
    Dim wb As Workbook
    Set wb = Workbooks.Add
    rng.Copy Destination:=wb.Sheets(1).Range("A1")
    
    'Get the file name and location for the text file
    myFile = Application.GetSaveAsFilename(InitialFileName:="ExportedData.txt", FileFilter:="Text Files (*.txt), *.txt")
    
    'If the user cancels the file selection, exit the sub
    If myFile = "False" Then Exit Sub
    
    'If the file name is not valid, display an error message and exit the sub
    If Len(myFile) = 0 Then
        MsgBox "Invalid file name. Please select a valid file name and location.", vbExclamation, "Export to Text File"
        Exit Sub
    End If
    
    'Save the new workbook as a text file and close it
    wb.SaveAs FileName:=myFile, FileFormat:=xlText, CreateBackup:=False
    wb.Close SaveChanges:=False
    
    'Display a success message
    MsgBox "Data has been exported to text file successfully!", vbInformation, "Export to Text File"
End Sub

Step4: Press “Alt + F8” to open the Macro dialog box. Select the ” ExportSelectionToTextFile_ExcelHow” macro from the list of macros and click “Run“.

How to Export Excel Data to Text Files in Excel vba 2.png

Step5: select the range to be exported

How to Export Excel Data to Text Files in Excel vba 3.png

Step6: select a file name and location for the text file to be exported.

How to Export Excel Data to Text Files in Excel vba 4.png

Step6: Click “Save” to export the selected range to the text file.

How to Export Excel Data to Text Files in Excel vba 5.png

The selected range should now be exported to a text file in the location that you specified.

Video: Export Excel Data to Text Files This

This video will demonstrate how to export Excel data to text files in Excel using VBA Code.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel

In accounting, statistics or data analysis work, copying worksheets from other workbooks to an existing workbook or to a new workbook is a simple and frequent thing that happens almost every day. Everyone has their own tips or tricks to get around it. This article will describe a common and convenient way to copy all worksheets or some worksheets from other different workbooks into a new one.

Copy Sheets from Other Workbooks to a New Workbook via Move or Copy Feature

Step1: Open the worksheet that contains the sheets you want to copy, and also open the workbook you want to copy those sheets to (if you have such a workbook).

Step2: Hold down the “Ctrl” key on your keyboard and select the worksheet you want to copy to the new workbook.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 1.png

If you want to copy all the worksheets, hold down the “Shift” key and select the first and the last worksheet, then all the worksheets are selected.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 2.png

Note that in a Mac workbook you should use the “Command” key instead of the “Ctrl” key.

Step3: Right-click on any of the selected sheets, select “Move or Copy” in the context menu.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 3.png

Step 4: In “Move or Copy” dialog box, click the drop-down list in “To book” to select a workbook you want to copy to.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 4.png

Step 5: Select a workbook you want to copy to. You can select an existing workbook or click “(new book)” to create a new workbook. Note that only the opened workbooks are listed in “To book” drop-down list.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 5.png

Step 6: Select an existing workbook, for example, select “Book 5”, check on “Create a copy“.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 6.png

Step 7: Click “OK” in “Move or Copy” dialog. The selected worksheets will be copied to the opened existing workbook “Book 5”.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 7.png

If you select “(New Book)” to copy the selected worksheet, a new workbook will be created and opened and the selected worksheet will be pasted.

How to Copy Multiple Sheets from Other Workbooks to a New Workbook in Excel 8.png

Step 8: Repeat above steps to copy sheets from the other workbooks to the destination workbook.

Conclusion

After reading this article, you will know how to select non-consecutive worksheets in a workbook by holding down the “Ctrl” key, and how to create a copy of these selected worksheets in an existing workbook or in a new workbook by using the “Move or Copy” feature. The “Move or Copy” feature helps us move worksheets inside or outside of a workbook in a convenient way and also provides the option to create a copy directly in a specific workbook.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel

If a sheet is hidden, it cannot be accessed or edited directly. By copying it to a new visible sheet, you can access and work with the data on that sheet. Copying a hidden sheet to a visible sheet can also help you create a backup of the hidden sheet. This ensures that you have a copy of the data in case the original sheet becomes corrupted or lost.

This article will show you a great way to copy a hidden worksheet to a new worksheet through VBA code without unhiding or accessing the hidden worksheet.

Copy a Hidden Sheet to a New Visible Sheet Through VBA Code

We may want to hide certain worksheets that contain confidential or sensitive information. If we are the owner of these worksheets, we can copy the hidden worksheet in the traditional way, unhide it and create a copy in the workbook. If we are not allowed to access it, we can still copy the hidden worksheet through VBA code and we can update the data on the new copy without editing the original data.

You can use the following steps to copy a hidden sheet to a new visible sheet using VBA code.

Step1: Open the Excel workbook that contains the hidden sheet and press “Alt+F11” to open the “Visual Basic Editor“. You can also click “Visual Basic” on the “Developer” tab in the ribbon.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 1.png

Step2: In the “Visual Basic Editor“, right-click on the name of the workbook in the “Project Explorer” and select “Insert > Module” to insert a new module.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 2.png

Step3: In the module, write the following code:

Sub CopyHiddenSheet()
    Sheets("HiddenSheet").Copy Before:=Sheets(1)
    Sheets(1).Visible = True
End Sub

In this example, “HiddenSheet” is the name of the hidden sheet that you want to copy. Change it to the name of the hidden sheet that you want to copy.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 3.png

Step4: Press “F5” or click the “Run” button to run the code.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 4.png

This will copy the hidden sheet to a new sheet before the first sheet in the workbook and make it visible.

In this step, if you already closed “Visual Basic Editor“, you can also click the “Macros” on the “Developer” tab in the ribbon to run the “Macro“.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 6.png

Click “Run” in the “Macro” dialog.

Copy a Hidden Sheet with a Different Name Through VBA Code

In this case the code “Sheets(1).Visible = True” works only for the copy of the first worksheet location. It does not work if the copy is saved in a specific location. If you want to copy the hidden worksheet to a specific location, change the “Before:=Sheets(1)” parameter to the desired worksheet location.

For example, if you want to copy the hidden worksheet to the end of the workbook, change it to “After:=Sheets(Sheet.Count)“. If you want to create a copy after the hidden sheet, change it to “After:=Sheets(“HiddenSheet”)“. This operation will only copy the sheet to a specific location, but will not set it to be visible.

If you want to copy the hidden sheet with a different name, you can write the following code.

Sub CopyHiddenSheet()
    Sheets("HiddenSheet").Copy Before:=Sheets(1)
Sheets(1).Visible = True
    Sheets(1).Name = "New Sheet Name"
End Sub

Here, for the “New Sheet Name“, change it to the name you want to name the copy hidden sheet.

How to Copy a Hidden Sheet to a New Visible Sheet in Excel 8.png

Conclusion

After reading this post, you have gained the skills to copy hidden worksheets to visible worksheets by editing VBA code. With Visual Basic editor, you can see all visible and invisible worksheets. By editing the provided code, you can copy the worksheet you want to copy to any specific location. You can also rename the copied worksheet correctly with the VBA code.