VBA - Filepath - Inputbox

p4nny

Board Regular
Joined
Jan 13, 2015
Messages
246
Hi

I have the following path in vba which at the moment is static:

"C:\Completed Scorecards\May"

Rather than May being fixed, is it possible to prompt the user to key this in using an InputBox so the user can specify June, July etc?

thank you
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,
A very quick way of doing this is

Code:
Sub asksave()
Dim sbasepath As String
Dim sfolder As Variant

sfolder = InputBox("Enter the month")
sbasepath = "C:\Completed Scorecards\" 'set your basepath if you want to use this

spath = sbasepath & sfolder
If Dir(spath, vbDirectory) = "" Then
        MkDir spath 'Create directory if it does not exist
  
  End If
  
      'below I have changed this section to just display the path,
    'ActiveWorkbook.SaveCopyAs Filename:=sPath & sFilename
    MsgBox spath & sFilename

End Sub

Another idea is to create the month without asking them (presuming that they are saving in the current month at all times?)

This code below will create a folder C:\Completed Scorecards\June
if you keep the section I have said "delete this section is no sub folder" then it will create a the path C:\Completed Scorecards\June\21 June 2018\

This means that if you are saving each day it will create a new folder (once per day only) for people to save files into... hope it all makes sense:

Code:
Sub save()
Dim syear As String
Dim smonth As String
Dim sbasepath As String

sbasepath = "C:\Completed Scorecards\" 'set your basepath if you want to use this

    syear = Year(Now) 'Set this year
    If Len(Month(Now)) = 1 Then
    smonth = "0" & Month(Now) 'Add leading zero
    Else
    smonth = Month(Now)
    End If
    spath = sbasepath & MonthName(Month(Now)) & "\" 'set the base level + month name + year c:\users\test\May
    'sPath = sbasepath & MonthName(Month(Now)) & " " & syear & "\" 'set the base level + month name + year  - c:\users\test\may 2016
    
    'lets check if the directory exists, if not then lets create it
    If Dir(spath, vbDirectory) = "" Then
        MkDir spath 'Create directory if it does not exist
    End If
   
   '/////////// DELETE THIS SECTION IF YOU DONT WANT ANOTHER SUB FOLDER CREATED WITH TODAYS DATE 21 JUNE 2018 /////////////////
   
   'Lets create a day folder in the month folder with todays date, if it does not already exist
    spath = spath & Format(Now, "DD") & " " & MonthName(Month(Now)) & " " & syear & "\"   'Set path incl. month
    If Dir(spath, vbDirectory) = "" Then
        MkDir spath 'Create directory if it does not exist
    End If
    
    '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    
    
      If Len(Dir(spath & sFilename)) = 0 Then
      
      'below I have changed this section to just display the path,
    'ActiveWorkbook.SaveCopyAs Filename:=sPath & sFilename
    MsgBox spath & sFilename
End If
End Sub
 
Upvote 0
Hi,
you could try and add following in to your code & see if does what you want

Rich (BB code):
Dim GetMonthName As Variant, Default As Variant
Dim ValidMonthName As Boolean
Dim i As Integer
Do
Default = MonthName(Month(Date), False)
GetMonthName = InputBox("Enter the Month Name", "Month Name", Default)
'cancel pressed
    If StrPtr(GetMonthName) = 0 Then Exit Sub
    For i = 1 To 12
    If GetMonthName = MonthName(i, False) Then ValidMonthName = True
    Next i
 Loop Until ValidMonthName
 
 Folder = "C:\Completed Scorecards\" & GetMonthName

solution checks user has entered a valid Month name. If you use abbreviated month names change False values shown in RED to True.

An alternative suggestion would be to use the built-in FileDialogFolderPicker.

Hope Helpful

Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top