Appending forecasting data into one table, as dates change daily?

[sneeky]

hi. Say you have multiple excel sheets where forecasting is done daily. For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10 and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)... how to I join all these forecasts into one table; vertically with each forcast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates.. confused

 

[ranman256]

adjust this code to your tables and names.

  'run this code from a macro.  RUNCODE:  ScanCols2Tbl(tTable)
Function ScanCols2Tbl(ByVal pvTbl)
Dim sSql As String, sMyFld As String
Dim rst 'As Recordset
Dim fld As field
Dim f
Dim colFlds As New Collection
Const kFLD2START = 4
sSql = "select * from " & pvTbl
Set rst = CurrentDb.OpenRecordset(sSql)
   'collect all fields starting with fld# 4
With rst
      i = 1
      For Each fld In Fields
          If i >= kFLD2START Then
             colFlds.Add fld.Name
          End If
          i = i + 1
      Next
End With
            
                'now run the queries to pull in all data using only needed fields
      For Each f In colFlds
         sMyFld = "[" & f & "]"
             
             'post the data using this:
         sSql = "Insert into tTargTable ([Depot],[department],[Location],[TargetDate]) select [Depot],[department],[Location]," & sMyFld & " from [" & pvTbl & "]"
         DoCmd.RunSQL sSql
      Next
rst.Close
Set rst = Nothing
Set colFlds = Nothing
End Function

 

[sneeky]

Thanks ranman. But I have 20,000 products or rows in each excel file and it'll end up being a gig excel file which would be inefficient if not impossible.
Hence I need access database.
So as you can imagine I have 50 days of forecasts; each done daily.
So first few column headings are the same but then the heading! Starts from 01/09 to columns 13/10
Then another forecast is done for next day..
From 02/09 to 14/10..
Next workbook has 03/09 to 15/10
And so on...
Hence you can imagine it going diagonally..
Purpose is to see the significant differences we see each day with our forecasts...
So I need them into one table..
Simply make table and append doesn't work as the headings are dates which change daily...
Pls help anyone...