Excel Database Help

P

Pooky

Garfield's Teddy
Joined
Dec 16, 2007
Messages
24,504
Reaction score
21
Location
Neverland
Hi guys

I wonder if you can help.

I have a folder with about 350 files in it which are all named as follows:
2014-08-22 File Description
Click to expand...

So they have the date first then a description of the file.

Is there a way to use excel so I can like click something and get a list of all the files with a keyword?
Also can I link the files relative to the directory they are in so you click the name in excel and the file opens?

Can I set up sorting columns so I can for example sort by year and month and get a list of all files in that time frame?

So far I have put all the file names in a separate cell.
 
You can so this using VBA. Going to be a challenge if you've never programmed before though
 
Okay, what I have managed to do is split the date from the file name, and then make a table which I can specify words.

Now if only I can automatically generate keywords for the files
 
Here's some very simple code to get you started, it assumes it is run from the same folder as the files you wish to list:

HTML: 
Sub GetFiles()
    Dim ws As Worksheet, h As Hyperlink
    
    Set ws = ActiveSheet
    For Each h In ws.Hyperlinks
        h.Delete
    Next
    ws.Cells.ClearContents
    
    i = 4
    fDir = Application.ThisWorkbook.Path
    fName = Dir(fDir & "\*.*")
    Do While Len(fName)
        If InStr(fName, " ") Then
            sDate = Split(fName, " ")(0)
            sDescription = Split(fName, " ")(1)
            sYear = Split(sDate, "-")(0)
            sMonth = Split(sDate, "-")(1)
            Cells(i, 1) = sDate
            Cells(i, 2) = sYear
            Cells(i, 3) = sMonth
            Cells(i, 4) = sDescription
            ws.Hyperlinks.Add Cells(i, 5), fDir & "\" & fName
            
            i = i + 1
        End If
        fName = Dir
    Loop
    
End Sub
 
excel isn't a database

however I'm sure you can butcher it to do what you want. (I know it's not much help)

If you do get a solution going. Remember to post it here for others if they are looking to do the same :)
 
Another suggestion, perhaps the long way, but doesn't involve any coding.

Getting your list
Open cmd.exe
go to the directory where the files are, e.g. C:\My Files\
type in dir > xxx.txt (replace xxx with what ever you want to name the text file)
A text file, listing the folder structure and files in the root will be created.
Copy and past this into Excel, cell A1

Formatting
In Excel, go to the data ribbon and select text-to-columns, select Fixed length. This is going to split all the data up for you but you need to do a bit of tweaking yourself.
The date range will be character 0-10
Your file name should be starting at character 39 to the end.
In the middle (character 11-38) should be all other junk that you dont need. deselect all columns in between.
It should look something like this
attch1.JPG
Click next
Select Date Format, in the exact format you have DMY, YMD or it's variants.
On the middle - garbage field - select do not import
On the last field - filename - select general
Click next and finish

Your data should be in a usable format now.
Add column names (Date / Filename) and you'll be able to sort.
 
=if(isnumber(search("keyword", cell where to find,1)),"Found","not Found");

Then you just set up a filter on the founds..
 
Last edited:
Some fine tuning needed:
1. You'll still have garbage in column 1 and 2, delete that (basically anything that doesn't have a date next to it, will be garbage)
2. In Column C (cell C2), you can add a formula for hyperlink "phase 1", =concatenate("xxx",b2) Replace xxx with the folder destination, e.g. "C:\My Files\"
3. In Column D (cell D2), you add the formula =hyperlink(c2) which will add the hyperlink functionality to it.

You can of course do steps 2 and 3 together =hyperlink(concatenate("C:\My Files\", B2))

That's the easiest, non programming/VBA way that I can think of off the top of my head.
 
envo said:
excel isn't a database

however I'm sure you can butcher it to do what you want. (I know it's not much help)

If you do get a solution going. Remember to post it here for others if they are looking to do the same :)
Click to expand...

Technically it is a database. Notepad can be used as a database.
Anything that can store, and to an extent organize data, is considered a database.
You can even use Excel to create a relational database with vlookup functionality. Would suck to be the "DBA", but it's possible.

Thanks to VBA, Excel sure as hell can be used to create anything.
Often times accountants use it to its full extent, storing, sorting, calculating and pivoting data like there is no tomorrow; even fully automated functionality with scheduled jobs.
 
Pooky said:
Hi guys

I wonder if you can help.

I have a folder with about 350 files in it which are all named as follows:


So they have the date first then a description of the file.

Is there a way to use excel so I can like click something and get a list of all the files with a keyword?
Also can I link the files relative to the directory they are in so you click the name in excel and the file opens?

Can I set up sorting columns so I can for example sort by year and month and get a list of all files in that time frame?

So far I have put all the file names in a separate cell.
Click to expand...

You can use HTML Linking with the UNC path to the file to link to the file
Make a column for link with a formula to build the filename and UNC path
 
Have you considered using a v lookup, then a hyperlink to open the file?
Google v lookup - its a bit of mission to understand at first, but works well once you get going. - Essentially your keywords in cells across a number of columns.
The concatenate command splits the date and file name.
 
TEXTILE GUY said:
Have you considered using a v lookup, then a hyperlink to open the file?
Google v lookup - its a bit of mission to understand at first, but works well once you get going. - Essentially your keywords in cells across a number of columns.
The concatenate command splits the date and file name.
Click to expand...

VLookUp is very handy, but essentially won't help Pooky's initial question.
VLookUp formulae also ties in with my relational database comment from earlier - you can have data sets across multiple sheets, and use the functionality to "look it up", thus creating keys is an option.

Not sure I understand your comment regarding concatenate? Concatenate is used to join multiple items together. The Left, Mid and Right formula is used to "extract" info. If you have the following value in cell A1 "2014/08/26 Example", you can use them on the following basis to get the date and data separately:
=left(A1,10) or =mid(A1,1,10) which will result in : 2014/08/26
=right(A1,10) or =mid(A1,12,10) which will result in : Example
 
MrR said:
Technically it is a database. Notepad can be used as a database.
Anything that can store, and to an extent organize data, is considered a database.
You can even use Excel to create a relational database with vlookup functionality. Would suck to be the "DBA", but it's possible.

Thanks to VBA, Excel sure as hell can be used to create anything.
Often times accountants use it to its full extent, storing, sorting, calculating and pivoting data like there is no tomorrow; even fully automated functionality with scheduled jobs.
Click to expand...

Excell Database? It would suck to be the user and the developer as well.
 
To proof that it is possible to do what you've asked, I've created a Workbook to accomplish the task.
If you are still in need of it you are welcome to PM me and I'll email the file.

I'm not able to attach the file to the post.
 
Um the person who I was making it for was happy with me splitting the date and name and then I managed to make a table thing for each column to sort it.

I would still like to see that though. Will pm you.
 
I've send you the email as requested.
 
Code to accomplish the task.

Copy code into new VBA module and execute.

Code: 
Option Explicit
 Sub GetFileNames()
 Dim xRow As Long
 Dim xDirect$, xFname$, InitialFoldr$
 InitialFoldr$ = "C:\"
 With Application.FileDialog(msoFileDialogFolderPicker)
 .InitialFileName = Application.DefaultFilePath & "\"
 .Title = "Please select a folder to list Files from"
 .InitialFileName = InitialFoldr$
 .Show
 If .SelectedItems.Count <> 0 Then
 xDirect$ = .SelectedItems(1) & "\"
 xFname$ = Dir(xDirect$, 7)
 Do While xFname$ <> ""
 ActiveSheet.Cells(3, 1).Select
 ActiveCell.Offset(xRow) = xDirect$ & xFname$
 ActiveCell.Offset(xRow, 1) = Trim(Mid(xFname$, 11, 255))
 ActiveCell.Offset(xRow, 2) = Left(xFname$, 10)
 ActiveCell.Offset(xRow, 3) = Left(xFname$, 4)
 ActiveCell.Offset(xRow, 4) = Mid(xFname$, 6, 2)
 ActiveCell.Offset(xRow, 5) = Mid(xFname$, 9, 2)
 ActiveCell.Offset(xRow, 6) = Left(xFname$, (InStrRev(xFname$, ".", -1, vbTextCompare) - 1))
 xRow = xRow + 1
 xFname$ = Dir
 Loop
 End If
 ActiveSheet.Range("A3:A65000").Select
 Call Set_Hyperlinks
 ActiveSheet.Cells(2, 1).Value = "Link"
 ActiveSheet.Cells(2, 2).Value = "Description"
 ActiveSheet.Cells(2, 3).Value = "Date"
 ActiveSheet.Cells(2, 4).Value = "Year"
 ActiveSheet.Cells(2, 5).Value = "Month"
 ActiveSheet.Cells(2, 6).Value = "Day"
 ActiveSheet.Cells(2, 7).Value = "Filename"
 ActiveSheet.Range("A2").AutoFilter
 End With
 End Sub

Public Sub Set_Hyperlinks()
On Error GoTo Set_Hyperlinks_Err
 Dim Cell As Range
 For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
 If Cell <> "" Then
 ActiveSheet.Hyperlinks.Add Cell, Cell.Value, , , "GoTo file"
 End If
 Next
 ActiveSheet.Cells(2, 1).Select
Set_Hyperlinks_Exit:
    Exit Sub
Set_Hyperlinks_Err:
    ActiveSheet.Cells(2, 1).Select
    MsgBox "Directory selection was canceled." & vbCrLf & "Hyperlinks cannot be applied.", vbInformation, "Select folder"
    Resume Set_Hyperlinks_Exit
 End Sub
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X