VBA to insert a row/data when a new pdf has been added to a folder

Bramble

New Member
Joined
Aug 8, 2013
Messages
33
Hello everybody!

I'm currently using the below code to add a file from a folder location and hyperlink it to an excel sheet.

Code:
Option Compare TextOption Explicit
 
Function Excludes(Ext As String) As Boolean
     'Function purpose:  To exclude listed file extensions from hyperlink listing
     
    Dim X, NumPos As Long
     
     'Enter/adjust file extensions to EXCLUDE from listing here:
    X = Array("exe", "bat", "dll", "zip", "xlsm")
     
    On Error Resume Next
    NumPos = Application.WorksheetFunction.Match(Ext, X, 0)
    If NumPos > 0 Then Excludes = True
    On Error GoTo 0
     
End Function
 
Sub HyperlinkFileList()
     'Macro purpose:  To create a hyperlinked list of all files in a user
     'specified directory, including file size and date last modified
     'NOTE:  The 'TextToDisplay' property (of the Hyperlink object) was added
     'in Excel 2000.  This code tests the Excel version and does not use the
     'Texttodisplay property if using XL 97.
     
    Dim fso As Object, _
    ShellApp As Object, _
    File As Object, _
    SubFolder As Object, _
    Directory As String, _
    Problem As Boolean, _
    ExcelVer As Integer
    Dim OpenPosition As Integer
    Dim ClosePosition As Integer
    Dim i As Integer
    Dim j As Integer
        
     
     'Turn off screen flashing
    Application.ScreenUpdating = False
     
     'Create objects to get a listing of all files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
     
     'Prompt user to select a directory
    Do
        Problem = False
        Set ShellApp = CreateObject("Shell.Application"). _
        Browseforfolder(0, "Please choose a folder", 0, "c:\\") 'change this to specific job folder
         
        On Error Resume Next
         'Evaluate if directory is valid
        Directory = ShellApp.self.Path
        Set SubFolder = fso.GetFolder(Directory).Files
        If Err.Number <> 0 Then
            If MsgBox("You did not choose a valid directory!" & vbCrLf & _
            "Would you like to try again?", vbYesNoCancel, _
            "Directory Required") <> vbYes Then Exit Sub
            Problem = True
        End If
        On Error GoTo 0
    Loop Until Problem = False


     'Adds each file, details and hyperlinks to the list
    For Each File In SubFolder
        If Not Excludes(Right(File.Path, 3)) = True Then
            With Worksheets("MASTER")
                 'If Excel 2000 or greater, add hyperlink with file name
                 'displayed.  If earlier, add hyperlink with full path displayed
                If Val(Application.Version) > 8 Then 'Using XL2000+
                    .Hyperlinks.Add _
                    anchor:=Worksheets("MASTER").Range("A65536").End(xlUp).Offset(1, 0), _
                    Address:=File.Path, _
                    TextToDisplay:=Split(File.Name, "-")(0)
                Else 'Using XL97
                    .Hyperlinks.Add _
                    anchor:=Worksheets("MASTER").Range("A65536").End(xlUp).Offset(1, 0), _
                    Address:=File.Path
                End If
                 'Add date last modified, and size in KB
                With .Range("A65536").End(xlUp)
                    .Offset(0, 4) = File.DateCreated    'file created
                    With .Offset(0, 2)
                    
OpenPosition = InStr(File.Name, "-")
ClosePosition = InStr(File.Name, ".pdf")
On Error Resume Next
                        .Value = Mid(File.Name, OpenPosition + 1, ClosePosition - OpenPosition - 1) 'naming value for the description
                        With .Offset(0, -1)
                            .Value = Split(File.Name, " ")(0)


                        End With
                    End With
                End With
            End With
        End If
    Next
     
End Sub

In short, it adds files, then hyperlinks them to Column A, adds parts of file name in columns B, C, and E that can be used by a user to search for specific pdf's. From the file information, the user can manually add comments into Column I for future information. Everything works except when it comes to the commenting part; once another file is added to the folder, the comments to the pdf don't align. I'm hoping that there would be a way to insert a row and add the new files when they have been added, thus the rest of the information in Column I would align with the pdf.

I've looked for a week or so and tweaked code that I've found here and on different websites to see if I can get it to work, but have not been successful. I'm hoping that someone has a better idea, or can add to this code!

Thank you in advance!
 

Forum statistics

Threads
1,084,753
Messages
5,379,657
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top