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.
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
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 MoveFile() 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
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:
- The range to check
- The criteria to check against
- 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
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:)
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.
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About 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.Follow