Excel VBA - Skip renaming when cell empty

I use PowerQuery to create a table from databases and manage files.

There are some files I need to move to a specific folder, depending the value in some column. No need in my VBA code to check those, all the various checks are done with PowerQuery.

THere is in fact more column in my table, but the 3 I look at in the macro are those 3.

My VBA code is just to move the files listed in my generated table. Some line are blank because there is no file or no need to rename ( but other columns are needed, so I cannot delete my line completely). I need to create the folder, if needed, before moving my files. but I also need to skip the lines that are blank and it's here I have error.

My last attempt as code is the following one, where I try to skip the renaming.

   Sub Move_Native()
   Dim TAncNouv(), L&
   Dim Fobj As Object
   Set Fobj = CreateObject("scripting.filesystemobject")
   ChDrive ThisWorkbook.Path: ChDir ThisWorkbook.Path
   TAncNouv = ActiveSheet.Range("xml_ENGDOC[OriginalNativeName]").Resize(, 3).Value
   
   For L = 1 To UBound(TAncNouv, 1)
   If Fobj.FolderExists(TAncNouv(L, 3)) = False Then
   MkDir (TAncNouv(L, 3))
   End If
   If IsEmpty(TAncNouv(L, 1)) = True Then
   
   Else
      
      Name TAncNouv(L, 1) As TAncNouv(L, 2)
      
      End If
      
      Next L
   End Sub

But I still have an Run Time error '75': Path/File access error:

so it means I'm not skipping correctly. What am I doing wrong ? what can I do to skip this part ?

Thanks in advance

Skip When Cell Is Blank

• A cell is empty when there is nothing in it. Some cells seem to be empty but are not for example cells containing formulas evaluating to ="" or containing "'" or whatnot. Also, the latter cells copied as values will still not become empty.
  Power Query copies them exactly as they are (cells can be empty).

• To get around this, you could check if their values are equal to "" as illustrated in CLR's answer but I prefer to check if their length is 0.

• Additionally, to prevent an error when the cell contains an error, I use the CStr function:

  If Len(CStr(cell.Value)) = 0 Then ' is blank
  If Len(CStr(cell.Value)) > 0 Then ' is not blank
  

• When dealing with files, there are two main ways in VBA:

  • Using the Dir and the 'accompanying' VBA functions MkDir, Name, Kill, ... etc.
    • very efficient
    • works on a MAC
    • can't handle unicode characters ('English only')
  • Using the FileSystemObject object
    • slower
    • doesn't work on a MAC
    • handles unicode characters
  • Although you can use both ways interchangeably, you would want to be consistent about it by using one or the other.

• The following solutions are not tested (the code compiled)!

• Note that existing (new) files will be overwritten!

At the Top of the Module

Option Explicit

Private Enum PathColumns
    OldFile = 1
    NewFile = 2
    NewFolder = 3
End Enum

Dir Function

Sub MoveNativeFilesDir()
    
    Const TABLE_NAME As String = "xml_ENGDOC"
    Const FIRST_COLUMN_TITLE As String = "OriginalNativeName"
    Const COLUMNS_COUNT As Long = 3 ' consecutive columns
    
    Dim TAncNouv() As Variant:
    TAncNouv = Application.Range(TABLE_NAME).ListObject _
        .ListColumns(FIRST_COLUMN_TITLE).DataBodyRange _
        .Resize(, COLUMNS_COUNT).Value
    
    Dim tRow As Long
    Dim OldFilePath As String, NewFolderPath As String, NewFilePath As String
    
    For tRow = 1 To UBound(TAncNouv, 1)
        OldFilePath = CStr(TAncNouv(tRow, PathColumns.OldFile))
        If Len(OldFilePath) > 0 Then
            If Len(Dir(OldFilePath)) > 0 Then ' old file exists
                NewFolderPath = CStr(TAncNouv(tRow, PathColumns.NewFolder))
                ' Note that the following 'If' statement will fail
                ' if the parent folder (e.g. `C:\Test1`) of the folder
                ' to be created (e.g. 'C:\Test1\Test2') doesn't exist!
                If Len(Dir(NewFolderPath, vbDirectory)) = 0 Then ' doesn't exist
                    MkDir NewFolderPath
                'Else ' new folder exists
                End If
                NewFilePath = CStr(TAncNouv(tRow, PathColumns.NewFile))
                If Len(Dir(NewFilePath)) > 0 Then ' new file exists
                    Kill NewFilePath ' delete
                'Else ' new file doesn't exist
                End If
                Name OldFilePath As NewFilePath ' move
            'Else ' old file doesn't exist
            End If
        'Else ' blank cell
        End If
    Next tRow
            
    MsgBox "Native files moved.", vbInformation

End Sub

FileSystemObject Object

Sub MoveNativeFilesFso()
    
    Const TABLE_NAME As String = "xml_ENGDOC"
    Const FIRST_COLUMN_TITLE As String = "OriginalNativeName"
    Const COLUMNS_COUNT As Long = 3 ' consecutive columns
    
    Dim TAncNouv() As Variant:
    TAncNouv = Application.Range(TABLE_NAME).ListObject _
        .ListColumns(FIRST_COLUMN_TITLE).DataBodyRange.Resize(, COLUMNS_COUNT)
    
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim tRow As Long
    Dim OldFilePath As String, NewFolderPath As String, NewFilePath As String
    
    For tRow = 1 To UBound(TAncNouv, 1)
        OldFilePath = CStr(TAncNouv(tRow, PathColumns.OldFile))
        If Len(OldFilePath) > 0 Then
            If fso.FileExists(OldFilePath) Then ' old file exists
                NewFolderPath = CStr(TAncNouv(tRow, PathColumns.NewFolder))
                ' Note that the following 'If' statement will fail
                ' if the parent folder (e.g. `C:\Test1`) of the folder
                ' to be created (e.g. 'C:\Test1\Test2') doesn't exist!
                If Not fso.FolderExists(NewFolderPath) Then ' ... doesn't exist
                    fso.CreateFolder NewFolderPath
                'Else ' new folder exists
                End If
                NewFilePath = CStr(TAncNouv(tRow, PathColumns.NewFile))
                If fso.FileExists(NewFilePath) Then ' new file exists
                    fso.CopyFile OldFilePath, NewFilePath, True ' overwrite
                    fso.DeleteFile OldFilePath, True ' also delete if read-only
                Else ' new file doesn't exist
                    fso.MoveFile OldFilePath, NewFilePath
                End If
            'Else ' old file doesn't exist
            End If
        'Else ' blank cell
        End If
    Next tRow
            
    MsgBox "Native files moved.", vbInformation

End Sub