VBA - Filepath - Inputbox

[p4nny]

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

 

[bugmonsta]

Hi,
A very quick way of doing this is

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:

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

 

[dmt32]

Hi,
you could try and add following in to your code & see if does what you want

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