vba instruction for file saving

[umpire_43]

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

 

[JLGWhiz]

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

 

[Mike]

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.

 

[umpire_43]

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

 

[umpire_43]

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

 

[Rick Rothstein \(MVP - VB\)]

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