links to all files in folder, and more..?

[lava]

I have a folder full of invoices and i would like to make some databases that are linked to them.
The first database i want to make would be like a calendar
Jan 1 2 3 ...
Feb
March
...
and on my invoices i have a date field and an order # field. i would like it to use the date to place the order # in the right field on my database and automatically do it for all the files in my invoice folder and any new invoices i make.

The second database would be a day by day inventory.
I'll have a folder with a database for each day:
11-19-09.xcl
11-20-09.xcl
11-21-09.xcl
...
and each file will have a full inventory
Chairs IN OUT
Tables
Linens
...
The IN number will be a set number - the OUT number. Say i have 100 chairs and 60 are out the IN will say 40 and the OUT will say 60. and i need the out numbers to be pulled from the invoices in a way that if B4 = Chairs, B5 = number of out chairs. but if B4 = Tables, B5 = number of out tables.

I appreciate any kind of help and i hope this isn't too confusing. thanks.
<!-- / message -->

 

[ravishankar]

Hi
Assuming A1 has the date and A2 has the invoice number, Paste the following codes in the macro window ( Alt f11) and save the workbook inside the invoice folder. run the macro.

Sub lava()
Dim z  As Long, e As Long, h As Long, a As Long
Dim f As String
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To z
        If Cells(e, 1) <> ActiveWorkbook.Name Then
            For h = 1 To 2
            Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!A" & h
            Cells(e, h + 1) = Cells(1, 3)
            Next h
        Cells(Month(Cells(e, 2)) + 1, Day(Cells(e, 2)) + 3) = Cells(Month(Cells(e, 2)) + 1, Day(Cells(e, 2)) + 3) & Chr(10) & Cells(e, 3)
        End If
    Next e
    For a = 1 To 31
    Cells(1, a + 3) = a
    Next a
Range("D2") = "January"
Range("D2").AutoFill Destination:=Range("D2:D13"), Type:=xlFillDefault
Range("D2:D13").Select
MsgBox "collating is complete."
End Sub

It will list all files in col A, Date in col B and invoice no in col C.
Calender from D1 to AH12 will be displayed and invoice numbers collated in appropriate cells.
Ravi

 

[jim may]

Rava,

This is excellant code. Just one minor, minor glitch if it could be altered..
The order numbers, as they are placed into the summary - How can I eliminate
the initial (Only the first) Chr(10)

code line
Cells(Month(Cells(e, 2)) + 1, Day(Cells(e, 2)) + 3) = Cells......

Is producing a given cell stacked 3 high (due to Char 10 being in position 1)

Blank
1111
2222

Other Chr(10)'s I understand are needed to break between elements, I just want to eliminate the First one which seems unnecessary.

How would the code be modified to accomplish this task.

Again, Thanks

JIm

 

[jim may]

Rav1, Never mind - I kept beating on it - I added this line under the one mentioned above, and it now does fine.

Cells(Month(Cells(e, 2)) + 1, Day(Cells(e, 2)) + 3) = Replace(Cells(Month(Cells(e, 2)) + 1, Day(Cells(e, 2)) + 3), Chr(10), Chr(32), 1, 1, 1)

 

[lava]

Thanks so much for the help. I'll give this a try sometime next week and report back.