The benefit in adding the checks, of course, is that your macro is less likely to run into problems as it is used by people other than you. Notice how much longer the second version of the macro is than the first? Anytime you start adding multiple checks in a macro, it can really make it much longer than without the checks. If Sheets(K).Name = "Control" Then K = K + 1 ' Check for empty and duplicate cells in range STemp = "Control worksheet is not active." STemp = "There are more than 13 worksheets." ' Check to make sure exactly 13 worksheets in workbook Here's a commented version of the macro that takes into account several of the possibilities just mentioned: For instance, what should be done if there are more (or fewer) than 13 worksheets in the current workbook? What should be done if there are empty cells in the range A1:A12? What should be done if someone runs the macro and "Control" isn't the active worksheet? What should be done if there are two identical values in A1:A12? What if there are leading or trailing spaces on one or more names in the range A1:A12? These and (most likely) a whole range of other questions can affect how the macro finally looks. The macro simply steps through the cell range A1:A12 and, if the next worksheet isn't named "Control," it renames the worksheet to the cell value.Īs noted, this macro is very simplistic and should, in all likelihood, be a lot more robust. If Sheets(J).Name = "Control" Then J = J + 1 For instance, you could use a very simple macro like this: The core of developing a macro to address this need is to rely on the Name property of each worksheet you want to rename. The worksheet names don't need to be dynamic they just need to be renamed when he runs the macro. He needs a way, in a macro, to rename each of the other 12 worksheets in the workbook based upon that range of cells. If (strTemp ".") And (strTemp ".Gilbert has a worksheet (named "Control") that contains a list of desired worksheet names in cells A1:A12. 'Fill colFolders with list of subdirectories of strFolder 'Add files in strFolder matching strFileSpec to colFiles Public Function RecursiveDir(colFiles As Collection, _ Wbk.SaveAs Filename:=strFilename & "x", FileFormat:=xlOpenXMLWorkbook Wbk.SaveAs Filename:=strFilename & "m", FileFormat:=xlOpenXMLWorkbookMacroEnabled Set wbk = Workbooks.Open(Filename:=strFilename) RecursiveDir colFiles, strPath, "*.xls", True Set obj = CreateObject("Scripting.FileSystemObject") StrPath = Mid(strPath, 2, Len(strPath) - 2) If Right(strPath, 1) "\" Then strPath = strPath + "\" MsgBox "Cancelled By User",, "List Folder Contents" Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) Or if you don't need the macro code in the converted files you could leave it alone and it will remove the macros when it converts it to the xlsx format. Note: If the files you are converting contain macros then you would need to update the FileFormat:=xlOpenXMLWorkbook to read FileFormat:=xlOpenXMLWorkbookMacroEnabled. ' Re-enable confirmation dialogs and screen updates PressAlt + F11keys to enable theMicrosoft Visual Basic for Applicationswindow, and click Insert> Module. Left(fFile.Name, Len(fFile.Name) - 4)) & ".xlsx", _ Batch rename range names with macro code Supposing there are several range names in a workbook as below screenshot shown, now I will rename them by adding a prefix. WkbConvert.SaveAs FSO.BuildPath(fFile.ParentFolder, _ Select the worksheet tab that you want to rename, then press the keyboard shortcut Alt + O + H + R (Just press these 4 keys on the keyboard one by one in the sequence), type a name and press Enter. ' then change to FileFormat:=xlOpenXMLWorkbookMacroEnabled i have a folder containing 3000 PDF files, file name as 'PAN'Q32017-18, i want to rename the PDF file using excel. Set wkbConvert = Workbooks.Open(fFile.Path) If LCase$(Right(fFile.Name, 4)) = ".xls" Then ' and screen updates (to speed up conversion) ' Disable confirmation dialogs (to prevent "unsaved changes" dialog popping up) Then we need to change the current directory to the one we want. Start by opening the command prompt by clicking Start then typing cmd (in XP, hit run and then type cmd). Set fFolder = FSO.GetFolder(strConversionPath) Step 1 - Generate Your File List First thing is to get a list of all the files you want to rename. If FSO.FolderExists(strConversionPath) Then On Error GoTo 0 ' Re-enable default error handling On Error Resume Next ' Prevent debug mode if user cancels selection With Application.FileDialog(msoFileDialogFolderPicker) ' Convert all xls files in selected folder to xlsx This code would need to be run from an Excel 2007 or above workbook. This code assumes that the files are all located within one folder and that all xls files need to be converted, but if you wanted to select individual files this code could be updated. I'd recommend using a macro to process the files within a folder to convert them from xls to xlsx.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |