VBA...ThisWorkbook.SaveAs

Posted by Ian on August 13, 2001 9:07 AM

I'm pretty crap at this os bear with me please

on opening a template i'd like the workbook to SaveAs
after some info is required.

So.

What I need is

Private Sub Workbook_Open()

'an input box for adviser name & Batch No.

Range("b3").value = inputbox.value (something like this for the name)
Range("b4").value = inputbox.value (something like this for the batch no.)

Range("b5").Value = Format(Now(), "dddd-dd-mmm-yy")

thisworkbook.saveas (this i have no idea i'll do excel styley) 'needs to be location the Standard_name&" "&adviser_name("b3")&" "&batch(b4)&" "&date(b5)

End Sub

any help or further info let me know

Thanks a MILLION!!!

Ian

Posted by Barrie Davidson on August 13, 2001 9:39 AM

Hi Ian, try this.

Private Sub Workbook_Open()
Advisor_Name:
Range("b3").Value = InputBox("Enter Advisor Name")
If Range("b3").Value = "" Then GoTo Advisor_Name
Batch_No:
Range("b4").Value = InputBox("Enter Batch No.")
If Range("b4").Value = "" Then GoTo Batch_No
Range("b5").Value = Format(Now(), "dddd-dd-mmm-yy")
'Note, you need to change the first part to standard path and name
'change "C:\Standard_name"
ActiveWorkbook.SaveAs ("C:\Standard_name" & " " & Range("b3").Value & " " & Range("b4").Value & " " & CStr(Range("b5").Value))
End Sub

Note that you need to specify the path and first part of your file name.

Regards,
Barrie

Posted by Ian on August 13, 2001 12:24 PM

Thanks Barrie,

I have a few question more (well I've been asked if Blah Blah Blah, can be done), would you mind having a look at the workbook for me. Don't worry everyone at work knows your the brains behind the org, have requested you're put on the payroll :)

I'll e-mail if ok

Thanks again

Ian

Posted by Barrie Davidson on August 13, 2001 12:38 PM

Go ahead and e-mail the file. I won't be able to look at it until tonight when I get home (it's 2:40 pm here in Winnipeg). Thanks for getting me on payroll; I'll make sure you get your cut of my pay :)

Barrie