Results 1 to 6 of 6

Thread: Inputbox in file path

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Inputbox in file path

    Hi...I have a code which splits worksheets into individual workbooks and saves each workbook.
    I have the file path in the code but the folder will change each month so I added an inputbox. But the problem I have is that the inputbox prompts for every workbook. I just want to set the same path for all the workbooks it saves.
    The code for the filepath is [ fname = "C:\Reports\" & inputbox("Enter Folder") & "\" & wb.Sheets(1).Name & ".xlsx" ]

    Any suggestions please?

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Inputbox in file path

    You need to ask the user for the path at the beginning of the macro, before you start the separate and save process. Save this to a variable that you will use to save the file.

    Post the full code for the macro and we can find where to put it.

    Example:

    Code:
    Option Explicit
    
    Sub SaveSheets()
    
        Dim strFolderName As String, fname as String
    
        strFolderName = InputBox("enter Folder")
    
        ' Code to save sheets starts here
    
        ' Set File Name
        fname = "C:\Reports\" & strFolderName & "\" & wb.Sheets(1).Name & ".xlsx"
    
        ' Additional Save code here
    
    End Sub
    Last edited by jdc2000; Jan 2nd, 2018 at 02:16 PM.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Inputbox in file path

    you could also use a file dialog

    Code:
    with application.filedialog(mofiledialogfolderpicker)
      .initialfilename = "c:\reports"
      .show
      myfolder = .selecteditems(1)
    end with
    then just append the filename to myfolder
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Inputbox in file path

    hi this is the last part of the code where the workbooks are saved. I'm not quite sure where to add the above code
    Code:
           Range("E5").ClearContents
            sht.Move
            Set wb = ActiveWorkbook
            wb.Sheets(1).Name = Mid(wb.Sheets(1).Name, 14, 4)
            ActiveSheet.Cells(1, 1).Select
            fname = fname = "C:\Reports\" & inputbox("Enter Folder") & "\" & wb.Sheets(1).Name & ".xlsx"
            If Len(Dir(fname)) > 0 Then Kill fname       '  used for testing, saves getting file already exists dialog
            wb.SaveAs fname
            wb.Close
        End If
    Next
    
    End Sub

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Inputbox in file path

    move the inputbox (or dialog) above the for (which you have not shown)

    and change like
    Code:
    fname = fname = "C:\Reports\" & varfolder & "\" & wb.Sheets(1).Name & ".xlsx"
    where varfolder is the return from inputbox
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Inputbox in file path

    Thanks Pete. Works great.
    Last edited by fusion001; Jan 3rd, 2018 at 04:49 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width