How to Save or Export Excel Chart as Picture

Exporting an Excel chart as an image can be useful when you want to share the chart with others who do not have access to the Excel file or when you want to use the chart in other applications, such as Microsoft Word or PowerPoint.

There are several ways to save or export an Excel chart as an image, including using the “Save as Picture” option in Excel and using VBA code. The “Save as Picture” option allows you to choose the format and size of the image, while VBA code can automate the process and provide more flexibility in customizing the image.

In this article, we will explore these methods in more detail and provide step-by-step instructions on how to save or export an Excel chart as an image.

Save or Export Excel Chart as Picture using Save As Picture Feature

You can use the “Save as Picture” option in Excel to save or export an Excel chart as an image. Here are the steps to follow:

Step1: select the chart that you want to save or export.

Step2: right-click on the chart and select “Save As Picture” from the drop-down menu list. The Save As Picture dialog box will open.

How to Save or Export Excel Chart as Picture1.png

Step3: type one File name for saved picture, and select type as PNG, JPEG or others.

How to Save or Export Excel Chart as Picture3.png

Step4: The selected chart will be saved or exported as an image file in the selected type. You can then use the image file in other applications.

If you are working in an older Excel version, and you can copy the chart firstly.

How to Save or Export Excel Chart as Picture4.png

Then open Paint tool and paste the chart by clicking the Paste icon or just pressing Ctrl +V.

How to Save or Export Excel Chart as Picture5.png

Last, you need to click the Save As button to save the current image file.

Save or Export Excel Chart as Picture with VBA Code

The below VBA code can also help you to save or export an Excel chart as a picture in your current worksheet. You just need to follow these steps:

Step1: Open the Excel workbook that contains the chart you want to save as an image. Then press ALT + F11 to open the Microsoft Visual Basic Editor.

Save or Export Excel Chart as Picture with VBA Code

Step2: Insert a new module by clicking on “Insert” from the menu and then selecting “Module.”

Save or Export Excel Chart as Picture with VBA Code

Step3: Copy and paste the below VBA code into the new module (Module1).

How to Save or Export Excel Chart as Picture vba 1.png
Sub ExportSelectedChartAsPicture_excelhow()
    Dim chartObj As ChartObject
    Dim filePath As String
    
    'Prompt user to enter file name
    filePath = Application.GetSaveAsFilename(InitialFileName:="Chart", FileFilter:="PNG Files (*.png), *.png")
    
    'Check if a chart is selected
    On Error Resume Next
    Set chartObj = ActiveChart.Parent
    If chartObj Is Nothing Then
        MsgBox "Please select a chart to export.", vbExclamation, "No chart selected"
        Exit Sub
    End If
    
    'Export chart as PNG file
    chartObj.Chart.Export Filename:=filePath, FilterName:="PNG"
End Sub

Step4: Save the VBA code by pressing CTRL + S. and click Yes button.

How to Save or Export Excel Chart as Picture vba 2.png

Step5: Close the Visual Basic Editor and return to the Excel workbook.

Step6: Select the chart that you want to save as an image.

How to Save or Export Excel Chart as Picture vba 3.png

Step7: Press ALT + F8 to open the Macro dialog box or click on Macros command under Code group. Then select the macro named “ExportSelectedChartAsPicture_excelhow” from the list of macros and click on “Run.”

How to Save or Export Excel Chart as Picture vba 4.png

Step8: Enter a file name and select the desired file format in the “Save As” dialog box. Click on “Save” to save the chart as an image file.

How to Save or Export Excel Chart as Picture vba 5.png

Save All Charts in an Excel Workbook as Image

You can use VBA code to save all charts in an Excel workbook as images. Here’s an example code:

save all chart as image in workbook2.png
Sub ExportAllChartsAsPictures_excelhow()
    Dim sheet As Worksheet
    Dim chartObj As ChartObject
    Dim filePath As String
    
    'Prompt user to enter file name
    filePath = Application.GetSaveAsFilename(InitialFileName:="Charts", FileFilter:="PNG Files (*.png), *.png")
    
    'Loop through each worksheet
    For Each sheet In ActiveWorkbook.Worksheets
        'Loop through each chart object in the sheet
        For Each chartObj In sheet.ChartObjects
            'Export chart as PNG file
            chartObj.Chart.Export Filename:=filePath & "_" & chartObj.Name & ".png", FilterName:="PNG"
        Next chartObj
    Next sheet
End Sub

You can use this VBA code to follow the above steps to run it.

save all chart as image in workbook1.png

You need to modify the file format by changing the “Filename” parameter.  The code will loop through each worksheet in the active workbook and then loops through each chart object in each worksheet.

How to Export All Images at once From Excel

This post will guide you on how to export all images at once from Excel. However, exporting these images can be time-consuming, especially if you have many images spread across different worksheets. This post you will learn how to export all images at once from Excel, saving you time and effort.

Export All Images at Once From Excel Worksheet

If you want to export all images at once from your active workbook in Excel, and you can do the following steps:

How to Export All Images at once From Excel1.png

Step1: Click on the “File” tab in Excel and select “Save As“.

How to Export All Images at once From Excel2.png

Step2: In the “Save As” dialog box, select the folder where you want to save the images. Choose “Web Page” from the “Save as type” dropdown menu.

How to Export All Images at once From Excel3.png

Step3: Check the box next to “Save:” and select “Entire Workbook” radio button, and click on the “Save” button.

How to Export All Images at once From Excel4.png

Step4: Navigate to the folder where you saved the file, you should see a folder named “xxxxx_files” or something similar. Open that folder.

How to Export All Images at once From Excel5.png

Step5: All of the images from the Excel file should be in that folder. You can then copy or move the images to another folder as needed.

How to Export All Images at once From Excel6.png

Export Images and Rename Them with the Adjacent Cell Values

If you want to export images and rename them with the adjacent cell values, and you can use VBA code to achieve the result, just do the following steps:

Step1: you can press Alt +F11 shortcut to open the Microsoft VBA editor.

Step2: In the Microsoft VBA editor, insert a new module by clicking “Insert” -> “Module” from the drop-down menu list.

Step3: copy and paste the following VBA code into the new module window.

export all image and rename file name vba 1.png
Sub ExportImages_excelgeek()
    Dim cell As Range
    Dim shp As Shape
    Dim chartObj As ChartObject
    Dim path As String
    Dim fileName As String
    Dim fd As FileDialog
    Dim xImg As Shape
    
    path = "C:\Users\Images\" 'Change the path to your desired location
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.AllowMultiSelect = False
    fd.Title = "Select a folder to save images"
    If fd.Show = True Then
        path = fd.SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
    
    For Each oneImg In ActiveSheet.Shapes
        If oneImg.Type = msoPicture Then
            fileName = oneImg.TopLeftCell.Offset(0, -1).Value & ".jpg" 'Change the offset to match the adjacent cell where the name is located
            If fileName <> "" Then
                oneImg.Select
                Selection.Copy
                 With ActiveSheet.ChartObjects.Add(0, 0, oneImg.Width, oneImg.Height)
                     .Activate
                     ActiveChart.Paste
                     .Chart.Export path & fileName
                     .Delete

                End With
            End If
        End If
    Next
End Sub

Step4: Modify the offset value to match the adjacent cell where the name is located.

Step5: Press “F5” to run the VBA code or press “Alt + F8” to open Macro dialog box, and select “ExportImages_excelgeek” Macro name, then click on Run button.

export all image and rename file name vba 2.png

Step6: select a folder to save images, click on Ok button.

export all image and rename file name vba 3.png

Step7: The code will loop through each image in the current worksheet, copy it, and export it as a JPEG file with the name from the adjacent cell.

How to Export All Images at once From Excel6.png

How to Import and Connect a Website in Excel

This post will guide you how to import and connect a website in Excel. Importing data from websites can be a time-saving way to gather information, and it can be especially useful when you need to update data on a regular basis. By following the steps below, you can quickly and easily import and connect website data to your Excel worksheet.

Read More: How to Import Data from Another Worksheet in Excel

Import and Connect a Website in Excel

If you want to import data from a website into your current worksheet, and you can use the From Web Feature to achieve it. just do the following steps:

Step1: Open a new Excel worksheet and click on the “Data” tab in the ribbon at the top of the screen. Click on the “From Web” button in the “Get & Transform Data” section. This will open the “From Web” dialog box.

How to Import And Connect a Website in Excel 1.png

Step2: In the “From Web” dialog box, enter the URL of the website you want to import data from and click “OK“. Excel will load the data from the website and display it in the “Navigator” dialog box.

How to Import And Connect a Website in Excel 2.png

Step3: In the “Navigator” dialog box, you can select the data you want to import by clicking on items. You can also preview the data by clicking on it.

How to Import And Connect a Website in Excel 3.png

Step4: Once you have selected the data you want to import, click on the “Load” button to import it into Excel. Excel will load the data into a new worksheet.

How to Import And Connect a Website in Excel 4.png

Step5: If you only want to connect to the website data so that it updates automatically, you can click on the “Load to” button in the “Queries & Connections” section of the “Data” tab. This will open the “Import Data” dialog box.

How to Import And Connect a Website in Excel 5.png

Step6: In the “Import Data“, click on the “Only Create Connection” button. Click on Ok button.

How to Import And Connect a Website in Excel 5.png

This will create a connection to the website data that updates automatically whenever you open the Excel file or refresh the data.

How to Import Data from Another Worksheet in Excel

This post will guide you on how to import data from another worksheet using the Connections function in Excel. You can easily import data from other worksheets in the same workbook or from external workbooks through Connections or VBA code in Excel.

Import Data from Another Worksheet with Connections Function

The Connections function in Excel allows you to import data from another worksheet, which can be useful when you need to consolidate data from multiple sources or keep your data organized in separate sheets.

Here’s how to use the Connections function to import data from another worksheet:

Step1: Open the workbook that contains the data you want to import.

Step2: Click on the worksheet where you want to import the data.

Step3: Click on the “Data” tab in the Excel ribbon. Click on “Existing Connections” in the “Get External Data” section of the ribbon.

How to Import Data from Another Worksheet in Excel 1.png

Step4: In the “ Existing Connections ” dialog box, click on “Browse for More…“.

How to Import Data from Another Worksheet in Excel 2.png

Step5: locate and select the Excel workbook that contains the data you want to import. Click on “Open” button in the “Select Data Source” dialog box.

How to Import Data from Another Worksheet in Excel 3.png

Step6: Choose the worksheet that contains the data you want to import, and then click “OK“.

How to Import Data from Another Worksheet in Excel 4.png

Step7: Choose how you want to view and organize the data in the imported worksheet. For example, choose Table option, and select one destination Cell to put the imported data.

How to Import Data from Another Worksheet in Excel 5.png

Step8: Click “OK” to create the connection and import the data.

How to Import Data from Another Worksheet in Excel 6.png

Import Data from Another Worksheet with VBA Code

You can also import data from another worksheet using VBA code in Excel, you can use the Application.FileDialog function to prompt the user to select the source file, and then use the Workbooks.Open and Range.Copy methods to copy the data to the destination worksheet. Just do the following steps:

Step1: Open a new or existing Excel workbook that you want to import data in.

Step2: Press “Alt + F11” shortcut to open the Microsoft VBA Editor.

Step3: Insert a new module by clicking “Insert” > “Module“.

Step4: Copy and paste the below code into the module window.

How to Import Data from Another Worksheet in Excel vba1.png
Sub ImportDataFromWorksheet_excelgeek()

    Dim MyFile As String
    Dim MyPath As String
    Dim MyWorkbook As Workbook
    Dim MySourceRange As Range
    Dim MyDestination As Range
    Set currentWorkbook = ActiveWorkbook

    ' Prompt the user to select the source file
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select the source file"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show

        If .SelectedItems.Count > 0 Then
            MyFile = .SelectedItems(1)
            MyPath = Left(MyFile, InStrRev(MyFile, "\"))
        Else
            MsgBox "No file selected.", vbExclamation, "Error"
            Exit Sub
        End If
    End With

    ' Open the source workbook 
    Set MyWorkbook = Workbooks.Open(MyFile)
    Set MySourceRange = Application.InputBox(prompt:="Select source range from source worksheet", Title:="Select Source Range", Default:="A1", Type:=8)
    currentWorkbook.Activate
    Set MyDestination = Application.InputBox(prompt:="Select one destination cell", Title:="Select Destination to put data", Default:="A1", Type:=8)
    MySourceRange.Copy MyDestination
    MyWorkbook.Close SaveChanges:=False
End Sub

Step5: In your current worksheet, press “Alt +F8” shortcut to open Macro window. Then select the Macro name “ImportDataFromWorksheet_excelgeek”, click Run button.

How to Import Data from Another Worksheet in Excel vba2.png

Step6: Select the source workbook that contains worksheets you want to import.  Click Open button.

How to Import Data from Another Worksheet in Excel vba3.png

Step7: choose one worksheet and then select source range from the current worksheet. For example: test3!$A$1:$G$11, click OK button.

How to Import Data from Another Worksheet in Excel vba4.png

Step8: select one destination cell in your worksheet. Click OK button.

How to Import Data from Another Worksheet in Excel vba5.png

Step9: You would see that the selected data has been imported into your worksheet.

How to Import Data from Another Worksheet in Excel vba6.png

Read More: Use VBA to Copy Data from Workbook without Opening

Conclusion

There are a few different methods to import data from another worksheet in Excel. One option is to use the Connections function, which allows you to link to data in another worksheet or workbook. Another option is to use VBA code.

How to Import Multiple File Names into Cells in Excel

This post will guide you how to import file names only into your current worksheet throught VBA code and Power Query. When working with large amounts of data, it is often useful to import only the file names, rather than the file contents. This can be achieved using either VBA code or Power Query.

Import Multiple File Names into Cells with Power Query

You can use Power Query to import file names into Excel. Here are the steps:

Step1: Open a new or existing Excel workbook. Click on the “Data” tab in the Excel ribbon. And click on the “Get Data” option in the “Get & Transform Data” section and choose “From File“, then choose “From Folder” in the dropdown menu.

How to Import Multiple File Names into Cells in Excel 1.png

Step2: Browse and select the folder that contains the files you want to import. Click Open button.

How to Import Multiple File Names into Cells in Excel 2.png

Step3: Click on the “Transform Data” button to open Power Query Editor.

How to Import Multiple File Names into Cells in Excel 3.png

Step4: In Power Query Editor, select the Name column and right click on it. then click on “Remove Other Columns”.

How to Import Multiple File Names into Cells in Excel 4.png

Step5: Once you have finished editing the data, click on the “Close & Load” button to import the data into a new worksheet.

How to Import Multiple File Names into Cells in Excel 5.png

Step6: you can see that the only file names would be imported successfully.

How to Import Multiple File Names into Cells in Excel 6.png

Import Multiple File Names into Cells with VBA Code

You can also use VBA code to import file names into Excel. Just do the following steps:

Step1: Open a new or existing Excel workbook.

Step2: Press “Alt + F11” shortcut to open the Microsoft VBA Editor.

How to Import Multiple File Names into Cells vba code 1.png

Step3: Insert a new module by clicking “Insert” > “Module“.

How to Import Multiple File Names into Cells vba code 2.png

Step4: Copy and paste the below code into the module window.

How to Import Multiple File Names into Cells vba code 3.png
Sub ImportFileNames_excelgeek()

    Dim MyFolder As String
    Dim MyFile As String
    Dim i As Integer

       ' Prompt the user to select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder that contains the files you want to import"
        .Show
        If .SelectedItems.Count > 0 Then
            MyFolder = .SelectedItems(1)
        Else
            MsgBox "No folder selected.", vbExclamation, "Error"
            Exit Sub
        End If
    End With

    ' Get the first file in the folder
    MyFile = Dir(MyFolder & "\*.*")

    ' Loop through all files in the folder
    Do While MyFile <> ""
        ' Increment the row counter
        i = i + 1
        ' Insert the file name in the cell
        Cells(i, 1).Value = MyFile
        ' Get the next file in the folder
        MyFile = Dir
    Loop
End Sub

Step5: Run the code by clicking “Run” > “Run Sub/UserForm” or by pressing “F5“.

How to Import Multiple File Names into Cells vba code 4.png

Step6: Select the folder that contains the files you want to import. Such as: mergeFolder.

How to Import Multiple File Names into Cells vba code 5.png

Step7: The code will loop through all the files in the specified folder and insert the file names in the first column of the active worksheet.

How to Import Multiple File Names into Cells vba code 6.png

Conclusion

Importing file names can be a useful way to manage large amounts of data in Excel. Whether you prefer to use VBA code or Power Query, both options provide a way to automate the process and import file names quickly and efficiently.

How to Insert Worksheets from Another Workbook in Excel

This post will introduce you to the process of inserting worksheets from another workbook in Microsoft Excel using the “Move or Copy” feature. This is a useful method when you need to consolidate data from multiple workbooks into a single workbook, or if you want to reuse existing worksheets across different workbooks.

Read More: Use VBA to Copy Data from Workbook without Opening

Insert Worksheets from Another Workbook

If you want to insert worksheets from another workbook in Microsoft Excel, just follow these steps:

Step1: Open the destination workbook where you want to insert the worksheets.

Step2: Click on the “Home” tab in the ribbon at the top of the Excel window.

Step3: Click on the “Insert” dropdown in the Cells group, and select “Insert Sheet“.

How to Insert Worksheets from Another Workbook in Excel 1.png

Step4: Click on the “File” tab in the ribbon at the top of the Excel window, Click on “Open” and navigate to the source workbook. And select the source workbook and click on “Open“.

How to Insert Worksheets from Another Workbook in Excel 2.png

Step5: In the source workbook, select the worksheet(s) that you want to insert. Right-click on the selected worksheet(s) and click on “Move or Copy“.

How to Insert Worksheets from Another Workbook in Excel 3.png

Step6: In the “Move or Copy” dialog box, select the destination workbook from the “To book” dropdown. And select the position where you want to insert the worksheet(s) in the destination workbook.

How to Insert Worksheets from Another Workbook in Excel 4.png

Step7: Select the “Create a copy” checkbox if you want to create a copy of the worksheet(s) rather than moving them.

How to Insert Worksheets from Another Workbook in Excel 5.png

Step8: Click “OK” to insert the worksheet(s) into the destination workbook.

How to Insert Worksheets from Another Workbook in Excel 6.png

Alternatively, you can also drag and drop the selected worksheet(s) from the source workbook to the destination workbook. To do this, open both workbooks side by side, select the worksheet(s) in the source workbook, drag and drop them to the desired location in the destination workbook.

How to Insert Worksheets from Another Workbook in Excel 7.png

Conclusion

Inserting worksheets from another workbook in Microsoft Excel can be a time-saving technique that helps you manage data efficiently. Whether you’re consolidating data from multiple sources or reusing existing worksheets across different workbooks, the “Move or Copy” feature can simplify the process. By following the steps outlined in this post, you can easily insert worksheets from one workbook into another, and keep your data organized and accessible.

How to Import Multiple Text Files to Multiple Sheets in Excel

This post will introduce you how to import multiple text files into multiple sheets in Excel. You can quickly and easily import multiple text files into separate sheets within a single workbook using VBA code. Let’s get started with the step-by-step instructions for importing multiple text files using VBA.

Import Multiple Text Files to Multiple Sheets with VBA Code

If you have a large number of text files that you need to import into Excel, it can be a time-consuming task to import them one by one. You can achieve it by using VBA code, just do the following steps:

To import multiple text files into multiple sheets with VBA code in Excel, you can follow these steps:

Step1: Open a new workbook in Excel and press ALT + F11 to open the Visual Basic Editor.

How to Import Multiple Text Files to Multiple Sheets1.png

Step2: In the Visual Basic Editor, insert a new module by clicking Insert > Module.

How to Import Multiple Text Files to Multiple Sheets2.png

Step3: Copy and paste the following VBA code into the new module:

How to Import Multiple Text Files to Multiple Sheets3.png
Sub ImportTextFiles_excelgeek()
    Dim FileNames As Variant
    Dim i As Integer
    FileNames = Application.GetOpenFilename(filefilter:="Text Files (*.txt),*.txt", MultiSelect:=True)
    If Not IsArray(FileNames) Then
        Exit Sub
    End If
    For i = LBound(FileNames) To UBound(FileNames)
        With ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
            .Name = Left(Right(FileNames(i), Len(FileNames(i)) - InStrRev(FileNames(i), "\")), Len(Right(FileNames(i), Len(FileNames(i)) - InStrRev(FileNames(i), "\"))) - 4)
            .QueryTables.Add Connection:="TEXT;" & FileNames(i), Destination:=.Range("A1")
            .QueryTables(1).TextFilePlatform = xlWindows
            .QueryTables(1).TextFileStartRow = 1
            .QueryTables(1).TextFileParseType = xlDelimited
            .QueryTables(1).TextFileTextQualifier = xlTextQualifierDoubleQuote
            .QueryTables(1).TextFileConsecutiveDelimiter = False
            .QueryTables(1).TextFileTabDelimiter = True
            .QueryTables(1).TextFileSemicolonDelimiter = False
            .QueryTables(1).TextFileCommaDelimiter = False
            .QueryTables(1).TextFileSpaceDelimiter = False
            .QueryTables(1).TextFileOtherDelimiter = ""
            .QueryTables(1).Refresh BackgroundQuery:=False
        End With
    Next i
End Sub

Read More: How to Use QueryTables in Excel VBA

Step4: Save the module and return to the Excel worksheet.

Step5: Press ALT + F8 to open the Macro dialog box and run the ” ImportTextFiles_excelgeek ” macro.

How to Import Multiple Text Files to Multiple Sheets4.png

Step6: In the Open dialog box that appears, select the text files you want to import and click the “Open” button.

How to Import Multiple Text Files to Multiple Sheets5.png

Step7: The VBA code will create a new sheet for each text file and import the contents of each file into its corresponding sheet.

How to Import Multiple Text Files to Multiple Sheets6.png

This VBA code makes it easy to import multiple text files into multiple sheets in Excel with just a few clicks.

Read More: Import or Merge Multiple CSV Files into one Excel Worksheet using Power Query

How To Import Multiple CSV Files into One Excel Workbook

This post will introduce three methods for importing multiple CSV files into one Excel workbook.

  • The first method involves using VBA code to automate the process of importing CSV files.
  • The second method involves using Power Query, a data connection technology that allows you to connect to and import data from various sources.
  • This third method can be used to easily combine multiple CSV files into one Excel worksheet using the command prompt.

Those methods can save time and effort when working with multiple CSV files in Excel.

Import or Merge Multiple CSV Files into Separate Worksheet Using VBA Code

If you try to import or merge multiple CSV files manually into separate worksheets, it should be time-consuming and tedious. You can use VBA code to automate the process to save your time and effort.

To import or merge multiple CSV files into separate worksheets using VBA code, just follow the steps below:

Step1: Open the Excel workbook where you want to import or merge the CSV files.

How to Import Multiple CSV Files into One Excel Workbook vba1.png

Step2: Press Alt+F11 to open the Visual Basic Editor.

How to Import Multiple CSV Files into One Excel Workbook vba2

Step3: In the Visual Basic Editor, select Insert > Module to create a new module.

How to Import Multiple CSV Files into One Excel Workbook vba3.png

Step4: Copy and paste the below VBA code into the new module.

Sub ImportCSVFiles_excelgeek ()
    Dim FolderPath As String, Filename As String
    Dim Sheet As Worksheet, NextRow As Long
    Dim FilePicker As FileDialog

    ' Show the File Picker dialog box to select the folder containing the CSV files
    Set FilePicker = Application.FileDialog(msoFileDialogFolderPicker)
    FilePicker.AllowMultiSelect = False
    FilePicker.Title = "Select the folder containing the CSV files"
    If FilePicker.Show <> -1 Then Exit Sub
        FolderPath = FilePicker.SelectedItems(1)
        ' Loop through all CSV files in the folder
        Filename = Dir(FolderPath & "\*.csv")
        Do While Filename <> ""
            ' Open the CSV file
            Workbooks.Open Filename:=FolderPath & "\" & Filename, ReadOnly:=True
            ' Copy the data to a new worksheet
            Set Sheet = ThisWorkbook.Worksheets.Add(After:= _
                  ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            Sheet.Name = Left(Filename, Len(Filename) - 4)
            Range("A1").Select
            Selection.CurrentRegion.Select
            Selection.Copy Sheet.Range("A1")
            Workbooks(Filename).Close
            ' Move to the next row on the new worksheet
            NextRow = Sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            ' Set the filename to the next file in the folder
            Filename = Dir
        Loop
End Sub

Step5: Save the module and return to the Excel workbook.

Step6: Press Alt+F8 to open the Macro dialog box, or click on the Macros command under Code group and select the macro you just created. Click Run to execute the macro.

How to Import Multiple CSV Files into One Excel Workbook vba5.png

Step7: You need to select one folder that containing the CSV files.

How to Import Multiple CSV Files into One Excel Workbook vba6.png

Step8: The macro will automatically import or merge the CSV files into separate worksheets in the same workbook.

How to Import Multiple CSV Files into One Excel Workbook vba7.png

This code will display the File Picker dialog box to select the folder containing the CSV files. It will then loop through all CSV files in the folder, open each file, and copy the data into a new worksheet in the same workbook. The name of each worksheet will match the name of the CSV file, without the “.csv” extension.

Import or Merge Multiple CSV Files into one Excel Worksheet using Command Prompt

If you want to import or merge multiple CSV files into one Excel worksheet, and you can achieve it by using the command prompt in Windows command line, just do the following steps:

Step1: Open the Command Prompt. On Windows, you can press the Windows key + R, type “cmd“, and press Enter.

How to Import Multiple CSV Files into One worksheet 1.png

Step2: Navigate to the folder where your CSV files are located using the “cd” command.

How to Import Multiple CSV Files into One worksheet 2.png

Step3: Type “copy *.csv mergedfiles.csv” command and press Enter.

How to Import Multiple CSV Files into One worksheet 3.png

This will create a new file named “ mergedfiles.csv ” that contains the data from all the CSV files in the folder.

Step4: Open Microsoft Excel and select “Open” from the “File” menu.

Step5: In the “Open” dialog box, navigate to the folder where your CSV files are located. And Change the file type to “All Files“. Select the “ mergedfiles.csv ” file and click “Open“.

How to Import Multiple CSV Files into One worksheet 4.png

Step6: In the Text Import Wizard, select “Delimited” as the file type and click “Next“.

How to Import Multiple CSV Files into One worksheet 5.png

Step7: Select “Comma” as the delimiter and click “Next“.

How to Import Multiple CSV Files into One worksheet 6.png

Step8: click on “Finish” command, the csv file would be imported into the current worksheet.

How to Import Multiple CSV Files into One worksheet 7.png

Import or Merge Multiple CSV Files into one Excel Worksheet using Power Query

You can import or merge multiple CSV files into one Excel worksheet using Power Query, just do the following steps:

Step1: Open Microsoft Excel and select “Data” Tab.

import multiple csv files into one worksheet using power query1.png

Step2: Click on “From File” in the “Get & Transform Data” section. And Select “From Folder” in the drop-down menu.

import multiple csv files into one worksheet using power query2.png

Step3: In the “From Folder” dialog box, navigate to the folder where your CSV files are located and click “Open“. The Power Query window will open.

import multiple csv files into one worksheet using power query3.png

Step4: select “Combine & Transform Data” from the Power Query window. And the Combine Files window will appear.

import multiple csv files into one worksheet using power query4.png

Step5: In the “Combine Files” dialog box, choose the Delimiter option as Comma based on your csv data and click “OK“.

import multiple csv files into one worksheet using power query5.png

Step6: In the Power Query Editor dialog box, select the columns you want to include in the merged table. You can now edit, filter, and transform the merged data as needed.

import multiple csv files into one worksheet using power query6.png

Step7: Click “Close & Load” to import the merged data into Excel.

import multiple csv files into one worksheet using power query7.png

Step8: This will merge the data from all the CSV files in the selected folder into one table in Excel using Power Query.

import multiple csv files into one worksheet using power query7.png

Conclusion

There are several ways to import or merge multiple CSV files into one Excel workbook. VBA code can be used to merge the files into one workbook, while using command prompt can be a quick and simple solution for those who prefer a command-line interface. On the other hand, using Power Query is a more user-friendly solution that allows for editing, filtering, and transforming the data, and can be particularly useful for large datasets.

How to Use QueryTables in Excel VBA

This post will introduce you to the concept of QueryTables in Excel VBA. QueryTables is an object that allow you to import data from external sources, such as databases, web pages, and text files, directly into your Excel workbook.

In this post, we will discuss how to create, refresh, and delete QueryTables using VBA code, and we’ll provide examples of how to use QueryTables with different types of data sources.

Creating a New QueryTable

If you want to create a new QueryTable, you can use the Add method of the QueryTables collection of a Worksheet object.

The Add method has several parameters that you can use to customize the QueryTable, such as the Connection parameter, which specifies the data source, and the Destination parameter, which specifies the range of cells where the data should be imported.

Here’s an example of how to create a new QueryTable in VBA:

Sub ImportData_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
End Sub
How to use QueryTables in Excel VBA 1.png

In this example, the Connection parameter specifies an ODBC connection to a database, and the Destination parameter specifies that the imported data should be placed in cell A1 of the current worksheet.

Specifying Data Source

The Connection parameter is used to specify the data source for the QueryTable. The syntax of the Connection parameter depends on the type of data source you are using.

If you want to import data from a web page, you can use the following connection parameter:

Connection:="URL;http://www.excelgeek.net/mycontent.html"

If you want to import data from a text file, you can use the following Connection syntax:

Connection:="TEXT;C:\Folder\MyTextFile.txt"

To import data from a database, you can use the following syntax:

Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password"

In this example, the ODBC connection uses the Data Source Name (DSN) ” DatabaseName “, the username ” Username “, and the password ” Password “.

Refreshing the QueryTable

Once you have created a QueryTable, you can refresh it by calling the Refresh method of the QueryTable object.

Here’s an example of how to refresh a QueryTable:

Sub RefreshData_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
    myquerytable.Refresh
End Sub
How to use QueryTables in Excel VBA 2.png

In this example, the RefreshData_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Refresh method of the QueryTable to refresh the data.

Cleaning up QueryTable

After you have finished using a QueryTable, you should clean it up by calling the Delete method of the QueryTable object.

Here’s an example of how to delete a QueryTable:

Sub Cleanup_Data_excelgeek()
    Dim myquerytable  As QueryTable
    Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
    myquerytable.Refresh
    myquerytable.Delete
End Sub

In this example, the Cleanup_Data_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Delete method of the QueryTable to delete it.

How to use QueryTables in Excel VBA 3.png

How to Import Text File or CSV File into Excel

This post will introduce you to the process of importing a text file or CSV (Comma Separated Values) file into Excel. If you’re working with a large dataset, importing the data into Excel can save you time and effort compared to manually entering the data.

 Excel provides several tools and methods for importing data from external sources, including text files and CSV files. In this article, we’ll show you how to use these tools and provide step-by-step instructions for importing a text file or CSV file into Excel.

Import Text File or CSV File into Excel Using From Text/CSV Feature

Here are the detailed step-by-step instructions for importing a text file or CSV file into Excel:

How to Import Text File or CSV File into Excel5.png

Step1: Open a new or existing Excel workbook.

Step2: Go to the “Data” tab on the ribbon and click on “From Text/CSV” in the “Get & Transform Data” group. If you’re using an older version of Excel, you may need to go to the “Data” tab and click on “From Text” instead.

How to Import Text File or CSV File into Excel 1.png

Step3: Navigate to the location of the text or CSV file you want to import and select it. If the file is not in the default location, you can click on the “Browse” button to search for it. then click on the Import button.

How to Import Text File or CSV File into Excel 2.png

Step4: The Text Import Wizard will open in the Microsoft Excel Spreadsheet. Choose one delimiter based on your imported file, such as: Tab character.

How to Import Text File or CSV File into Excel 3

Step5: Once you’re satisfied with the settings, click on the “Load” button to import the data into Excel. The data will be inserted into a new worksheet in the workbook.

How to Import Text File or CSV File into Excel 4

Open Text File Using with Open Command in Excel

You can also use the “Open” command to open or import a text file or CSV file. Here are the steps:

Step1: Open a new or existing Excel workbook.

Step2: Go to the “File” tab on the ribbon and click on “Open“.

open text file in excel 1.png

Step3: Navigate to the location of the text or CSV file you want to import and select it.

Step4: In the “Open” dialog box, select “Text Files” or “All Files” from the drop-down menu next to “File name”. If you don’t see the file type you want to import, you can select “All Files” and then locate the file manually.

open text file in excel 2.png

Step5: Select the file you want to import and click on “Open“.

Step6: The Text Import Wizard will open. The first step is to select the file type. By default, Excel will detect the file type based on the file extension, but you can also select the file type manually. For a text file or CSV file, you should select “Delimited“. Click Next button.

open text file in excel 3.png

Step7: you need to specify the delimiter used in the file. Choose Tab checkbox as delimiters. Click Next button.

open text file in excel 4.png

Step8: Preview the data to make sure it looks correct in Data Preview section. If you need to make any changes, you can go back to the previous steps in the Text Import Wizard. Click “Finish” button.

open text file in excel 5.png

Step9: Click on the “Load” button to import the data into Excel. The data will be inserted into a new worksheet in the workbook.

open text file in excel 6.png

If you want to import multiple text files, and you can go back to the “From Text/CSV” dialog box and repeat the above steps for each file.

Import CSV File to Worksheet with VBA Code

You can also import CSV file to a worksheet with VBA code in Excel, and you just need a few lines of VBA code, and you can quickly import a CSV file into a new worksheet in the active workbook.

Just do the following steps:

Step1: Press Alt + F11 to open the Microsoft VBA editor

import csv file with vba 1.png

Step2: From the menu bar, choose Insert > Module

import csv file with vba 2.png

Step3: In the Module window, paste the following VBA code.

import csv file with vba 2.png
Sub ImportCSV()

    ' Prompt the user for the path and filename of the CSV file
    Dim filePath As String
    filePath = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "excelgeek for Excel", , False)
    ' Check if the user canceled the input box
    If filePath = "" Then
        MsgBox "No file selected. Import canceled.", vbExclamation, "Import CSV"
        Exit Sub
    End If

    Set rangeAddress = Application.InputBox("please select a cell to output csv data", "excelgeek for Excel", Application.ActiveCell.Address, , , , , 8)

    ' Import the CSV file into a new worksheet in the active workbook
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=Range(rangeAddress.Address))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
End Sub

Step4: Press F5 to run the code, or press the “Run” button on the toolbar

import csv file with vba4.png

Step5: You need to select one CSV file in the Import dialog box.

import csv file with vba5.png

Step6: you need to select one cell to output the csv data that you want to import.

import csv file with vba6.png

Step7: The selected CSV data will be inserted into the current worksheet.

import csv file with vba7.png

Conclusion

Importing text files or CSV files into Excel is very useful and it can save you time and effort when working with large datasets. With the built-in tools and methods provided by Excel, you can easily import your data and begin analyzing, manipulating, and visualizing it in a meaningful way.