Hi,
Greetings from sunny England.
I'm not well-versed in excel, but I've recently started a new role and I see opportunities to really improve some of the processes in place here.
The basic idea is to refine the process of saving files from emails to specific directories. I don't know if this is possible with excel interacting with outlook and it is well beyond my capabilities to maintain a system like this if it were possible.
So my compromise solution, which I'm looking to get assistance with, is to have a shared folder where people can drag outlook attachments into. What I want is an excel script to automatically go through this folder and save individual files to individual locations based on their filename. Is this possible?
What I've cobbled together so far from google:
I've got the ability to fetch the data from the folder of stored attachments via:
Where documents = the directory path
this gives me all of the file data on an excel sheet.
On that excel sheet I've split out the data on the file name via:
So I can have individual cells with the key info - Date, place, visit type.
This is where I've become unstuck. I've tried to cobble together bits of VBA but I just don't know where to head now and can't find specific answers.
This is the VBA I've messed around with:
so as you can see I'm trying to set 6 variables - path being where to save the document
the path will be different based on the "contractor" variable
so is it possible to have the contractor variable affect the path variable?
For the variable "range" I don't know how this works? Can I just specify a column and it will cycle through individual cells until it hits blanks? Or with the above, will it try to collate all the column data at once?
Also at the moment I'm aware I have activeworkbook.saveas - which is clearly not correct. Is it possible to find the file name based on cell data, store it as a variable of some kind, and then save it to the chosen path based on the file name contents?
Long story short - I don't know what I'm doing :D
Hope any of this makes sense.
Thanks,
[Edit]
To try and clarify:
From the folder where the files are dragged
Ideally I want to be able to save as, based on the current file name
a second but less ideal option would be for us to manually change the file name, and then the excel function move the file to a filepath depending on the filename after we've renamed it.
I just don't know if it's possible for excel to save as to a new location?
Thanks again,
Bookmarks