+ Reply to Thread
Results 1 to 8 of 8

Save individual files, based on individual cell data

  1. #1
    Registered User
    Join Date
    06-10-2022
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Red face Save individual files, based on individual cell data

    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,
    Last edited by twgTjP; 06-10-2022 at 04:24 AM.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Save individual files, based on individual cell data

    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?

  3. #3
    Registered User
    Join Date
    06-10-2022
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Save individual files, based on individual cell data

    Quote Originally Posted by cytop View Post
    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,

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Save individual files, based on individual cell data

    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

  5. #5
    Registered User
    Join Date
    06-10-2022
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Save individual files, based on individual cell data

    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,

  6. #6
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Save individual files, based on individual cell data

    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..

  7. #7
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Save individual files, based on individual cell data

    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.
    Last edited by cytop; 06-10-2022 at 11:12 AM. Reason: Corrected where the Path gets a trailing '\'

  8. #8
    Registered User
    Join Date
    06-10-2022
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Save individual files, based on individual cell data

    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
    Attached Files Attached Files
    Last edited by twgTjP; 06-10-2022 at 11:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need to save individual sheets in a workbook as different files
    By aparnawangu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2018, 09:46 PM
  2. VBA / Macro / Mail Merge / Save as individual pdf files
    By Perav in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-06-2015, 10:41 AM
  3. Replies: 2
    Last Post: 08-18-2014, 08:39 PM
  4. Export Individual Rows To Individual Text Files?
    By jimmer18 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-13-2014, 11:43 AM
  5. Outputting individual cells to individual text files
    By db9429 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2013, 03:39 PM
  6. [SOLVED] Creating a Macros to save individual columns as text files
    By lorigies in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-04-2012, 05:18 PM
  7. save many worksheets as individual csv files?
    By applegatecz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2010, 03:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1