Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
I have created a workbook that contains several spreadsheets--one for each
month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
You can use the dir function to determine if a file (or directory) exists.
One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
Thank you for your advice. Do you have any other suggestions on a universal
place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
You could use either thisworkbook.path or application.path... Probably
thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
SJC, maybe Application.DefaultFilePath
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "SJC" wrote in message ... Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
I think the 'thisworkbook.path' would work--can I still designate a name for
the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
Ok I am just a little bit lost in exactly what you want... The file you are
sending is going to create a new workbook and you want to save that workbook somewhere that you will be able to get to it. Paul had a great suggestion on the path. This one will work and should not cause any problems... How about something like this dim strPath as String dim strFileName as string strpath = Application.DefaultFilePath & "\" strfilename = format(now(), "mmm") & "MyFile.xls" activeworkbook.saveas strpath & strfilename That is untested but it should be failry close... HTH "SJC" wrote: I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
Maybe just write the file and mail it, then delete the file - then path
isn't an issue: Ron de Bruin http://www.rondebruin.nl/sendmail.htm http://msdn.microsoft.com/library/en...odc_xlmail.asp If your users don't all use outlook (not outlook express) then this might be less desirable. Ron has instructions on using CDO, but I don't think it is distributed with the Windows 9x operating systems. -- Regards, Tom Ogilvy "SJC" wrote in message ... I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
Your suggestions seem to work beautifully--thank you all for your help!
"Jim Thomlinson" wrote: Ok I am just a little bit lost in exactly what you want... The file you are sending is going to create a new workbook and you want to save that workbook somewhere that you will be able to get to it. Paul had a great suggestion on the path. This one will work and should not cause any problems... How about something like this dim strPath as String dim strFileName as string strpath = Application.DefaultFilePath & "\" strfilename = format(now(), "mmm") & "MyFile.xls" activeworkbook.saveas strpath & strfilename That is untested but it should be failry close... HTH "SJC" wrote: I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
I hope I am not being a pest, but I had one last small question--your command
lines worked perfectly, but the letters "Mar" appear in front of the file name--is there any way to get rid of that? Thanks once again. "Jim Thomlinson" wrote: Ok I am just a little bit lost in exactly what you want... The file you are sending is going to create a new workbook and you want to save that workbook somewhere that you will be able to get to it. Paul had a great suggestion on the path. This one will work and should not cause any problems... How about something like this dim strPath as String dim strFileName as string strpath = Application.DefaultFilePath & "\" strfilename = format(now(), "mmm") & "MyFile.xls" activeworkbook.saveas strpath & strfilename That is untested but it should be failry close... HTH "SJC" wrote: I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
strfilename = format(now(), "mmm") & "MyFile.xls"
format(now(),"mmm") puts the 3 letter abbreviation for the month in front of the filename. strfilename = "MyFile.xls" Would not do that, but then, wouldn't all your file names be the same? (it they are, the you would want to overwrite the existing file). -- Regards, Tom Ogilvy "SJC" wrote in message ... I hope I am not being a pest, but I had one last small question--your command lines worked perfectly, but the letters "Mar" appear in front of the file name--is there any way to get rid of that? Thanks once again. "Jim Thomlinson" wrote: Ok I am just a little bit lost in exactly what you want... The file you are sending is going to create a new workbook and you want to save that workbook somewhere that you will be able to get to it. Paul had a great suggestion on the path. This one will work and should not cause any problems... How about something like this dim strPath as String dim strFileName as string strpath = Application.DefaultFilePath & "\" strfilename = format(now(), "mmm") & "MyFile.xls" activeworkbook.saveas strpath & strfilename That is untested but it should be failry close... HTH "SJC" wrote: I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
Yes, I get it now. Thank you so much for your help I think I finally have
it right. "Tom Ogilvy" wrote: strfilename = format(now(), "mmm") & "MyFile.xls" format(now(),"mmm") puts the 3 letter abbreviation for the month in front of the filename. strfilename = "MyFile.xls" Would not do that, but then, wouldn't all your file names be the same? (it they are, the you would want to overwrite the existing file). -- Regards, Tom Ogilvy "SJC" wrote in message ... I hope I am not being a pest, but I had one last small question--your command lines worked perfectly, but the letters "Mar" appear in front of the file name--is there any way to get rid of that? Thanks once again. "Jim Thomlinson" wrote: Ok I am just a little bit lost in exactly what you want... The file you are sending is going to create a new workbook and you want to save that workbook somewhere that you will be able to get to it. Paul had a great suggestion on the path. This one will work and should not cause any problems... How about something like this dim strPath as String dim strFileName as string strpath = Application.DefaultFilePath & "\" strfilename = format(now(), "mmm") & "MyFile.xls" activeworkbook.saveas strpath & strfilename That is untested but it should be failry close... HTH "SJC" wrote: I think the 'thisworkbook.path' would work--can I still designate a name for the file within the programming using this command? If so, how would that look? I can successfully place the 'thisworkbook.path' where it belongs, but I can't figure out a way in which I could name the new file. When the macro is run, it transfers a portion of the data into the new file. The new file is what I would like to rename to a universal path. I apologize--some of this is new to me. "Jim Thomlinson" wrote: You could use either thisworkbook.path or application.path... Probably thisworkbook, which is the directory where they have saved your workbook. If it is not saved then you could run into a problem... In that case application.path which returns where the excel program is saved... End users may not like this... HTH "SJC" wrote: Thank you for your advice. Do you have any other suggestions on a universal place to put this file? Unfortuneately, there will be about 200 users from dozens of organizations, so I will not be able to confirm the administration of the c: drive for all of them. Is there a way to get to the "My Documents" folder or "Desktop" that is universal to all operating systems? "Jim Thomlinson" wrote: You can use the dir function to determine if a file (or directory) exists. One thing to be aware of is that it is possible that some users will not be able to create files in their root of C. Often administrators lock the root down to keep users from accidentally mucking up their systems. That is something to confirm before you begin. HTH "SJC" wrote: I have created a workbook that contains several spreadsheets--one for each month of the year. I have about 200 novice users that will be recording some monthly data and sending the report to me. Within each spreadsheet, I have included a macro which will export what data I need and save it into a folder so that they can easily find the file and email it to me. The users have operating systems anywhere from Windows 95 to XP, so it was difficult to find a folder to have these files saved to within the macro that fit all of the operating systems. So, in the macro, I opened a new folder and saved the file under the new folder to the C:\ drive, which would be universal to all operating systems. So the way I have it set up is to have the May file be the macro that opens up the new folder and stores the first file. Then future month macros saves the new files to that same folder. The problem is that if I have a user that doesn't use the program until August, that macro obviously won't create the folder and cannot find the right path. Is there a way to program the macro to create the new folder only if it hasn't been previously created? Any other options to solve this problem? I apologize if I am sounding confusing--I am fairly new to this macro business. Please offer any recommendations. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
Macro question | Excel Discussion (Misc queries) | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question | New Users to Excel | |||
Macro Question | Excel Programming |