![]() ![]() Step 3: Design the Supporting structure to compose the new name ShtTool.Range("FinalName")(1).Offset(lngCount - 1).Formula = "=IF(ISBLANK(Override),NewName,Override)&Extension" ShtTool.Range("Override")(1).Offset(lngCount - 1).NumberFormat = "General" ShtTool.Range("Override")(1).Offset(lngCount - 1).Value = vbNullString ShtTool.Range("NewName")(1).Offset(lngCount - 1).Formula = "=Prefix&SUBSTITUTE(FileName,ReplaceWhat,ReplaceWith)&Suffix" ShtTool.Range("DateLastModified")(1).Offset(lngCount - 1).Value = Format(objFile.DateLastModified, "D MMM YYYY") ShtTool.Range("Extension")(1).Offset(lngCount - 1).Value = FileExtension(objFile.Name) ShtTool.Range("FileName")(1).Offset(lngCount - 1).Value = RemoveExtension(objFile.Name) ShtTool.Range("FullPath")(1).Offset(lngCount - 1).Value = objFile.Path ShtTool.Range("FileNumber")(1).Offset(lngCount - 1).Value = lngCount 'This is my preferred method, for various self percieved advantages. 'Please feel free to accomplish this however you see fit. 'Use the counter and named ranges to fill up the sheet. MsgBox "No files were found.", vbExclamation 'If the folder does not contain files, exit the sub VbNewLine & "VBA Error Description : " & Err.Description Please Check if the specified folder exists." & _ If Not Err.Number = 0 Then 'Standard Error Handling code Set objFolder = objFSO.GetFolder(strPath) Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an instance of the FileSystemObject StrPath = shtTool.Range("FilePath").Value2 Sub GetFiles(Optional ByVal booDummy As Boolean = True) 'A macro that uses FileSystemObject to access the names of all the files in a folder Subsequently, we write a macro to list all the files in the selected folder into a range in the spreadsheet.Step 2: Listing files inside selected Folder Click the image below to download the file. Please feel free to download the Workbook and snoop around, for some of the ancillary macros won’t make complete sense unless you take a look at the actual spreadsheet layout. Then we write a simple macro that writes the path to a cell in the sheet.On Error GoTo 0 'Standard Error Handling code OpenFolder = diaFolder.SelectedItems(1) & "\" 'Set AllowMultiSelect property to true to allow the user Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) ![]() 'Set the FileDialogue to be able to select only folders On Error Resume Next 'Standard Error Handling code 'A fairly generic Function to fetch a Forlder's Pathįunction OpenFolder(Optional ByVal AllowMulti As Boolean = False) As String ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |