|
save multiple files with different names in other drive using macr
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 |
save multiple files with different names in other drive using macr
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" wrote in message ... 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 |
save multiple files with different names in other drive using
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" wrote: 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" wrote in message ... 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 |
save multiple files with different names in other drive using
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
save multiple files with different names in other drive using
"Conan Kelly" wrote: 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. 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...? Again, I didn't explain well enough. No, Each file name ends with a date the file ran, ex ENR0200XXX0000311272007, with 11272007 being the date, therefore, the files would be named different each week and are saved as a new file. There are probably going to be a few more questions, but I can't think of any right now. conan "Lois" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
save multiple files with different names in other drive using
One more thing, the beginning of the code is below.
Dim idPth As String Let idPth = strXXX & " " & txtGroupNumber Let yyr = strYear "Conan Kelly" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
save multiple files with different names in other drive using
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 "Lois" wrote in message ... "Conan Kelly" wrote: 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. 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...? Again, I didn't explain well enough. No, Each file name ends with a date the file ran, ex ENR0200XXX0000311272007, with 11272007 being the date, therefore, the files would be named different each week and are saved as a new file. There are probably going to be a few more questions, but I can't think of any right now. conan "Lois" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com