save multiple files with different names in other drive using macr

[Lois]

Is it possible to have an excel macro to automatically save many files in
another drive with the specific name of each file in its own folder using a
simple code? Currently, we use an excel macro to format text files to an
excel file with subtotals and they are saved to their own unique folder.
There are approx. over 1000 files that are run through this macro each week.

Example, ABC company with customer # XXX00003 has 10 files that need to be
saved under drive M:\ABC Company 2007\ and DEF company with customer #
XXX00004 has 20 files that need to be saved under drive M:\DEF Company 2007\,
and so on. Currently, the files are saving to their own folders through the
macro using the function: where 'ABC Company' 'XXX0 0004', however, we need
to manually add this 'where' code for each group's name and customer# to the
macro.

I want to be able to not have to add in each new customer's name and number
code since new customers are being added to the macro constantly. Any help is
very much appreciated. Thanks, LW

 

[Conan Kelly]

Lois,

Yes it is possible, but I need more info.

Where is "ABC Company" coming from? From a file name? From a sheet name?

How about "customer # XXX00003"? Is that coming from a file name or sheet
name?

I understand about the folders/locations you want resulting files save
at/in, but what do you want the resulting file name to be?

Do you want this code to loop through all files in one folder and then
create appropriate folders and save resulting files in those folders? Are
the source files going to be left alone, moved, copied, deleted?

Please give examples of:
--Source File Names
--# of Sheets/Sheet Names in each source file
--Source File Format
--Destination File Names
--# of Sheets/Sheet Names in each destination file
--Destination file format
--etc...

HTH,

Conan

 

[Lois]

Hi Kelly- The files are named with the customer # in the name. Ex: file
ENR0200XXX00003 ..would be for customer XXX00003, ABC Company. the
destination folder would be called ABC Company. The company name is not part
of the file name. The resutling file name should not change. It needs to
remain named as ENR0200xxx00003. There are existing approriate folders for
each customer and I want the files to go to each folder of its corresponding
name. The source files will be left alone and remain.

Source File name: ENR0200XXX00003.txt
Sheet names have the same as above. I'm assuming you mean the tab name of
the worksheet? If so, it would ENR0200XXX00003.
Source File Format= txt file
Destination file format= ABC Company folder as excel format

I hope this helps and thanks!



Thanks-

 

[Conan Kelly]

Lois,

This will take some doin', but still need some more info.

Do each of the source files get save in each of the destination
folders.........or do some files belong to ABC Co, some to DEF Co, etc.? If
so, how do you determine which file belongs to which company?

Each source file is a text file? ...tab delimited? So each text file will
need to be opened in XL, then saved as an XL Workbook file in a new
location, correct?

Is there any chance that files could exist in the destination folders with
the same names? If I "save as" and the file already exists, do I overwrite
the existing file, do I cancel and close the file w/o making any changes,
etc...?

There are probably going to be a few more questions, but I can't think of
any right now.

conan

 

[Lois]

Lois,

This will take some doin', but still need some more info.

Do each of the source files get save in each of the destination
folders.........or do some files belong to ABC Co, some to DEF Co, etc.? If
so, how do you determine which file belongs to which company?

the latter answer. The customer number determines which company folder it
belongs to. The code we use is Case "XXX0 0003", l = "ABC Company". So, all
the ABC Company files have the customer number of XXX00003 in the file names.

Each source file is a text file? ...tab delimited? So each text file will
need to be opened in XL, then saved as an XL Workbook file in a new
location, correct?

My bad, I didn't explain well enough. The files begin as text ,but we run
them through this macro and it changes them to excel format before it saves
to the destination folders.

 

[Lois]

One more thing, the beginning of the code is below.

Dim idPth As String
Let idPth = strXXX & " " & txtGroupNumber
Let yyr = strYear

 

[Conan Kelly]

Lois,

If you want to email me directly, that is fine.....just remove the "NOSPAM"s
from the email address.

I went back and reread your original post, and I realized that we are
thinking of 2 different things.

Your code is ALREADY saving to a different location. You are just looking
for a more automatic way of handling new clients (ie. creating new folders
and saving the files in those new folders).

The only way that can happen is if the new folder name can be
determined/decrypted from the file name.

There is another way that I'm thinking of that might be less hassle than
adding new client info to the code each time. Maybe you could create an XL
file.....a "Master List" of Clients and their account numbers. This master
list would only need to be 2 columns......1 for the account number and one
for the name of the new folder (or the whole path of the new folder). This
master list would have to be updated with new clients.

Then you could set your code to open this master list, import the account
numbers and folder names (or paths) into a string array variable. It could
loop through every folder/path in that list and check to see if it exist,
and create that folder if it doesn't exist. Then that array variable could
be used in place of your SELECT CASE statement. It can search through all
of the account numbers and return the folder/path it needs to save to.

But, keep in mind, this master list would still need to be updated. It just
might be easier to update one XL spreadsheet rather than updating the code
each time.

You might also run into issues if more than one person will update the
master list and/or run this macro (ie. The master list may not be updated
when someone else runs the code).

Let me know what you think,

Conan