Loading LOTS of data files

[mark hansen]

I've been asked to link about 180 individual (identical) workbooks with one workbook so a central person can monitor the training dates entered by individual users (in their own workbook).

I can link one sheet to 180 other workbooks, but I don't think that's the best way as anytime we get some new, I will need to set up another set of links to their workbooks.

I'm thinking about having each of the 180 workbooks save a one line CSV file from their workbook containing various information such as name, section, and a training date for each type of training we all need to take. Then the central person will open their workbook and it will read in all of the one line of data files for everyone and then we can go from there. Once all of the data for all 180 people is in one file we can do the other stuff such conditional formatting and reporting of people that are not up to date with their training.

I've done some internet searches and there is help loading multiple data files, with variable names into Excel. I haven't tried them yet, I'm still exploring options. Unfortunately, MS Access isn't an option here and having one file that everyone updates doesn't seem practical.

Another factor is the individual excel files with dates is already out there, so I will need to modify 180 workbooks so I figure inserting a module with the code, and pasting in the same close event wouldn't be too bad to set up the saving of the one line data set for each person.

Does this seem practical? Any other suggestions out there?

Thanks for any advise.
Mark

 

[mark hansen]

Thanks, I'll see what I can do with this.

 

[offthelip]

here is something else to play with, this code will create a hyperlink to every file in a folder. Currently the folder which the active workbook is saved. Note you need to make a refence to Microsoft scripting runtime to get it to work in the VBA tool menu

Sub test()' Add reference to Microsfot Scripting runtime to get the FSo functionality
Dim pathn As String
    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim inarr(1 To 5000) As String


pathn = ActiveWorkbook.Path
FolderName = FSO.GetFolder(pathn)
Set myFolder = FSO.GetFolder(pathn)


cnt = 1
' find all the files
For Each myFile In myFolder.Files
    inarr(cnt) = myFile.Name
    cnt = cnt + 1
Next
 For i = 1 To cnt - 1
                        '   now add links
              With ActiveSheet
              .Hyperlinks.Add Anchor:=.Range(Cells(i, 1), Cells(i, 1)), _
                                Address:=Path & inarr(i), _
                                TextToDisplay:=inarr(i)
              End With
 Next i
End Sub

 

[offthelip]

finally here is some code that will check every file in a folder againts every hyperlink on the active sheet and tell which are missing. Run the previous code and then delete a couple of the links and then try this code

Sub findlinks()


Dim h As Hyperlink
' Add reference to Microsfot Scripting runtime to get the FSo functionality
Dim pathn As String
    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim inarr(1 To 5000) As String






pathn = ActiveWorkbook.Path
FolderName = FSO.GetFolder(pathn)
Set myFolder = FSO.GetFolder(pathn)


cnt = 1
' find all the files
For Each myFile In myFolder.Files
     
    fnd = False
    For Each h In ActiveSheet.Hyperlinks
        If myFile.Name = h.Name Then     ' Filename = linked file
         ' file already linked
          fnd = True
          Exit For
        End If
    Next h
    If Not (fnd) Then
     MsgBox (myFile.Name & " was not linked")
    End If
Next




End Sub

I reckkon that is all you need to do the job

 

[mark hansen]

Wow, thanks offthelip... this is very helpful!

 

[LikeButtah1]

Just another possible way could be the use of MoreFunc with the Indirect.ext formula as long as you are using a 32 bit version of Excel. I currently use it to pull payroll into a summary worksheet using something like this : =IFERROR(INDIRECT.EXT("'S:\Security\Security Payroll-Scheduling\PAYROLL JAN-DEC 2018\["&TEXT($O38,"M-D-YY")&".xlsm]Summary'!M9"),"")

So I list the path and I'm using ["&TEXT($O38,"M-D-YY")&".xlsm] because the file name is a date to pull the value from the cell. I don't know how you have your files set up but if its a last name then you could have a list of names and pull from that so whenever you add personnel just add it to the list.