Lookup question

[llschwab]

Hello everyone,

I have a spreadsheet to which I want to add data from other workbooks. The problem is the workbooks I want to access are crated from a single workbook that is opened, data entered and then saved as another workbook. I don't want to access the original workbook, I want to access each copy saved from the original. Is this possible?

Example:

I open Calculation.xlsx, enter the data, then save the workbook as Calc12345.xlsx. I do up to 30 of these calculation worksheets a month. Now I open Monthly Inventory and want to import data from each workbook (Calc12345.xlsx, Calc12346.xlsx, Calc12347xlsx,...). Is it possible to do this without having to open each workbook? The data I want to import into Monthly Inventory is in the same cell in each of the Calculation workbooks if that is any help.

Thanks to anyone that can help.

Linda

 

[Smitty]

Welcome to the Board!

When you're collecting/storing time nature data (daily, weekly, monthly, etc.), it's far better to keep it all in a single dataset as opposed to saving a copy for each period. As you can see it's hard to get it all back into one place.

If it's all in one place to begin with, then you can use Pivot Tables to create summaries in a multitude of fashions. Think if it the same way that a database works: all of the data is in one place, then you create different queries/reports based off of the data.

To aggregate the data from multiple workbooks, you might want to take a look at INDIRECT.EXT, which is part of the MOREFUNC add-in.

The other way to do it is if all of the workbooks are in the same folder, then you can loop through the workbooks with VBA, open them, copy/paste the data you need into the master workbook, then close them. But the best approach is to have all of the data in the same place to start.

HTH,

 

[llschwab]

Thanks for your reply Smitty, I appreciate your help. I have tried looking for a download for morefunc.xll and haven't been able to find one that I can download! So that stops me in my tracks at the start. Unfortunately the file structure was set up before I got the job that I'm doing and I'm stuck with all of these files with no easy way to extract the information. There are so many files and so many computer uneducated people that it would be difficult to change them at this point. My work computer limits my search for morefunc.xll but I will try looking for it on my personal computer and see if I can get it that way. I will also try doing loops with VBA but I'm really new at VBA; not totally green but certainly not proficient yet.

Thanks,

Linda

Welcome to the Board!

When you're collecting/storing time nature data (daily, weekly, monthly, etc.), it's far better to keep it all in a single dataset as opposed to saving a copy for each period. As you can see it's hard to get it all back into one place.

If it's all in one place to begin with, then you can use Pivot Tables to create summaries in a multitude of fashions. Think if it the same way that a database works: all of the data is in one place, then you create different queries/reports based off of the data.

To aggregate the data from multiple workbooks, you might want to take a look at INDIRECT.EXT, which is part of the MOREFUNC add-in.

The other way to do it is if all of the workbooks are in the same folder, then you can loop through the workbooks with VBA, open them, copy/paste the data you need into the master workbook, then close them. But the best approach is to have all of the data in the same place to start.

HTH,

 

[Smitty]

Here's one spot for it:

Morefunc 5.05 Freeware Download - Freeware Files.com - Business/Organize Category

 

[highndry]

Hi,

I have this simple easy to understand code that loop thru a specific directory to extract data from certain cell, may be this may help you. It's not fance but it does the job.

Dim wbOpen As Workbook
Dim wbRecon As Workbook
Dim I As Long
Dim p As Long
Dim rng As Range
Dim lastRow As Long

With Application.FileSearch
.NewSearch
.LookIn = "s:\acctg\CGIT\Budget\FY 2008\cost center budgets\FY08 CC Templates All\"
.FileType = msoFileTypeExcelWorkbooks
.Execute
For I = 1 To .FoundFiles.Count

Set wbOpen = Workbooks.Open(.FoundFiles(I), UpdateLinks:=0)
Set wbRecon = Workbooks("recon to sap08.xls")
wbOpen.Activate

wbOpen.Worksheets("fy08").Select
Range("b148").Select

If Range("b148").Value = "Total" Then
Range("o148").Copy
Else: Range("o149").Copy

End If
End Sub