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

lava

New Member
Joined
Nov 19, 2009
Messages
2
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 -->
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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.
Code:
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Rav1, Never mind - I kept beating on it - I added this line under the one mentioned above, and it now does fine.

Code:
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

New Member
Joined
Nov 19, 2009
Messages
2
Thanks so much for the help. I'll give this a try sometime next week and report back.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,078
Messages
5,599,632
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top