6 Replies Latest reply on Feb 19, 2017 12:27 PM by Deepak Gupta

    Macro to rename files according to excel cells

    Andy Zhou

      Hi guys,

       

      I'm in need of your help in creating a macro to rename files which contains hundreds of Assembly, Parts, Drawings; off an excel spread sheet. In the spread sheet, the first column would contain the current file names of all Assembly, Parts and Drawings. The second column would contain the new names for the corresponding row. Like shown below in the picture.

       

       

      Regardless of the file extension, just replacing the old name with the new one while keeping all the references.

      I'm thinking it will have to use the PackandGo macro and make it search through the folder for the Old Name and replace it with the New Name of the next column. (eg. make it search for 'A1234' in the folder then replace it with 'C4567', then move on to search for 'A1235' so on.

       

      So my question is that, I'm not sure how to pull values from excel sheet; then search through the folder and replace it with another value from excel cell. Does anyone have an idea?

       

      Any help would be appreciated. Thank you.

        • Re: Macro to rename files according to excel cells
          Mr Omkar Deshpande

          Andy Zhou wrote:

           

           

          So my question is that, I'm not sure how to pull values from excel sheet; then search through the folder and replace it with another value from excel cell. Does anyone have an idea?

           

           

          Hi,

            You will have to add a reference of Microsoft Excel 14.0 Object Library.

            And then using CreateObject("Excel.Application") or GetObject(, "Excel.Application") you can access the excel files.

          Similarly, to search through folder, you may need to add reference of Microsoft Scripting Runtime

          And using CreateObject("Scripting.FileSystemObject") or GetObject(, "Scripting.FileSystemObject").

           

          However, you should use 'PackNGo' instead of FileSystemObject so that you can maintain the references of SolidWorks files.

            • Re: Macro to rename files according to excel cells
              Andy Zhou

              Hi Omkar,

               

              Instead of using scripting, would this also work? Trying to get if the Value (old name) matches the nValue(new name), and it has the right file extension, it will rename accordingly.

               

              Dim PackAndGoObj As PackAndGo
              Set PackAndGoObj = swModelDocExt.GetPackAndGo
              Dim VDocs
              Dim result As Boolean
              PackAndGoObj.FlattenToSingleFolder = True
              PackAndGoObj.IncludeToolboxComponents = True
              PackAndGoObj.IncludeDrawings = True
              result = PackAndGoObj.GetDocumentNames(VDocs)


              Dim xl As Excel.Application
              Dim xlsh As Excel.Worksheet
              Set xl = GetObject(, "Excel.Application")
              Set xlsh = xl.ActiveSheet
              Dim row As Integer
               
                  row = 1
                 

                
              For i = 0 To UBound(VDocs)

                  Value = xlsh.Cells(row, 1).Value
                  nValue = xlsh.Cells(row, 2).Value
                 
                  If Value = Split(VDocs(i), ".")(0) And Split(VDocs(i), ".")(1) = "sldprt" Then
                  VDocs(i) = "nValue" & ".sldprt"

                  ElseIf Value = Split(VDocs(i), ".")(0) And Split(VDocs(i), ".")(1) = "sldasm" Then
                  VDocs(i) = "nValue" & ".sldasm"

                  ElseIf Value = Split(VDocs(i), ".")(0) And Split(VDocs(i), ".")(1) = "slddrw" Then
                  VDocs(i) = "nValue" & ".slddrw"

                  End If
                 
                  row = row + 1
                 
              Next i

               

              result = PackAndGoObj.SetSaveToName(True, SavingPath)
              result = PackAndGoObj.SetDocumentSaveToNames(VDocs)
              Dim vResult
              vResult = swModelDocExt.SavePackAndGo(PackAndGoObj)

            • Re: Macro to rename files according to excel cells
              Christian Chu

              Andy,

              If you only want to rename files in Excel cells in a certain orders, you can create simple script in Excel without calling SW API