Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
Hi,
I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
The following does exactly what you described, but I doubt that
is what you really want. Post back with more detail if I am right. Sub dostuff() myVar = ActiveSheet.Range("A1").Value '1. info retrieved Application.Dialogs(xlDialogSaveAs).Show '2.Opens Dialog Box 'for user to select 'or enter file name. MsgBox "File Saved As " & ActiveWorkbook.Name 'Rest of 2. And '3. File Saved End Sub "umpire_43" wrote: Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
On Apr 16, 2:45*pm, umpire_43
wrote: Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith Something like this? 'Ask for a path and then save a copy of the workbook in that path 'using the file name contained on the active worksheet in cell "B3" myPath = Application.InputBox("Enter a path to save the file") If Mid(myPath, Len(myPath) - 1, 1) < "\" Then myPath = myPath & "\" ActiveWorkbook.SaveCopyAs myPath & ActiveWorkbook.ActiveSheet.Range("B3").Value --Best regards, --Mike Jr. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
Hi.
thanks for replying back so quick!!! I think its close. Retrieve the information looks right. I know that vba has a filesave dialog box built in the system. Will the filename be automatically put into the space provide without the user entering it? I don't want them to enter anything into the dialog box except for selecting the directory where to save it The user will have to click on save or Cancel. does that make sense? thanks for your help again :) keith "JLGWhiz" wrote: The following does exactly what you described, but I doubt that is what you really want. Post back with more detail if I am right. Sub dostuff() myVar = ActiveSheet.Range("A1").Value '1. info retrieved Application.Dialogs(xlDialogSaveAs).Show '2.Opens Dialog Box 'for user to select 'or enter file name. MsgBox "File Saved As " & ActiveWorkbook.Name 'Rest of 2. And '3. File Saved End Sub "umpire_43" wrote: Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
Hi Mike,
Thanks for your quick reply. Its a good idea but i tried at first but i found that vba has built in dialog boxes that will be more user friendly. My theory less typing i have to do....less errors occur for me and my other users :). thanks again Mike. thanks for replying to my message "Mike" wrote: On Apr 16, 2:45 pm, umpire_43 wrote: Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith Something like this? 'Ask for a path and then save a copy of the workbook in that path 'using the file name contained on the active worksheet in cell "B3" myPath = Application.InputBox("Enter a path to save the file") If Mid(myPath, Len(myPath) - 1, 1) < "\" Then myPath = myPath & "\" ActiveWorkbook.SaveCopyAs myPath & ActiveWorkbook.ActiveSheet.Range("B3").Value --Best regards, --Mike Jr. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
It sounds like you already have the filename that the save should take place
to. If that is the case, you can pass the filename as the first argument to the Save operation... Application.Dialogs(xlDialogSaveAs).Show "YourFileName.txt" If you know what directory you would like to start the dialog box from, just qualify the filename with that path. For example... Application.Dialogs(xlDialogSaveAs).Show "c:\Temp\YourFileName.txt" will open the SaveAs dialog to the Temp directory on drive c: and put YourFileName.txt into the filename field. Rick "umpire_43" wrote in message ... Hi. thanks for replying back so quick!!! I think its close. Retrieve the information looks right. I know that vba has a filesave dialog box built in the system. Will the filename be automatically put into the space provide without the user entering it? I don't want them to enter anything into the dialog box except for selecting the directory where to save it The user will have to click on save or Cancel. does that make sense? thanks for your help again :) keith "JLGWhiz" wrote: The following does exactly what you described, but I doubt that is what you really want. Post back with more detail if I am right. Sub dostuff() myVar = ActiveSheet.Range("A1").Value '1. info retrieved Application.Dialogs(xlDialogSaveAs).Show '2.Opens Dialog Box 'for user to select 'or enter file name. MsgBox "File Saved As " & ActiveWorkbook.Name 'Rest of 2. And '3. File Saved End Sub "umpire_43" wrote: Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba instruction for file saving
Keith,
When your users are presented with the dialog where they are to select the destination for the file save, should they see files or only see the folder structure? I understand you want to limit the user to only selecting folders but should they be able to view the files already present? Will the users be able to edit the file name at all or is it entirely extracted from cells on the active sheet or somewhere else? Once the user selects a folder and clicks OK, will they be given the option to Save or Cancel? How are you wanting to trigger the appearance of this dialog? Trying to intercept attempts by the user to click 'File SaveAs' or other methods would be quite a challenge. On the other hand, if the user knows to launch your macro when it's time to save, the task is not that tough. There are several ways to approach this. I've always been partial to the folder picker that is common for all the office applications rather than trying to translate the documentation for the built in xlDialogSaveAs. Steve Yandl "umpire_43" wrote in message ... Hi, I'm wondering if anyone can help me on this? I'm using excel 2003. I have several sheets that have different statements. This is what i want to accomplish through vba. 1) I want the programming to automatically retrieve information from the cell within the sheet that i'm currently working on. (Note: i have several sheets within the workbook) 2) User will select the directory where they want to save the file but the filename will automatically be put into the popup dialog box. 3) Save the file automatically as pertaining to number 1. Is this possible? I hope you can understand. thanks keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Confused here Prevent Saving File - but allow ONLY File SAVEAS Met | Excel Discussion (Misc queries) | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
How do I stop Excel 2000 from saving file history from file that . | Excel Discussion (Misc queries) | |||
saving an excel file as an ASCII text file without delimiters | Excel Programming |