Category: Excel Basics

This post will guide you through three different methods to save a chart as a TIFF image in Excel 2013/2016/2019/365.

The first method involves using the “Save As Picture” feature in Excel, which allows you to save a selected chart as a TIFF image with just a few clicks.

The second method involves using the “Copy and Paste” feature to copy the chart as an image and then save it as a TIFF file using an image editing program.

The third and most advanced method involves using VBA code to automate the process of exporting multiple charts as TIFF images.

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

To save or export an Excel chart as a TIFF image using the “Save As Picture” feature, follow these steps:

Step1: Select the chart you want to save as a TIFF image.

Step2: Right-click on the chart and select “Save As Picture” from the context menu.

Step3: In the “Save As Picture” dialog box, select “TIFF” as the file type. Choose the destination folder where you want to save the TIFF image and enter a name for the file.

Step4: Click the “Save” button to save the chart as a TIFF image.

That’s it! The chart will be saved as a TIFF image in the selected destination folder with the specified filename.

Copy and Paste the Chart as TIFF Image

You can also use Copy and Paste feature to save the selected chart as TIFF image. Just do the following steps:

Step1: Open the Excel spreadsheet that contains the chart you want to save as a TIFF image.

Step2: Click on the chart to select it. Press “Ctrl + C” to copy the chart.

Step3: Open your preferred image editor (such as Microsoft Paint or Adobe Photoshop).

Step4: Press “Ctrl + V” to paste the chart into the image editor.

Step5: In the image editor, choose the “Save As” option from the “File” menu. Choose the location where you want to save the TIFF image. Select “TIFF” or “TIFF Image (.tif;.tiff)” in the “Save As Type” dropdown menu. Enter a file name for the TIFF image.

Step6: Click on the “Save” button.

Copy and Paste the Chart with VBA Code

If you want to save all charts as TIFF images in an Excel workbook, and you can use a VBA code to do it. Just follow these steps:

Step1: Open your Excel workbook containing the charts.

Step2: Press “Alt + F11” to open the VBA editor.

Step3: In the editor, go to “Insert” > “Module” to create a new module.

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

Sub SaveChartsAsTIFF_excelgeek()
    Dim myChart As ChartObject
    Dim myPath As Variant
    Dim myFile As String
    
    'Prompt user to select the destination folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Destination Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        myPath = .SelectedItems(1)
    End With
    
    'Loop through all chart objects in the workbook
    For Each myChart In ActiveSheet.ChartObjects
        myChart.Chart.Export Filename:=myPath & "\" & myChart.Name & ".tif", FilterName:="TIFF"
    Next myChart
    
    'Display message box when all charts are saved
    MsgBox "All charts have been saved as TIFF images in the selected folder."
    
End Sub

Step5: Press “Alt + F8” to open the Macro dialog box.Select the “SaveChartsAsTIFF_excelgeek” macro and click “Run“.

Step6: When prompted, select the destination folder where you want to save the TIFF images.

Step7: Click “OK” and the macro will save all charts as TIFF images to the selected folder.

Conclusion

Saving charts as TIFF images in Excel is a useful and easy-to-accomplish task that can be done using different methods. If you need to export multiple charts as TIFF images, VBA code provides an efficient and automated solution.

This post will introduce you to three methods for saving shapes as images in Excel.

• The first method involves using the copy and paste functionality to manually copy each shape to a temporary chart and then save the chart as an image.

• The second method uses VBA code to automate the process of copying the shapes to a chart and saving the chart as an image.

• The third method involves saving the worksheet as a web page, which automatically saves each shape as an individual image file.

Save Shapes as Images Using the “Copy and Paste” option

Step1: Select the shape you want to save as an image.

Step2: Press “Ctrl + C” to copy the shape to the clipboard.

Step3: Open a new Microsoft Paint file or any image editing software of your choice. Press “Ctrl + V” to paste the shape onto the new file.

Step4: Save the file in your desired file format (such as PNG or JPEG) and select a location to save the image.

This method is useful for saving multiple shapes as images or for making edits to the shape before saving it as an image.

Save All Shapes as Image Using “saving as web page”

You can save all shapes on a worksheet as individual images by using Excel’s “Save as Web Page” feature. This feature converts the worksheet into a webpage and saves it along with all embedded objects, including shapes, as separate image files. Here’s how you can do it:

Setp1: Select the worksheet that contains the shapes you want to save as images.

Setp2: Click on “File” in the top left corner of the Excel window and select “Save as“. Select “Web Page (.htm;.html)” in the “Save as type” dropdown menu. Choose a location to save the file and enter a file name.

Setp3: In the “Save As” dialog box, make sure that the “Selection: Sheet” option is selected. Click on Save button.

Step4: click on Publish buttonin the Publish as Web Page dialog box.

Setp5: Excel will now save the worksheet as a webpage, along with all embedded objects, including shapes, as separate image files in a folder named “yourfilename_files” (where “yourfilename” is the name you gave to the file in step 2).

Setp6: Navigate to the folder where you saved the file, and you should see all of the images that correspond to the shapes on the worksheet.

Save Shapes as Image with VBA Code

You can use the VBA code to create a temporary chart object, pastes the selected shape inside the chart, and then saves the chart as an image. Just do the following steps:

Step1: Open the Excel workbook that contains the shapes you want to save as images.

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

Step3: In the Visual Basic Editor, select Insert from the menu bar and choose Module.

Step4: Copy the following VBA code for saving shapes as images and paste it into the new module (Module1).

Sub sleep(T As Single)
    Dim time1 As Single
    time1 = Timer
    Do
        DoEvents
    Loop While Timer - time1 < T
End Sub

Sub SaveShapeAsImage_excelgeek()
    Dim shp As Shape
    Dim savePath As String, fileName As String, fileFormat As String
    Dim cht As ChartObject
    Dim UserSelection As Variant
    
    Set UserSelection = ActiveWindow.Selection
    Set shp = ActiveSheet.Shapes(UserSelection.Name)
    
    ' prompt user for save location and file name
    savePath = Application.GetSaveAsFilename(InitialFileName:=shp.Name, _
        FileFilter:="JPEG (*.jpg), *.jpg, PNG (*.png), *.png")
    If savePath = "False" Then Exit Sub ' exit if user clicks cancel
    
    ' extract file name and format from save path
    fileName = Mid(savePath, InStrRev(savePath, "\") + 1)
    fileFormat = Mid(fileName, InStrRev(fileName, ".") + 1)
    fileName = Left(fileName, InStrRev(fileName, ".") - 1)
    
    ' create temporary chart object, set dimensions to shape dimensions, paste shape inside, and save as image
    Set cht = ActiveSheet.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    shp.Copy
    Call sleep(2)
    cht.Chart.Paste
    cht.Chart.Export savePath, fileFormat
    cht.Delete
    Application.CutCopyMode = False
    
End Sub

Step5: Press F5 or select Run from the menu bar to execute the code. Or Press ALT + F8 to open the Macro dialog box. Then select the macro named “SaveShapeAsImage_excelgeek” from the list of macros and click on “Run”.

Step6: you need to specify the file format and location for the saved image.

Step7: Check the file path you specified in the code to confirm that the images have been saved successfully.

Conclusion

There are three methods for saving shapes as images in Excel. The method you choose will depend on your specific needs and preferences. Whether you prefer a manual approach or an automated one, these methods provide several options for saving shapes as images in Excel.

This post will guide you on how to save or export Excel charts as PDF using the move chart feature and VBA code. And we will provide you with the necessary steps to export multiple charts into a PDF file.

The move chart feature allows you to easily move charts to a new worksheet, which is useful when exporting multiple charts. Additionally, we will provide VBA code that automates the process of exporting multiple charts into a single PDF file.

Save one Chart to a PDF Page

You can save an Excel chart as a PDF page by following these steps:

Step1: Select the chart you want to save as a PDF.

Step2: Click on the “File” menu in Excel, then select “Save As“. the “Save As” dialog box will open. Choose the location where you want to save the PDF file. In the “Save as type” dropdown menu, select “PDF (*.pdf)”. Enter a name for the PDF file in the “File name” field.

Step3: Click on the “Options” button to open the “Options” dialog box.

Step4:  In the “Options” dialog box, choose the “Selected chart” option under the ” Publish What” section.

Step5: Click on the “OK” button to close the “Options” dialog box.

Step6: Click on the “Save” button to save the chart as a PDF file.

The resulting PDF file will contain only the selected chart and nothing else.

Export Multiple Charts into one PDF File using Move Chart

If you need to print several charts, it can be more efficient to print them all at once in one document rather than printing them separately.

Here are the steps to export multiple charts into one PDF file:

Step1: Select the first chart you want to export.

Step2: Right-click on one of the selected charts and choose “Move Chart“. The “Move Chart” dialog box will open.

Step3: In the “Move Chart” dialog box, select the “New sheet” radio button and click “OK“.

Step4: Repeat steps 1-3 for each chart you want to export.

Step5: Select all of the charts by holding down the Ctrl key and clicking on each chart sheet tab that you want to export.

Step6: Click on the “File” menu and choose “Save As“. Choose “PDF” as the file type in the “Save As” dialog box. Enter a name for the PDF file.

Step7: In the “Save As” dialog box, choose the option “Active sheet” under “Publish what“. Click on the “OK” button to close the dialog box.

Step8: Click on the “Save” button to save the PDF file.

You can see that the last PDF file will contain one page for each chart you exported, with each chart on a separate page.

Export Multiple Charts into one PDF File with VBA Code

You can save or export multiple charts in the active workbook into one PDF file using VBA Code in Excel, and each chart is in a separate page. Just do the following steps:

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

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

Step3: In the VBA Editor, click “Insert” from the top menu, then choose “Module“.

Step4: In the new module, paste the below VBA code.

Sub ExportAllChartsToPDF_excelgeek()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim co As ChartObject
    Dim exportSheet As Worksheet
    Dim chartCount As Integer
    Dim fileSaveName As Variant
    Dim chartWidth As Integer
    Dim chartHeight As Integer
    Dim chartTop As Integer
    Dim chartLeft As Integer
    
    ' Set a reference to the active workbook
    Set wb = ActiveWorkbook
    
    ' Create a new worksheet to export the charts
    Set exportSheet = wb.Worksheets.Add
 
    'Copy each chart to the export sheet
    For Each mySheet In wb.Worksheets
        For Each myChart In mySheet.ChartObjects
            chartCount = chartCount + 1
            If chartCount > 0 Then
                exportSheet.HPageBreaks.Add Before:=exportSheet.Cells(chartCount * 30, 1)
                'exportSheet.Cells(chartCount * 30, 1).Select
            End If
            myChart.Chart.ChartArea.Copy
            exportSheet.Paste Destination:=exportSheet.Cells(chartCount * 30 - 29, 1)
            
        Next myChart
    Next mySheet
    
    ' Check if any charts were exported
    If chartCount = 0 Then
        MsgBox "No charts found in workbook."
        exportSheet.Delete
        Exit Sub
    End If
    

    
    ' Export the export sheet as a PDF file
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save PDF File")
    If fileSaveName <> False Then
        'exportSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileSaveName
        exportSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    End If
    
    ' Delete the export sheet
    Application.DisplayAlerts = False
    exportSheet.Delete
    Application.DisplayAlerts = True
End Sub

Step5: Run the code by clicking the “Run” button or pressing “F5“. Or Press ALT + F8 to open the Macro dialog box or click on Macros command under Code group. Then select the macro named “ExportSelectedChartAsPicture_excelgeek” from the list of macros and click on “Run”.

Step6: choose one location where you want to save the PDF file.

Step7: The code will export all the charts in your workbook into one PDF file and save it to your specified location.

Conclusion

Exporting charts as PDFs is useful when you need to share or print them. If you have multiple charts in your Excel document and want to export them as a single PDF file with each chart on a separate page, you can use the VBA code provided in this article.

This post will provide you with step-by-step instructions on how to export one or more charts from Excel to PowerPoint using VBA code.

This VBA code provides a simple and efficient way to export one or more charts from an Excel worksheet to PowerPoint. Before running VBA code,  you need make sure that the Microsoft PowerPoint is installed on your computer, and that the PowerPoint object library is selected in the Visual Basic Editor.

How to Enable PowerPoint Object Library?

To select the PowerPoint object library in Visual Basic Editor, just follow these steps:

Step1: Open the Visual Basic Editor by pressing “Alt + F11” on your keyboard.

Step2: Go to “Tools” > “References” in the menu bar. the References-VBAProject window will open.

Step3: Scroll down the list of available references in References-VBAProject window and look for “Microsoft PowerPoint <version> Object Library“, where “<version>” is the version of PowerPoint installed on your computer. Check the box next to the PowerPoint object library to select it. Click “OK” to save the changes and close the References dialog box.

After selecting the PowerPoint object library, you can use VBA code to interact with PowerPoint in your Excel workbook. For example, you can create a new instance of PowerPoint, open a presentation, add new slides, insert text and shapes, and so on.

Export Single or Multiple Charts from Excel Worksheet to PowerPoint with VBA Code

You can refer to the following steps to export single or multiple charts from your worksheet to a PowerPoint file with VBA code:

Step1: Open your Excel workbook and navigate to the worksheet that contains the chart(s) you want to export.

Step2: Open the Visual Basic Editor by pressing “Alt + F11” on your keyboard.

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

Step4: In the new module, paste the following VBA code, Save the VBA module and return to the Excel worksheet.

Sub ExportChartsToPowerPoint_excelgeek()
    
    ' Declare variables
    Dim pptApp As PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim pptSlide As PowerPoint.Slide
    Dim pptShape As PowerPoint.Shape
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim slideIndex As Integer
    
    ' Prompt user for PowerPoint file name and location
    pptFileName = Application.GetSaveAsFilename(FileFilter:="PowerPoint Presentation (*.pptx), *.pptx", Title:="Save as PowerPoint Presentation")
    
    ' Exit if user cancels or no file is selected
    If pptFileName = False Then Exit Sub
    
    ' Create new PowerPoint application and presentation
    Set pptApp = New PowerPoint.Application
    Set pptPres = pptApp.Presentations.Add
    
 ' Loop through each chart object in the active worksheet
For Each chartObj In ActiveSheet.ChartObjects
    
    ' Create a new slide in the PowerPoint presentation
    Set pptSlide = pptPres.Slides.Add(pptPres.Slides.Count + 1, ppLayoutTitleOnly)
    slideIndex = pptSlide.slideIndex
    
    ' Copy the chart to the clipboard and paste it as an image onto the slide
    chartObj.Chart.ChartArea.Copy
    pptSlide.Shapes.Paste
    Set pptShape = pptSlide.Shapes(1)
    
    ' Format the chart image as desired
    pptShape.Width = 400
    pptShape.Height = 300
    pptShape.Left = 100
    pptShape.Top = 100
    
    ' Add a title to the slide
    pptSlide.Shapes.Title.TextFrame.TextRange.Text = chartObj.Chart.ChartTitle.Text
    
Next chartObj
    
    ' Save and close the PowerPoint presentation
    pptPres.SaveAs pptFileName
    pptPres.Close
    
    ' Clean up
    Set pptApp = Nothing
    Set pptPres = Nothing
    Set pptSlide = Nothing
    Set pptShape = Nothing
    Set chartObj = Nothing
    Set chartRange = Nothing
    
    ' Show message box to confirm export
    MsgBox "Charts exported to " & pptFileName
    
End Sub

Step5: Run the VBA code by going to “Developer” > “Macros” and selecting the “ExportChartsToPowerPoint_excelgeek” macro.

Step6: This code will prompt the user for a file name and location to save the PowerPoint presentation.

Step6: The code will create a new instance of PowerPoint, open a new presentation, and loop through each chart on the active worksheet.

For each chart, the code will copy the chart and paste it as an Enhanced Metafile on a new slide in the PowerPoint presentation.

If the slide contains four or more charts, the code will add a new slide to the presentation before pasting the next chart.

Step7: When the code finishes running, a message box will appear indicating that the charts were exported successfully.

Conclusion

Exporting charts to PowerPoint can be a time-consuming process, especially when dealing with multiple charts. With the VBA code, you can automate this process and save yourself valuable time.

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.

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

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.

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

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.

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

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

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.

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

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

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.”

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.

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:

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.

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.

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.

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

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.

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

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.

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

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.

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.

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

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

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

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

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.

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.

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

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

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

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

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

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.

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

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

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“.

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

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.

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.