In this week’s edition of Q&A, learn how to move files on your computer, use AverageIf with filters and prevent users from closing their workbook with unprotected sheets.

Quick Jump
Q1: Moving Files?
Q2: Using Hyperlinks to move files?
Q3: Moving and renaming files?
Q4: Ignoring Hidden Rows?
Q5: Message Box before Closing Workbook?

Q1: How do I move a file from one folder to another using VBA?

To move a file from one folder to another, use the following code:

Sub MoveFile()
Name "C:\test.txt" As "C:\Users\test.txt"
End Sub

This example moves the file test.txt from the directory C: to C:\Users

Q2: I have hyperlinks to files in a spreadsheet. Is there a way to move these files to another location?

Absolutely! Here’s a simple version that assumes the cell containing the hyperlink is in “A1” of the active sheet. You can modify this to loop through an entire column of hyperlinks if you want. An example of a column loop is presented in my answer to Q3.

Sub MoveFile2()
Dim strPath As String
strPath = Range("A1").Hyperlinks.Item(1).Address
Name strPath As "C:\Users\test.txt"
End Sub

This macro reads the hyperlink address and moves the linked file to C:\Users\test.txt.

Q3: I have a spreadsheet with file paths in Column A. I want to move the files to a specific folder and rename them with the name in Column B. Is that possible?

Anything is possible with VBA! This will do the trick for you. It copies the files mentioned in column “A” to the file names mentioned in column “B,” while preserving the same file extension the file originally held.

Sub CopyFile()
Dim i As Integer, lastrow As Integer, iext As Integer

lastrow = Range("a50000").End(xlUp).Row
For i = 1 To lastrow
    iext = InStrRev(Range("a" & i), ".")
    FileCopy Range("a" & i), "C:\Users\Desktop\new data\" & Range("b" & i) & _
        Mid(Range("a" & i), iext, Len(Range("a" & i)))
Next i
End Sub

Q4: I'm applying a filter but my AVERAGEIF() function averages all the values, including hidden values. How can I average only the visible values?

I recommend creating a custom function. Paste the following macro in your VBA editor:

Function AverageIfVisible(rng2check As Range, condition, rng2avg As Range)
  Dim i As Long
  Dim icount As Long
  For i = 1 To rng2avg.Count
    If rng2check(i) = condition And rng2avg(i).EntireRow.Hidden = False Then
      icount = icount + 1
      AverageIfVisible = (AverageIfVisible + rng2avg(i))
    End If
  Next i
  AverageIfVisible = AverageIfVisible / icount
End Function

This function behaves like the AVERAGEIF() function. It accepts 3 arguments:

  1. The range to check
  2. The criteria to check against
  3. The range to actually average

If your original formula was =AVERAGEIF($A$2:$A$100,B1,$C$2:$C$100), change it to =AverageIfVisible($A$2:$A$100,B1,$C$2:$C$100) to only look at visible cells. Cells hidden by your filter will be ignored!

Q5: How do I prevent the user from closing the Excel workbook if his/her worksheet is unprotected?

Try placing the following VBA macro in the ThisWorkbook section of your VBA editor:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim X As Boolean

    X = False
    
    If ActiveSheet.ProtectContents Then X = True
    If ActiveSheet.ProtectDrawingObjects Then X = True
    If ActiveSheet.ProtectScenarios Then X = True
    If ActiveSheet.ProtectionMode Then X = True

    If X = False Then
        MsgBox "The worksheet is not protected."
        Cancel = True
    Else
        MsgBox "The worksheet is protected."
    End If
End Sub

The Cancel = True line blocks the user from closing the file, so be kind! You can really frustrate people with this line of code by preventing them from ever closing their workbook:)

Comments

This is the first of what I hope to many Q&A posts. Let me know what you think by leaving a comment!

If you learned something today, share this article with a friend, submit a comment below and follow me on Google+! Come back often to see more great VBA ideas.

Remember, if you or your company are struggling with VBA, just contact me and I’ll gladly point you in the right direction.


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.