need help have audit and i'm going to get slaughtered unless

  • Thread starter dont think auditors deserveyourmoney guy
  • Start date
D

dont think auditors deserveyourmoney guy

not savy at all so please guys simple :) I have created an inoice system
where we saved things as such. "abcompany#1.xls" now problem is i should have
did oppossite to get sequential order. I tried sorting by creation date, but
these are all copies..master is lost. Can i seperate or create a query to
search by the numbers 1st. but remember these are ind xls. docs in a big
folder.

if no, is there a simple way to pull three things from each worksheet and
paste into one new sheet. That would be name CELLS(B6:B7) and date CELL (I8),
finally amount which has an inherent problem because that is a formula so a)
copy sum cells (i14+i49) or set all invoices to a value in another macro.

guys these auditors are not cool people please help.
 
J

JMB

I'm sure there's a way to do the query (number first), but I'm not sure
exactly what you're getting at. With numbers first in the filename, Windows
still doesn't get the order right (both 10 and 100 would come before 1, 200
before 2, and so on).

If it helps, you can pull the information you want into a summary
spreadsheet. Open a new workbook in Excel, go into the visual basic editor
(Tools/Macro/Visual Basic Editor). You should see the Project Explorer
Window, which will show all of your open workbooks as "Project(Book1.xls)"
(if it's not there click View/Project Explorer). Insert a module
(Insert/Module). Expand the subfolder "Modules" under your project in the
project explorer. Click on any module under this folder and a new window
should pop up (code window). Paste the following into the code window.

Sub ImportInformation()
Const Path As String = "C:\Temp\Test"
Dim fs, Folder, FileCollection, Temp
Dim Summary As Worksheet

application.screenupdating = false
Set fs = CreateObject("Scripting.FileSystemObject")
Set Folder = fs.GetFolder(Path)
Set FileCollection = Folder.Files
Set Summary = Worksheets.Add
Count = 0

For Each x In FileCollection
Workbooks.Open FileName:=x.Path
Count = Count + 1
With Summary
.Cells(Count, 1).Value = x.Name
.Cells(Count, 2).Value = ActiveSheet.Range("B6").Value
.Cells(Count, 3).Value = ActiveSheet.Range("B7").Value
.Cells(Count, 4).Value = ActiveSheet.Range("I8").Value
.Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14").Value, _
ActiveSheet.Range("I49").Value)
End With
Workbooks(x.Name).Close savechanges:=False
Next x

application.screenupdating=true
End Sub

You will need to edit the following line to identify the folder where all of
your files are
Const Path As String = "C:\Temp\Test"

Also, as I read your email, you wanted to get the sum of cells I14 and I49.
If you meant I14 through I49, you will need to change the following line from

..Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14").Value, _
ActiveSheet.Range("I49").Value)

To:
..Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14:I49"))


Now close visual basic window and go back to Excel. Click on
Tools/Macros/Macros and you should see this macro on the list
(ImportInformation). Run the macro and
you should get a summary that has

Column 1 - source file name
Column 2 - first name (cell B6)
Column 3 - last name (cell B7)
Column 4 - date (cell I8)
Column 5 - Sum of cell (I14), cell (I49)


You can sort this summary by the number in the filename by going to column
A, inserting a row and inputting the following formula.

=TRIM(MID(B1,SEARCH("#",B1,1)+1,SEARCH(".",B1,1)-(SEARCH("#",B1,1)+1)))

copy this formula down, then highlight column A, click on copy, then go to
Edit/PasteSpecial, Select Values and click OK. Now you can sort this table
as you normally would through data/sort.

Hope this helps.
 
J

JMB

One other thing - I assumed your folder that has all of your XLS invoices
does not have any other file types in the folder. If there are other file
types, you should change the For Each loop in the macro to the following:

For Each x In FileCollection
If x.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=x.Path
Count = Count + 1
With Summary
.Cells(Count, 1).Value = x.Name
.Cells(Count, 2).Value = ActiveSheet.Range("B6").Value
.Cells(Count, 3).Value = ActiveSheet.Range("B7").Value
.Cells(Count, 4).Value = ActiveSheet.Range("I8").Value
.Cells(Count, 5).Value = Application.Sum(ActiveSheet.Range("I14:I49"))
End With
Workbooks(x.Name).Close savechanges:=False
End If
Next x
 
B

boyd

JMB--VERY UNSAVY, BUT I AM GOING TO READ THIS VERY CAREFULLY AND HOPEFULLY
FIGURE IT OUT---THX SO MUCH I'LL LET YOU KNOW
 
J

JMB

I read over my response and noticed a small error where I told you to go back
into Excel, go to A1 and insert a row and put in the following formula

=TRIM(MID(B1,SEARCH("#",B1,1)+1,SEARCH(".",B1,1)-(SEARCH("#",B1,1)+1)))

I meant to say insert a column at A1 and put in the above formula and copy
it down.

Sorry
 
Top