Compiling data from many workbooks

[starman0724]

I have data in many worbooks which needs to be compiled for charting. The many worbooks have filenames with a common convention and date stamp.
Example: 123456 - Chicago 2004-10-25.xls.
There are only 3 cells on one worksheet of each file which contain the needed data.

I'd either like to make a workbook which will search for a specific filename type (i.e. 123456 - Chicago) and build an array (table?) from the data in all the workbooks.

-or-
Open the individual workbooks but send the data to a central workbook with a macro that indexes the row number to the next available.

-or-
something that works better. We need to graph two data points over time (the date is in one of the cells).



Ken
ps - I have a "user's" grasp of Visual Basic which is available through Excel

 

[SkipVought]

Hi,

If all your workbooks' data is in table format per sheet, then you could QUERY each workbook/sheet in a loop and put the results in your chart source data table.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[starman0724]

Skip, First of all...thank you for your speedy reply...

The data is on the "Summary" sheet, in cells AE1, AF1 and AE2.

AE1 & AF1 are data, and AE2 is the date. These don't fall into a table format, do they? I suppose I could rearrange my data if it will enable a smoother project. Please feel free to advise on the best and cleanest method.

Regarding the query, I'm not sure how to point the query to different file names. Additionally, though I routinely use ODBC drivers to pull data from databases into Excel, I am very fuzzy on how to do Excel to Excel queries.


Ken

 

[SkipVought]

Excel is just another ODBC data source.

If you organize your data in tables on separate sheets with headings, your Excel workbooks/sheets can be referenced just like any other ODBC data source.

In your destination sheet, insert a query table using Data/Get external Data/New database Query - Excel Files ...

after completing the querytable and returning the resultset to Excel, turn on your Macro recorder and record EDITING the query. Turn off the recorder and look at your code & clean it up to look like

Sub GetRequirements()
    Dim sConn As String, sSQL As String
    
    sConn = "ODBC;DSN=A010PROD;PWD=;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;"
    sConn = sConn & "FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;"
    sConn = sConn & "MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
    
    sSQL = "SELECT "
    sSQL = sSQL & "MFG.PART_ID, MFG.MFG_ORD, MFG.QTY, SCH.CC, SCH.OPER, SCH.LPST "
    sSQL = sSQL & "FROM FPRPTSAR.MFG_ORDER_INFO MFG, FPRPTSAR.MC_BUILD_SCHEDULE SCH "
    sSQL = sSQL & "WHERE (MFG.PART_ID In (" & MakeList & ")) "
    sSQL = sSQL & "And (MFG.MFG_ORD=Substr(SCH.TRAVELER,1,7) Or Substr(MFG.MFG_ORD,3,7)=Substr(SCH.TRAVELER,4,7)) "
    
    With wsQry1.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Then you can modify either the connect string or the sql on the fly.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[starman0724]

Skip,

I tried to initiate a query, but it tells me there are no tables. Here's the scoop. We're performing a countif function that's based upon today's date...ie when the macro is run. In order for that value to 'save' a fixed value (ie not a calculated field), I copy to columns non contiquous columns to another location. For some reason, the formulae become numbers.

Great! Next, the spreadsheet is saved with a date stamp appended to the filename. Now we have to gather that pasted data from all these files for historic trending.

Hmmmmmm.....

Can I write a macro that prompts me for a file to pull data from? And then put it in a specific row number which is indexed each time I run the macro?

Ken

 

[SkipVought]

look at Application.GetOpenFilename


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}