Ok, so I got that to work...here's the code.
Sub GenerateFileLinks()
ActiveSheet.Cells.Clear
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
'Set objFolder = objFSO.GetFolder("C:\Users\jbishop\Desktop\SOPs With New Names")
Set objFolder = objFSO.GetFolder("\\jacksonville-dc\common\Jonathan Bishop\SOPs With New Names")
Dim i As Long: i = 1
For Each objFile In objFolder.Files
With Worksheets(1)
'.Cells(i, 1) = objFile
If UBound(Split(objFile, "-")) > 3 Then
.Cells(i, 1) = Split(objFile.Name, "-")(2)
.Cells(i, 2) = Split(objFile.Name, "-")(3)
'Create hyperlink in each cell
ActiveSheet.Hyperlinks.Add Anchor:=.Cells(i, 3), Address:=objFile.Path, TextToDisplay:=Split(objFile.Name, "-")(4)
.Cells(i, 4) = Split(objFile.Name, "-")(5)
End If
End With
i = i + 1
Next objFile
End Sub
I need to combine that with this code...
Option Explicit
Private Sub Workbook_Open()
' Set local folder path
Const FolderPath As String = "\\jacksonville-dc\common\Jonathan Bishop\SOPs With New Names"
Const FileExt As String = "docx"
Dim oFSO As Object
Dim oFolder As Object
Dim oFiles As Object
Dim oFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(FolderPath)
Set oFiles = oFolder.Files
Dim v As Variant
Dim iSheet As Long
' Clear Worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.ClearContents
Next ws
For Each oFile In oFiles
If LCase(Right(oFile.Name, 4)) = FileExt Then
v = Split(oFile.Name, "-")
Select Case v(3)
'Setup Select to determine dept values
Case "PNT", "VLG", "SAW"
Call pvtPutOnSheet(oFile.Name, 1)
Case "CRT", "AST", "SHP", "SAW"
Call pvtPutOnSheet(oFile.Name, 2)
Case "CRT", "STW", "CHL", "ALG", "ALW", "ALF", "RTE", "AFB", "SAW"
Call pvtPutOnSheet(oFile.Name, 3)
Case "SCR", "THR", "WSH", "GLW", "PTR", "SAW"
Call pvtPutOnSheet(oFile.Name, 4)
Case "PLB", "SAW"
Call pvtPutOnSheet(oFile.Name, 5)
Case "DES"
Call pvtPutOnSheet(oFile.Name, 6)
Case "AMS"
Call pvtPutOnSheet(oFile.Name, 7)
Case "EST"
Call pvtPutOnSheet(oFile.Name, 8)
Case "PCT"
Call pvtPutOnSheet(oFile.Name, 9)
Case "PUR", "INV"
Call pvtPutOnSheet(oFile.Name, 10)
Case "SAF"
Call pvtPutOnSheet(oFile.Name, 11)
Case "GEN"
Call pvtPutOnSheet(oFile.Name, 12)
End Select
End If
Next oFile
End Sub
Private Sub pvtPutOnSheet(s As String, i As Long)
Dim r As Range
With Worksheets(i)
Set r = .Cells(.Rows.Count, 1).End(xlUp)
If Len(r.Value) > 0 Then Set r = r.Offset(1, 0)
r.Value = s
End With
End Sub
Any suggestions on how to proceed? Should I just add this section of code to every case in the Select statement?
With Worksheets(1)
'.Cells(i, 1) = objFile
If UBound(Split(objFile, "-")) > 3 Then
.Cells(i, 1) = Split(objFile.Name, "-")(2)
.Cells(i, 2) = Split(objFile.Name, "-")(3)
'Create hyperlink in each cell
ActiveSheet.Hyperlinks.Add Anchor:=.Cells(i, 3), Address:=objFile.Path, TextToDisplay:=Split(objFile.Name, "-")(4)
.Cells(i, 4) = Split(objFile.Name, "-")(5)
End If
End With