browsing..not importing

[Shivalee Gupta via AccessMonster.com]

i need a code to import all files inside a folder from excel into access tables. i have searched many sites and from the site given below:
http://www.mvps.org/access/api/api0002.htm
i have found a code which starts the "browse window". but does not start importing.
please, please help me.

 

[John Nurick]

Hi Shivalee,

The code you already have will let the user select a folder.

After that, you need to write code that will iterate through each Excel
workbook in the folder and import the data you want. If the name of the
worksheet and the layout and column names of the data are identical in
all the workbooks, and you want to import them into a single table,
something like this will probably do the job in the Click event
procedure of a commandbutton. This is untested air code, so try it out
on a copy of your database and make sure you have backups of everything:

Dim strFolderName As String
Dim strFileName As String

strFolderName = BrowseFolder("What Folder you want to select?")
strFolderName = strFolderName & "\"

strFileName = Dir(StrFolderName & "*.xls")

Do While Len(strFileName) > 0
DoCmd.TransferSpreadsheet acImport, , "MyTable", _
strFolderName & strFileName, True, "Sheet1$"
'once the file has been imported, move it to an archive folder
Name strFolderName & strFileName _
As "C:\Archived Imported Files\" & strFileName
strFileName = Dir()
Loop


If the Excel files are going to be different, or if the data needs to go
into different tables, things get more complicated.

 

[Chris Reveille]

Maybe this will help. I got this from a previous post and
it works well.

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 need a code to import all files inside a folder from

excel into access tables. i have searched many sites and
from the site given below: