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

 

[offthelip]

I have done something similar which was for client applications, there were about 60 or so every month, I automatically created a new "summary" worksheet in the master workbook each month, when ever a client application was made a new workbook was created with the name of the client in a specific folder with links showing summary data from that file into the master, summary worksheet. It was working fine when I last saw it 18 months later, when there were over a 1000 linked workbooks.
Is there some reason you want to create CSV files rather that a workbook? I was using a template for the workbook and just copying it and putting the links in.

I think they way I would do it would be to put some code in the individual workbook open (or close) event to detect whether the "summary workbook for that client exists , if not create the workbook with the summmary data in it. I would put all the summary workbooks into one folder. I wouldn't bother about "reading" the summary files in the master file just create links to them automatically. This will allow you to delete a file when a person leaves. It will also allow the individual to update there "summary" bit provided you cater for that in the individual workbooks.

 

[mark hansen]

Thanks for the idea, I hadn't thought about that. If I'm understanding you correctly, each person would have more than one file. I don't need that (I think)... I just need the current information with the training dates of each subject we need training on.

I was thinking of a CSV with the (updated) training dates because I'm used to working with CSV files. Say "Jim" opened his individual workbook and updated one date, then closed. I need the current information, rather than an ongoing log of information. So the CSV would be overwritten with essentially the same information, except with the one date updated to the current date.

I envision a folder with 180 CSV files. Each of them updated at a different time by the individual. Other information will be in the CSV file such as date and time of last update and who closed the file. The trick will be how long does it take to read all of the files and lay them out in sort of a table fashion.

Thanks again,
Mark

 

[offthelip]

The only difference between your idea and mine is you are using csv files and i used excel files which means I was ableto link the master file to all of them and it updated automatically when the user updates his "summary" file. That won't happen with csv files you have to go and read them with a macro or something.

 

[Fazza]

alternative to adding code in the ~180 csv files is to create an add-in file

having the code in a separate file can simplify code revisions - just issue a new add-in, not change the 180 files

 

[mark hansen]

Offthelip: I thought about using excel (formula) linking, but thought it would be a higher maintenance way to go. for example, when we get a new person, and a new excel file is set for them, I will need to create new links to the new file. When a person leaves, I will need to remove those links...

With my searching, I found this, but it doesn't do exactly what I want.

The text file will have one line and look like this:

Testing |zzz|Applications|02 Apr 17|09 Apr 18|09 Apr 18|09 Apr 18|09 Apr 18|12 Apr 18|10 Apr 18 | |09 Apr 18|02 Apr 18|||||09 Apr 18||09 Apr 18|12 Apr 18| | | |03 Apr 18| | |

It's delimited with a "|" and of course some spots won't have any data.

The code I came up with so far is below. I don't know enough about arrays to read the whole string and then lay it down in excel.

Sub test()
    Dim myDir As String, fn As String, ff As Integer, txt As String
    Dim delim As String, n As Long, b(), flg As Boolean, x
    myDir = "C:\Users\mark.hansen" '<- change to actual folder path
    delim = "|" '<- delimiter (assuming Tab delimited)
    ReDim b(1 To Rows.Count, 1 To 1)
    fn = Dir(myDir & "\*.txt")
    Do While fn <> ""
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff 
        Do While Not EOF(ff)
            Line Input #ff , txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1
                b(n, 1) = fn
               
            End If
            
            If UBound(x) > 0 Then
                n = n + 1
                b(n, 1) = x(1)
            End If
            flg = True
        Loop
        Close #ff 
        flg = False
        fn = Dir()
    Loop
    ThisWorkbook.Sheets(1).Range("a1").Resize(n).Value = b
End Sub

Can anyone help with widening the array so it will get all the columns and laying it down in Excel?

Thanks,
Mark

 

[mark hansen]

I forgot to mention each text file will be 27 columns wide.

 

[offthelip]

I thought about using excel (formula) linking, but thought it would be a higher maintenance way to go. for example, when we get a new person, and a new excel file is set for them, I will need to create new links to the new file. When a person leaves, I will need to remove those links...

There should be no manual maintenence for this at all, it should be absolutely automatic. the way I would do it is: in the Excel workbook that you distribute I would put a check in the open workbook routine that automatically creates the linked workbook if it doens't already exist, including all the links to the user workbook. It will put this in a specific file location with the username as part of the file name. You could automatically get the windows username and use that in the filename.
In the master workbook when you open it , it can check the specific file location to see if any files exist that aren't already linked , if it finds one it adds the links to that file, all automatically.
When somebody leaves, you just have to delete the file from file location and the row from the master file. Or you could automatically detect that the file no longer exists and delete the line in the master file automatically
Why bother about trying to sort out formatting rows and columns when excel does it for you very nicely

 

[mark hansen]

Offthelip: The (Excel VBA) force must be strong with you. I wouldn't begin to imagine how to get excel to find files that are not already linked and create the links. Do you mean linked via formulas or another method?

I can have people pick up a blank file and have it automatically create a file specifically for them, with their name in the file name... I would like to think people would leave that file in the folder I need it to be, but I see them moving it to their own profile so others can't get to it. I see them save in folders for each specific branch in the department.

People leaving shouldn't be too be bad. in fact I see the departing people in your method being easier than my idea because even though the excel file may be deleted, the text files will also need to be deleted to stop bringing in the data.

I would like to learn how to have Excel to automagically set up links to any new files in a specific folder.

 

[offthelip]

Here is some code that does a lot of what you want, it checks if a file exists in a specifc path if it doesn't exist it creates it, it adds a link to the master file to one cell and then saves it ( it puts abit of data in to show it is working0

Sub test()

pathx = ActiveWorkbook.Path
MasterName = ActiveWorkbook.Name


' Set this to what ever you actually want
Filenme = "Testname4"
fullpth = pathx & "\" & Filenme & ".xls"
test2 = Dir(fullpth, vbDirectory)
If test2 = "" Then
Workbooks.Add
' put some data in
Cells(2, 2) = "this is B2 of the new workbook"


newName = ActiveWorkbook.Name
            Range("B2").Select
            Selection.Copy
            
             Windows(MasterName).Activate
             Worksheets("Sheet1").Select
            
             Cells(1, 1).Select
             ActiveSheet.Paste link:=True


Windows(newName).Activate


ActiveWorkbook.SaveAs Filename:=fullpth
End If
End Sub