Search code examples
excelvbadirectory

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.

OriginalNativeName NewNativeName completepath\newfolder
complete path folder1Newfilename1 complete path folder1Newfilename1 complete path folder1
complete path folder2
complete path folder1filename 3 complete path folder1Newfilename3 complete path folder1

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:
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

Solution

  • IsEmpty function - taken from learn.Microsoft.com

    enter image description here

    When a cell is entirely empty, i.e. when a worksheet is created, or the cell contents are deleted - the cell can be considered explicitly set to Empty and your script would work correctly.

    However, if the cell contains a value, string or formula (no matter what the result of that formula), the cell no longer holds that IsEmpty status.

    So if cell A1 is completely empty, and cell B1 contains the formula =A1 then IsEmpty(A1) would return True, but IsEmpty(B1) would return False.

    To test if a cell (or in this case the array's element) contains no data, use:

    If TAncNouv(L, 1) = vbNullString