Opening workbook from user form

[Darren Smith]

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

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

 

[Darren Smith]

I`ve also, asked

Log In

Stack Overflow | The World’s Largest Online Community for Developers

stackoverflow.com

 

[Darren Smith]

Hi

Please can somebody help?