Here is the answer to part two of your question. I have
used this for importing not linking. I picked this up from
one of the MVP's from a previous post. Watch for word wrap.
If the Excel files have similar names such as MyFile1,
MyFile2, MyFile3 or MyFileA, MyFileB, MyFileC then you can
put the import routine in a loop that
increments the file name each iteration of the loop.
The following code should get you started
To get the files use something like the following:
Dim foundFnames As New Collection ' collection to store
found filenames
' dirPath & fileFilters are string variables containing
' the path to search
' filefilter should be self explanatory
With Application.FileSearch
..NewSearch
..LookIn = dirPath
..SearchSubFolders = True
..fileName = fileFilter
..MatchTextExactly = True
End With
' apply the search
' vader possibly store the filenames in a collection
With Application.FileSearch
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s)found."
For i = 1 To .FoundFiles.Count
foundFnames.Add .FoundFiles(i)' store the filenames in
a collection
Next i
Else
MsgBox "There were no files found."
End If
End With
' now process each filename found
' need to open the individual spreadsheets listed above
' sheetRange defines name/range of sheet to import
For Each wbFname In foundFnames
DoCmd.TransferSpreadsheet acImport, 8, importTable,
wbFname,hasFldNamesFlg, sheetRange
next wbFname
' there are various error conditions to handle.
' the transfer spreadsheet is ok (see transferspreadsheet
action help topic) but it does have the odd gotcha.
If you are loading the data into an Access table (as
opposed to a linked table) then you should be ok.
Remember that if you get any errors with individual fields
the import is likely to load the rest of the row and just
let you know that there has been a problem in a seperate
ImportErrors table. If you get data conversion errors then
use the tool here to make sure all your cells are of the
same datatype.
http://www.j-walk.com/ss/excel/tips/tip28.htm
Good luck,
Chris
-----Original Message-----
i am working on access 2000. i want to write a code which
will link my access to excel. first it should show me a
browser from which i can select a folder. when i do so, all
the files present within that folder should get imported
from excel to access as tables. maually i can do with
import option of access. but i need to do it dynamically,
because i will always have the same named but different
content excel files to import into access.
