Results 1 to 3 of 3

Thread: VBA - Filepath - Inputbox
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Filepath - Inputbox

    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

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Filepath - Inputbox

    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

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,091
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Filepath - Inputbox

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

    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 by dmt32; Jun 21st, 2018 at 07:43 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •