Move individual files to various folders using VBA?

kilroyscarnival · #1 12-30-2022, 12:43 PM

I've been trying to figure out whether it's feasible to write either VBA, or a Power Automate flow, to help me do a repetitive task. Basically, at the end of every month, I need to move almost 100 PDF's of invoices from one common folder to the corresponding individual project folders.

I have made an Excel sheet that has: The names of the PDF files, the project numbers, and the file paths where the project invoices would go (for example, X:\Projects|12345G Tall Pines Property\Invoices\). Instead of having to move them one by one, using two screens with File Explorer, I'd love to automate this. Because the relevant info is already in Excel, I wonder whether it could be done there. I originally thought Power Automate Desktop, but every month the invoice numbers would of course be different, and it won't be the same exact list of projects being billed.

I envision running a simple Powershell dirlist to get the PDF file names, applying a quick XLOOKUP to pull in the other information, and only having to update a few file paths for new projects each month.

macropod · #2 12-30-2022, 01:39 PM

Try:

Code:

Sub InvoiceArchiver()
Application.ScreenUpdating = False
Dim FSO As Object, r As Long, i As Long, StrSrc, StrTgt, StrFlNm As String, StrTmp As String
Set FSO = CreateObject("Scripting.FileSystemObject")
With ActiveSheet
  For r = 2 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    StrFlNm = .Range("C" & r): StrSrc = ThisWorkbook.Path & "\": StrTgt = .Range("D" & r): StrTmp = ""
    'Confirm the source file exists
    If Dir(StrSrc & StrFlNm, vbNormal) <> "" Then
      'Confirm the destination folder exists, & create it if needed
      If Dir(StrTgt, vbDirectory) = "" Then
        For i = 0 To UBound(Split(StrTgt, "\")) - 1
          StrTmp = StrTmp & Split(StrTgt, "\")(i) & "\"
          If Dir(StrTmp, vbDirectory) = "" Then MkDir StrTmp
        Next
      End If
      'Delete any existing copy of the file in the destination folder
      If Dir(StrTgt & StrFlNm, vbNormal) <> "" Then Kill StrTgt & StrFlNm
      'Move the file
      FSO.MoveFile StrSrc & StrFlNm, StrTgt & StrFlNm
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

kilroyscarnival · #3 12-30-2022, 01:52 PM

Wow! I cannot wait to take this for a spin next week! Thanks, Paul. And, I just realized, I meant to post this in the Excel-Programming folder. I came back to this one to look for something and forgot to check my location.

Cheers, and happy new year to all!

Best,

Ann

macropod · #4 12-30-2022, 05:16 PM

Quote:

Originally Posted by kilroyscarnival

I just realized, I meant to post this in the Excel-Programming folder.

Which is where I've moved it to.

kilroyscarnival · #5 01-12-2023, 07:31 AM

Paul, I wish I could say I got this to work. I did try it on a test batch of PDFs where the PDFs to be moved and the Excel workbook containing the paths and the VBA were in the same test folder, and the destination paths were various places on our shared drive.

When the test folder was placed on my desktop, where it's automatically synced to my OneDrive, I could see when I hovered on the code that it was connecting to the first row of information in the Excel sheet. However, it gave me an error on this line:

Code:

  If Dir(StrSrc & StrFlNm, vbNormal) <> "" Then

I suspect that's because it may have been redirecting to the OneDrive rather than being on the desktop itself.

When I moved the entire test folder to an area on the shared drive where my invoice work (the things I want to be moved) will be, the VBA didn't generate any errors, but it didn't appear to do anything either.

By then I got buried in other things. So I have moved the files manually in the meantime.

I am sure I'm missing something obvious. I tried three different destination paths just to see if any one of them worked. My version is Microsoft® Excel® for Microsoft 365 MSO (Version 2211 Build 16.0.15831.20098) 64-bit.

macropod · #6 01-12-2023, 01:47 PM

You didn't mention OneDrive before, so the code was written assuming everything was on a local drive. In my testing (including with an external USB drive) it worked fine for that. Since OneDrive uses a url for the path behind the scenes, a different approach would be needed. A workaround would be to create an alias for the OneDrive folder on a local drive and use that.

kilroyscarnival · #7 01-13-2023, 12:42 PM

Yes, OneDrive isn't really relevant to what I was trying to do. I only noticed that when I did a practice run on the desktop, apparently that sync to OneDrive was in play. Normally none of the relevant folders interconnects with OneDrive at all.

I only mentioned it because that was where I got the error and I presume the reason was, the desktop was syncing to OneDrive. I'll have to test it out again using only local drives and see if it works. It's basically going from one shared drive (but not cloud) folder to another.

kilroyscarnival · #8 01-25-2023, 02:39 PM

Paul, it worked!

I finally started over again and realized I'd done the dumbest mistake. I'd named my dummy files "TestFile1.pdf" etc., and when opening one of them, I noticed it opened as "TestFile2.pdf.pdf".

The frustrating thing was I had a sense it was something that basic messing me up... either my file paths weren't in the correct format or something like that. I just didn't spot it until I went back to it.

Thank you for all your help!

Best,

Ann

kilroyscarnival · #9 02-01-2023, 08:40 AM

Paul, today was the day I finally got to run the VBA on the real batch of 140+ documents, and I only hit a few minor snags that were easy to fix. One file path had changed because someone had renamed a folder (easy), and I created my table before they had added about 15 follow up documents, so I added them and their paths, and it was SO satisfying to follow up and make sure they all went to their respective folders. Thank you, thank you! I will definitely be using this again. It might have taken me two hours to move the files by hand.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Any way to move multiple email folders?	Jennifer Murphy	Outlook	2	08-21-2017 06:42 AM
Favorite Folders - Accessing via Move Menu	mattstoc	Outlook	0	02-20-2015 04:16 PM
Move Outlook folders from old HD to new HD	Dennism	Outlook	1	01-30-2012 11:51 AM
trying to move pesonal folders to new computer	mstruhs	Outlook	1	02-24-2010 10:07 PM
How do I move file folders ..please?	steve e	Outlook	0	05-15-2008 10:21 AM