Excel VBA Bangla 67 : How to covert pdf to Excel file and browse folder in excel using VBA

March 24, 2023

Converting a PDF file to an Excel file in VBA is a complex task that requires additional software or APIs. However, you can use VBA to browse for a folder and list the files in that folder. Here are the steps:

Open the Visual Basic Editor in Excel by pressing “Alt + F11”.

In the editor, select the worksheet or workbook where you want to use the macro.

Click “Insert” from the menu and select “Module” to create a new module.

In the new module, copy and paste the following code:

vbnet
Copy code
Sub BrowseForFolder()
Dim objShell As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Set objShell = CreateObject(“Shell.Application”)
Set objFolder = objShell.BrowseForFolder(0, “Select a folder”, 0)
If Not objFolder Is Nothing Then
For Each objFile In objFolder.Items
i = i + 1
Cells(i, 1).Value = objFile.Path
Next objFile
End If
End Sub
Save the module with a descriptive name, such as “BrowseForFolder”.

Close the Visual Basic Editor and return to the worksheet.

Assign the macro to a button, shape, or keyboard shortcut. To assign the macro to a button, for example, click “Developer” from the ribbon and select “Insert”. Then, select “Button” from the ActiveX Controls, and draw the button on the worksheet. Right-click the button, select “Assign Macro”, and choose the “BrowseForFolder” macro from the list.

Test the macro by clicking the button, which will display a folder dialog box. Browse to the folder you want to list, and click “OK”.

When you run the macro, it will display a folder dialog box, which allows you to browse for and select a folder. The selected folder will be stored in the “objFolder” object. The macro then loops through each file in the folder and lists the file path in column A of the worksheet. You can modify the code inside the loop to perform any operation you want on the files in the folder.

on…(read more)