import

Hello,
I have a large number of spreadsheets to import into access 2000. Does someone know precisely how to write a macro, for instance, to do this?

I'm not entirely sure if this is possible, but you may be able to make an ODBC connection out of your Excel sheet. Then, in Access, you can create a new DB using the ODBC connection.

Just an idea.

Hello,

In my opinion you do not need any macro to import your spreadsheets to Access.

Go to

File -> Get External Data -> Import

on the Access 200 menu bar. Here you can import youe spreadsheet/xls files.

if the number of spreadsheets is too large to manually do a file->import

you could think about a vbs script in combination with ADO to do the work for you. This only if the spreadsheets have exactly the same table format, and the worksheets are named the same, and preferrably all the files in the same folder

This vbs script needs to do the following:
- Instantiates Scripiting.FileSystemObject
- Makes a folder object
- Grabs all the files in this folder
- Loop through the file collection
- ADO/ODBC Connection to each file in this folder
- Create recordset for the worksheet which contains the data
- Insert this recordset in the Access database, also using ADO/ODBC

This process of course only if the number of sheets is large enough to warrent a automated process, preferrably if the process is repetitive.

Hi,

Well, yes for large number of spreadsheets following steps and VB code could help without any need of ADO or ODBC code.

1. Copy or move all your spreadsheets to a directory, say in d:\work. Make sure only the required spreadsheets are present here, remove any other files.

2. Change directory to d:\work with "d:" then "cd\work".

3. Create a text file containing file names of all your spreadsheets with "dir/b > sheets.txt"

4. Copy and paste following Visual Basic code into MS Access "modules" as a new sub.

Sub ImportSheets()
Dim TextLine
Open "d:\work\sheets.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, TextLine
c = c + 1
Debug.Print TextLine
Debug.Print
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Sheet" & Trim(Str(c)), TextLine, True
Loop
Close #1
End Sub

5. You may need to change "acSpreadsheetTypeExcel7" option to the matching Excel/Lotus spreadsheet version of your files you need to import.

6. Open an immediate window in Access VB editor, and run this subroutine with "call ImportSheets" in the Immediate Window.

7. After successful execution all your spreadsheets will be imported to Access tables with names Sheet1, Sheet2, ...

Please reply me if this worked for you.