Combining tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a 2003 database which consists of a series of linked excel sheets. I want to combine all of the data into a single Access table. This is to collate timesheet information filled in by colleagues on a weekly basis. The database and all of the spreadsheets will be stored in the same directory, and the excell files will have number refernces, eg 245.xls. The data is contained in named ranges in the excel files, the name being common to all files.

Once the information is collated, it will be appended to a master table.

I have some basic programming knowledge in Access 2002.
 
Hi
You mean you want to add the records in to one table

copy and paste the records or

use union query simply.

if you want to add feilds then use make table query


R.D.CHETTY



-----Original Message-----
I have a 2003 database which consists of a series of
Click to expand...
linked excel sheets. I want to combine all of the data
into a single Access table. This is to collate timesheet
information filled in by colleagues on a weekly basis.
The database and all of the spreadsheets will be stored in
the same directory, and the excell files will have number
refernces, eg 245.xls. The data is contained in named
ranges in the excel files, the name being common to all
files.
 
Add all of the records to one table, I could do it manually, table by table, but there are about twenty tables, and this is a weekly excercise. All of the tables are identical, other than the data contained.

Each person uses a spreadsheet template which is saved in a specified location. In xp I had a collector spreadsheet, which referenced the individual files. The collected information was transferred to Access using Accesslinks, an add-in, available with xp but not with 2003

Importing the files into Access has not worked
 
I have a temporary solution, a union query with a line for every linked file seems to work. This will require editing every time we have a new staff member, What I hope to do is produce a routine which automatically picks up every spreadsheet file in a given directory. The SQL statement which works is

SELECT [101].*
FROM 101;
union all
select[154].*
from 154;
UNION ALL select [254].*
from 254;
etc.
 
Hi Jimmy,

If you modify the following procedure (originally posted by Joe Fallon
MVP) to use TransferSpreadsheet instead of TransferText, it should get
you started.

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub
 
I should be able to make use of that, though I could not get "transferspreadsheet" to work properly when the imported table included cells with formulae rather than data. That was in XP, perhaps MS have improived this for 2003. Thanks
 
TransferSpreadsheet still does not work reliably with all formulas, but
IME simple ones don't seem to bother it.

You may end up having to write VBA running in Access that uses
Automation to open each workbook in turn and either

-work through each named range doing something like this to replace the
formulas with their values:
Dim C As Excel.Range

For Each C in MyRange.Cells
C.Value = C.Value
Next
and then use TransferSpreadsheet

-work through each named range getting the cell values and appending
them to the relevant Access tables (using recordset operations)

-export each named range to a CSV file and use TransferText to import
that.
 
Back
Top