Textbox enter string and re use

Question

I want to be able to take x number of files in a folder and put them all on seperate sheets of the same workbook.

I found this code that works;

Public Sub consolWB()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object



    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\Users\patrickw\Desktop\exceltest"
    Set folder = FSO.GetFolder(folderPath)

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set openWB = Workbooks.Open(wb)
            For Each ws In openWB.Worksheets
                ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)


            Next ws
            openWB.Close
        End If
    Next wb

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With


End Sub

However, this only works because I have set the target filepath for it to look in.

I want to create a button that prompts the user for a filepath, and then uses that filepath to bring in the files.

I think I have to make the input a string, then use this variable in the code above.

is this possible?

In the example above, the new sheets are just called 'sheet1...' is it possible to name these myself?

Thanks

Do you want them to input into a box what they want, input into a cell, or bring up a box where they can browse and select a folder? Or something else. — QHarr, Oct 13 '17 at 12:06
I initially wanted a drop down box that they could then select the correct file. Whichever way I do it, I have to be able to use that string as a lookup in my other macro, if that makes sense? — Tony Hibbert, Oct 13 '17 at 12:07
Here is an example https://stackoverflow.com/questions/25153342/open-a-workbook-using-filedialog-and-manipulate-it-in-excel-vba — QHarr, Oct 13 '17 at 12:10
I have just tried that and that actually opens the file. I just want the user to enter a file directory as a string. Then I want to use this string in my other 'consolidate' macro that will then bring in the 3 files from that folder — Tony Hibbert, Oct 13 '17 at 12:15
If you stopped to think for a minute instead of blindly copy/pasting code from random strangers on the Internet, you'd realize that it's opening the file because of the `Workbooks.Open` call. *Look at the code* and *understand it*, and *adapt it* to your needs; don't just patchwork-paste your code with random bits & pieces gathered on the web. @QHarr's example is *exactly* what you need. — Mathieu Guindon, Oct 13 '17 at 13:32

score 1 · Accepted Answer · edited Oct 13 '17 at 13:28

Have the following inside your button_click event and then use the path variable in your other code.

Dim path As String
path = InputBox("Enter a file path")

If you need to access the variable outside the form then you can declare path as a module-level Public field:

Public path As String

Private Sub CommandButton1_Click()

 path = InputBox("Enter a file path")

End Sub

Private Sub CommandButton2_Click()
 Debug.Print path
End Sub

Or better, encapsulate it with a property if path isn't supposed to be assignable from outside the form:

Private path As String

Public Property Get SelectedPath() As String
    SelectedPath = path
End Property

Private Sub CommandButton1_Click()
    path = InputBox("Enter a file path")
End Sub

That way, code outside the form can read the SelectedPath, but cannot write to the private field or modify the property's value.

That said consider using a FileDialog instead of an InputBox, for better user experience and easier input validation.

This is what I wanted! I click the first button and enter the directory, however, when I click the second button I get a run-time error 5 message Is the new line in the 'consolidate' macro now; folderPath = path — Tony Hibbert, Oct 13 '17 at 12:36
Great. Please accept as answer if it meets what you want so as to close. I have added in how to pass between your buttons. — QHarr, Oct 13 '17 at 12:47

score 1 · Answer 2 · answered Oct 13 '17 at 12:52

1

In this way, the user only need to click, click and click. You don't have to check whether the input folder path is correct or not.

Sub GetFolder()
    Dim FD As Office.FileDialog
    Dim FolderPath as string
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    FD.Show

    FolderPath = FD.SelectedItems(1)
    Msgbox FolderPath
End Sub

answered Oct 13 '17 at 12:52

newacc2240

1,425
1
6
14

Thanks, I just tried that but how do I incorporate this into my initial code? – Tony Hibbert Oct 13 '17 at 13:07
The easiest way is to replace `folderPath = "C:\Users\patrickw\Desktop\exceltest"` with my code. – newacc2240 Oct 13 '17 at 13:31
1

Upvoted, but this should be a `Private Function` that returns a `String`. – Mathieu Guindon Oct 13 '17 at 13:34

Textbox enter string and re use

2 Answers2