Darren Smith
Well-known Member
- Joined
- Nov 23, 2020
- Messages
- 631
- Office Version
- 2019
- Platform
- Windows
I have previously done this code to open workbooks in a specified folder using the workbook cell value.
Now I want to use a value from the user form see my attempt below
Now I want to use a value from the user form see my attempt below
VBA Code:
Private Sub Open_Old_Jobcard_Click()
Dim JobCardNo As New DataObject
JobCardNo.SetText Me.Open_Old_Jobcard.Text
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PID As Double
Dim strRootPath As String
Dim objFile As Scripting.File
Dim wb As Workbook
Dim myfilename As String
Dim Test As String
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
If Target.Cells.Row = 1 And Target.Cells.Column = 2 Then
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
strRootPath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB CARDS\1 - ARCHIVED JOB CARDS\" & Int((ActiveSheet.Range("C1") - 1) / 50) * 50 + 1 & "-" & Int((ActiveSheet.Range("C1") - 1) / 50 + 1) * 50 & "\" & ActiveSheet.Range("C1") & "\P" & ActiveSheet.Range("C1")
PID = Shell(strExpExe & strArg & strRootPath, 3)
End If
If Target.Cells.Row = 1 And Target.Cells.Column = 1 Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB CARDS\1 - ARCHIVED JOB CARDS\" & Int((ActiveSheet.Range("C1") - 1) / 50) * 50 + 1 & "-" & Int((ActiveSheet.Range("C1") - 1) / 50 + 1) * 50 & "\" & ActiveSheet.Range("C1"))
For Each objFile In objFolder.Files
If Left(objFile.Name, 5) = CStr(ActiveSheet.Range("C1")) And Right(objFile.Name, 4) = "xlsm" Then
myfilename = objFile.Path
End If
Next objFile
Set wb = Workbooks.Open(myfilename)
End If
End Sub
Private Sub JobCardOpen(ByVal Target As Range)
Dim objFile As Scripting.File
Dim wb As Workbook
Dim myfilename As String
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
If Target.Cells.Row = 1 And Target.Cells.Column = 1 Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB CARDS\1 - ARCHIVED JOB CARDS\" & Int((ActiveSheet.Range("C1") - 1) / 50) * 50 + 1 & "-" & Int((ActiveSheet.Range("C1") - 1) / 50 + 1) * 50 & "\" & ActiveSheet.Range("C1"))
For Each objFile In objFolder.Files
If Left(objFile.Name, 5) = ActiveSheet.Range("C1") Then
myfilename = objFile.Path & objFile.Name
End If
Next objFile
Set wb = Workbooks.Open(myfilename)
End Sub