Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-30-2022, 12:43 PM
kilroyscarnival kilroyscarnival is online now Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2021
Competent Performer
Move individual files to various folders using VBA?
 
Join Date: May 2019
Posts: 188
kilroyscarnival is on a distinguished road
Default Move individual files to various folders using VBA?

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.
Attached Files
File Type: xlsx Files_Folders.xlsx (13.4 KB, 2 views)
Reply With Quote
  #2  
Old 12-30-2022, 01:39 PM
macropod's Avatar
macropod macropod is offline Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-30-2022, 01:52 PM
kilroyscarnival kilroyscarnival is online now Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2021
Competent Performer
Move individual files to various folders using VBA?
 
Join Date: May 2019
Posts: 188
kilroyscarnival is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 12-30-2022, 05:16 PM
macropod's Avatar
macropod macropod is offline Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by kilroyscarnival View Post
I just realized, I meant to post this in the Excel-Programming folder.
Which is where I've moved it to.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 01-12-2023, 07:31 AM
kilroyscarnival kilroyscarnival is online now Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2021
Competent Performer
Move individual files to various folders using VBA?
 
Join Date: May 2019
Posts: 188
kilroyscarnival is on a distinguished road
Default

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.
Attached Files
File Type: zip TestPath.zip (222.6 KB, 1 views)
Reply With Quote
  #6  
Old 01-12-2023, 01:47 PM
macropod's Avatar
macropod macropod is offline Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 01-13-2023, 12:42 PM
kilroyscarnival kilroyscarnival is online now Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2021
Competent Performer
Move individual files to various folders using VBA?
 
Join Date: May 2019
Posts: 188
kilroyscarnival is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 01-25-2023, 02:39 PM
kilroyscarnival kilroyscarnival is online now Move individual files to various folders using VBA? Windows 10 Move individual files to various folders using VBA? Office 2021
Competent Performer
Move individual files to various folders using VBA?
 
Join Date: May 2019
Posts: 188
kilroyscarnival is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Move individual files to various folders using VBA? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft