CODEWITHSUNDEEP
excelvba
Tony Hibbert
Tony Hibbert

Reputation: 101

Textbox enter string and re use

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

Upvotes: 0

Views: 54

Answers (2)

QHarr
QHarr

Reputation: 84465

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.

Upvotes: 1

newacc2240
newacc2240

Reputation: 1425

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

Upvotes: 1

Related Questions