PDA

View Full Version : Textbox and the standard module



dm28949
02-23-2015, 12:19 PM
This code lists the folders and their attributes. To make it more complete, I need to get user input (which is the id number of employee) and place that input right before the folder path because this macro will be used by multiple users. Below is what I did but it is not working. Any help would be greatly appreciated.




fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
Exit Sub
End If


Set newSheet = ThisWorkbook.Sheets.Add
Cells(1, 1).Value = fldpath
Cells(2, 1).Value = "Path"
Cells(2, 2).Value = "Date Created"
Cells(2, 3).Value = "Date Last Modified"
Cells(2, 4).Value = "Size"




Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(fldpath)
Set SubFolders = folder.SubFolders
For Each SubFolder In SubFolders
j = Range("A1").End(xlDown).Row + 1


Cells(j, 1).Value = ActiveSheet.TextBox1.Text & " " & SubFolder.Path
Cells(j, 2).Value = Format(SubFolder.DateCreated, "MM/DD/YYYY")
Cells(j, 3).Value = Format(SubFolder.DateLastModified, "MM/DD/YYYY")
Cells(j, 4).Value = Format(SubFolder.Size / 1024 / 1024, "0.0 \MB")

Cells(j, 1).Value = ActiveSheet.TextBox1.Text & " " & SubFolder.Path

Bob Phillips
02-24-2015, 05:09 AM
UserId = InputBox("Please supply user id", "User ID Input")
If UserId = "" Then
MsgBox "User ID Not Selected"
Exit Sub
End If

fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
Exit Sub
End If

Set NewSheet = ThisWorkbook.Sheets.Add
Cells(1, 1).Value = fldpath
Cells(2, 1).Resize(, 4).Value = Array("Path", "Date Created", "Date Last Modified", "Size")

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(fldpath)
Set SubFolders = folder.SubFolders
For Each SubFolder In SubFolders
j = Range("A1").End(xlDown).Row + 1

Cells(j, 1).Value = ActiveSheet.TextBox1.Text & " " & SubFolder.Path
Cells(j, 2).Value = Format(SubFolder.DateCreated, "MM/DD/YYYY")
Cells(j, 3).Value = Format(SubFolder.DateLastModified, "MM/DD/YYYY")
Cells(j, 4).Value = Format(SubFolder.Size / 1024 / 1024, "0.0 \MB")

Cells(j, 1).Value = ActiveSheet.TextBox1.Text & " " & UserId & " " & SubFolder.Path