Hello, does any one know how to pu

[hf28]

Hello,
does any one know how to put a command button on to Excel form. On the click event on that button, I'd like to save the Excel document in the certain folder with the hard coded address.

I'll appreciate any help.

 

[SkipVought]

Hi,

Is this a UserForm or a Worksheet that you want to put a button?

I'm guessing that it's a Worksheet that you are calling a form.

Right click in the toolbar and select Control Toolbox.

From the Control Toolbox toolbar, select the CommandButton and place it on your sheet.

Doubleclick the button on your sheet and the VB Editor will open to the CommandButton1_Click event. Insert your SaveAs method code.

Meanwhile, back at the worksheet...
with the command button selected, select the Properties from the toolbar and change the Caption and whatever other properties need changing.

Close the toolbar, SAVE YOUR WORK!

Now yer ready to rock 'n' roll! ;-)


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hf28]

I did everything like you recommended with the button.

I wrote the following function to save my Excel Workbook:

Private Sub CommandButton1_Click()

ActiveWorkbook.SaveAs Filename:="C:\TrialExcel\CheckSave" & Date & ".xls"

End Sub

I got the error '1004':

The file name or path does not exist

The file you're trying to open has been used another application

The name of the workbook is the same

See, I'll have to have the same Excel doc, which, if updated, will be saved in the same folder with the same name. The only difference in the name will be the Date and Time stamp.

What should I do to avoid the error

 

[SkipVought]

Hi,

Your problem is in the fomat of the filename --

do NOT use slashes in the date!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hf28]

Thanks.
So to replace / and : for date and time, should I use InStr function right within the address?

 

[RobBroekhuis]

Use something like

format(Date,"dd-mm-yy&quot



Rob

 

[hf28]

Thanks, Rob.

If I do

Private Sub CommandButton1_Click()
WSheet.SaveAs ("C:\Excel\" & Format(Date, "dd-mm-yy&quot & "_TEST.XLS&quot
End Sub

I get an ERROR 424 without explanations.

 

[Prestaul]

If you change that from WSheet.SaveAs (...) back to ActiveWorkbook.SaveAs (...) then you should be fine.

You are getting this error because WSheet is not a valid object.

 

[hf28]

Thank you guys. It works just great!!!

All of you were very helpful.

Appreciate it.