Save As path help

[austenr]

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]

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]

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]

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]

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]

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]

Perfect! Thanks!

[austenr]

Thanks everyone. Works great, as usual. This board really gives great advice. You can consider this SOLVED!!