Dear Reo
The code which I gave u earlier will actually save as PDF of a selected range of a current open file .. so to open a file the excel needs its path and file name.. so the list which u have should contain the path and filename.. to get the file path and names please use the below code which will give u the list of all files in folder and even subfolders.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
Cells.Clear
MsgBox "To avoid error" & vbNewLine & vbNewLine & "1. please make sure to enable microsoft scripting runtime from tools-> reference" & vbNewLine & _
"2. if not available please browse and select scrunn.dll " & vbNewLine & "3. then again enable the microsoft scripting runtime", vbInformation
'please make sure to enable microsoft scripting runtime if not available please browse and select scrunn.dll then enable
'the runtime so to run this code without error
'Set a reference to Microsoft Scripting Runtime by using
'Tools > References in the Visual Basic Editor (Alt+F11)
'Declare the variables
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim strTopFolderName As String
Dim strpath2 As String
'Insert the headers for Columns A through F
Range("A1").Value = "File Name"
Range("B1").Value = "File Size"
Range("C1").Value = "File Type"
Range("D1").Value = "Date Created"
Range("E1").Value = "Date Last Accessed"
Range("F1").Value = "Date Last Modified"
Range("G1").Value = "File path"
'this will select the folderpath
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show <> -1 Then MsgBox "No folder selected! Exiting sub...": Exit Sub
strpath2 = .SelectedItems(1)
End With
'Assign the top folder to a variable
strTopFolderName = strpath2
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the top folder
Set objTopFolder = objFSO.GetFolder(strTopFolderName)
'Call the RecursiveFolder routine
Call RecursiveFolder(objTopFolder, True)
'Change the width of the columns to achieve the best fit
Columns.AutoFit
MsgBox "done"
End Sub
Sub RecursiveFolder(objFolder As Scripting.Folder, _
IncludeSubFolders As Boolean)
'Declare the variables
Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim NextRow As Long
'Find the next available row
NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'Loop through each file in the folder
For Each objFile In objFolder.Files
Cells(NextRow, "A").Value = objFile.Name
Cells(NextRow, "B").Value = objFile.Size
Cells(NextRow, "C").Value = objFile.Type
Cells(NextRow, "D").Value = objFile.DateCreated
Cells(NextRow, "E").Value = objFile.DateLastAccessed
Cells(NextRow, "F").Value = objFile.DateLastModified
Cells(NextRow, "G").Value = objFile.path
NextRow = NextRow + 1
Next objFile
'Loop through files in the subfolders
If IncludeSubFolders Then
For Each objSubFolder In objFolder.SubFolders
Call RecursiveFolder(objSubFolder, True)
Next objSubFolder
End If
End Sub
So after u get the file names and path use my code
Sub Macro1()
Sub test()
Dim path As String
Dim actbook As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Sheet1").Select ‘rename the sheet that contains the list
Range("f2").Select ‘rename the range the path starts from
Do
path = ActiveCell.Value
Workbooks.Open Filename:=path
actbook = ActiveWorkbook.Name
'below is the selection
Range("B2:I16").Select
'the below command will save the pdf in its path with the same workbook name
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & "activeworkbook.name" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
