PDA

View Full Version : [SOLVED] Save As path help



austenr
12-14-2004, 11:21 AM
Hi Everyone,

Can someone help me in making the following code direct the file to save in the following directory?

V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets


Public Sub CommandButton1_Click()
If OptionButton1 = True Then
ThisWorkbook.SaveAs Range("B5").Value & ".xls"
Unload Me
End If
If OptionButton2 = True Then
ThisWorkbook.SaveAs Range("B5").Value & ".xls"
Sheets.PrintOut
Unload Me
End If
End Sub

Thanks in advance for your help

Ken Puls
12-14-2004, 11:38 AM
Try this:


Public Sub CommandButton1_Click()
dim FilePath as string
FilePath = "V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets\"
If OptionButton1 = True Then
ThisWorkbook.SaveAs FilePath & Range("B5").Value & ".xls"
Unload Me
End If
If OptionButton2 = True Then
ThisWorkbook.SaveAs FilePath & Range("B5").Value & ".xls"
Sheets.PrintOut
Unload Me
End If
End Sub

I haven't tested it, but I think that should work...

HTH,

Zack Barresse
12-14-2004, 11:38 AM
Hi Austen,

'Omitted. Same as Ken's

Although, if you are going to call this location from many different locations in one file (e.g. multiple Sub Routines, UserForms, etc.) you may want to think about declaring it as a Public Const and keeping it Global. This will save you the trouble of calling/Dim'ming it multiple times when once is sufficient.

NateO
12-14-2004, 11:46 AM
Or a private constant (versus variable). E.g.,


Const FilePath As String = "V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets\"

Ken Puls
12-14-2004, 11:51 AM
Nate, can you confirm for me...

I know that the "Const FilePath As String..." would be declared at the top of the module (just under Option Explicit), but before the first (public/private) Sub, but...

It would only work if the FilePath variable was called from a macro within that code module, correct? And declaring it public would allow it to be called from other modules within the same project?

Thanks,

NateO
12-14-2004, 11:57 AM
Hello Ken,

The scope will change pending your declaration.

Procedural scope:


Sub tester()
Const FilePath As String = "V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets\"
MsgBox FilePath
End Sub

Modular Scope:


Private Const FilePath As String = "V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets\"

Sub tester()
MsgBox FilePath
End Sub

Project-wide scope:


Public Const FilePath As String = "V:\B&E Ops Support\Billing And Enrollment\B&E National Unit\National & ASO Profile and COBRA Sheets\"

Sub tester()
MsgBox FilePath
End Sub

Hope this helps. :)

Ken Puls
12-14-2004, 12:06 PM
Perfect! Thanks! :thumb

austenr
12-14-2004, 03:19 PM
Thanks everyone. Works great, as usual. This board really gives great advice. You can consider this SOLVED!!