Save individual files, based on individual cell data

**twgTjP** · 06-10-2022, 04:11 AM

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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,

**cytop** · 06-10-2022, 05:43 AM

These lines

Please Login or Register  to view this content.

Will error. The variables are defined as String you and you are trying to assign a multicell range (I think) - that needs a Variant. But even then, you only need the value from 1 cell... so perhaps a little more explanation?

**twgTjP** · 06-10-2022, 05:51 AM

Originally Posted by cytop

These lines

Please Login or Register  to view this content.

Will error. The variables are defined as String you and you are trying to assign a multicell range (I think) - that needs a Variant. But even then, you only need the value from 1 cell... so perhaps a little more explanation?

Hi,

Thanks for the response.

Basically it's just a placeholder to remind myself that I need to assign a value to the variable. I have no idea how to use range, so just ignore that

what I want the variables to do is take the value of one cell, and use that variable in the function to save the file, and then repeat with a new value from the cell below. - Basically, a list of values in sequence?

So in essence

Attachment 783477

Set variable "Place" to England and "Date" to 310522
Save file as England 310522 to directory C//blahblah/documents/"England" (based on the variable)
-> repeat, collect the next variables Scotland 250422, Save, repeat ...

Thanks,

**cytop** · 06-10-2022, 06:05 AM

You can make up any name you like, just as long as it is a valid filename... and all referenced directories exist.

Please Login or Register  to view this content.

Would also suggest you use a YYMMDD format for the date, makes it a lot simpler if viewing a sorted list of the files.

All typed freehand so untested

**twgTjP** · 06-10-2022, 07:01 AM

Hi,

Thank you very much for the reply. That all makes sense and I will sew that in.
RE Referenced directories - yes they do all exist. I've come into the role with an established directory and format, it's just that everyone is saving these files manually.

Thanks for the advice on the YYMMDD format. I agree, but I'm crudely reversing this so it's in DDMMYY format (as above, this is already established, and my colleagues would need to be "on board" with having to look at a different date format, which might be a stretch for some of them...) The files we receive are actually come as 220531 format, but I've got a series of LEFT, MIDDLE, RIGHT formulas, along with a CONCAT to make it 310522 !

Sorry to be demanding, but I could really use some help to understand the following:

1) With your code quoted above, how can I sequentially use a list as a variable? Is there an equivalent of something like Place = Column A? Or Place = A$ ? Would this be a different type of variable to "String" ?

2) assuming that is possible, is there a loop type function that can allow me to repeat part of the macro to go down the list automatically?

E.g.

Please Login or Register  to view this content.

Or could you do something like

Please Login or Register  to view this content.

Would the loop atuomatically progress through the values of "A$ " and "B$" ?

Sorry my explanation is so crude. I guess I'm trying to run before I can walk to an extent!

Thanks,

**cytop** · 06-10-2022, 08:12 AM

I could give examples but they would only be examples, leaving you to edit and refine as needed. It would probably be a whole lot faster if you uploaded a sample workbook (See yellow banner above). Just need 5 or 10 anonymised rows. Much preferred if the layout/format of the sheet is exactly as you are using..

**cytop** · 06-10-2022, 08:40 AM

And I could also waste (hopefully not) 5 or 10 minutes to cobble something together. There are a lot of assumptions here, in particular I don't know what is part of the root file name and what are directories, so have assumed Contractor is a directory and everything else goes to make up the filename, each is separated by '-'. That's probably wrong, but I have no other info

Please Login or Register  to view this content.

**twgTjP** · 06-10-2022, 11:18 AM

Hi,

Thanks again for your patience and replies.

I've attached the format I'm working with currently. It's very basic, but I can re-arrange and reformat as needed once I have it working.

In Blue - the file names exported directly from the drop folder.
I've then split this to the right of the bordered cell (F) into individual data from the filenames
The Concat date is purely to rearrange the date format to name the file when re-saving

So,
Based on everything in bold - Ideally I want these columns to set variables that influence both; The file name when saved, and where the file is saved

Row 5 as an example, I would like the output to be:

Please Login or Register  to view this content.

Thank you

(Edit)
err, I guess it would help to clarify the file names that imported
E.G.
220510_0000900727_001_England_C_EngineeringReport.pdf
The 220510 is obviously date format
0000900727 has no relevance and would be ignored
001, England, C are all things that would change the file name and destination
Engineeringreport is irrelevant as well

EDIT 2
I started my example in Column B.. apologies