VBA - Filepath - Inputbox

p4nny

Board Regular
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

Board Regular
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
 

dmt32

Well-known Member
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:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top