Multiple excel file import

[MKH2001]

Have a issue related to thread701-1551678

I am attempting to achieve a similar result, this time with excel files.

code:

Private Sub cmdImport_Click()
DoCmd.SetWarnings False
Dim i As Integer
Dim SQL As String
DoCmd.RunSQL "DELETE tbl_FlowlineImportGPS.* FROM tbl_FlowlineImportGPS;"
     With FileSearch
        .NewSearch
        .SearchSubFolders = True
        .MatchTextExactly = True
        .LookIn = "C:\Documents and Settings\mark harris\Desktop\Flowline\" & [cbo_InsptypeFolder]
.filename = "00*.xls"
.Execute
If .Execute() > 0 Then
MsgBox "There were " & .Foundfiles.Count & _
" file(s) found"
For i = 1 To .Foundfiles.Count
List2.AddItem (.Foundfiles(i))
MsgBox .Foundfiles(i)
        filename = Mid(.Foundfiles(i), InStr(.Foundfiles(i), ".xls") - 8)
              SQL = "Insert into [tbl_FlowlineImportGPS]" _
              & " Select """ & (.Foundfiles(i)) & """ as [Key],*" _
               & " from " & "[" & Left(filename, InStr(filename, ".xls") - 1) & "]" _
                & " IN  """ & Left(.Foundfiles(i), InStr(.Foundfiles(i), ".xls") - 10) & """   ""Excel 8.0;"""
              DoCmd.RunSQL SQL
  Next i
  Else
MsgBox "There were no files found"
  End If
  End With
 DoCmd.SetWarnings True

ErrHandler:
MsgBox Err.Description
End Sub

Everything should work.... only changes I have made is regarding the extensions it is looking for and the path variables.

I still use process of using a batch rename to rename all files with a autonumber 00000000.xls, 00000001.xls etc on all xls files in a specific folder and its subfolders.

The script above finds and attempts to do the necessary, however, when run I get the following error message:

Run-time error '3051'.

The Microsoft Jet Database engine cannot open the file 'C\Documents and Settings\Mark Harris\Desktop\Flowline'. It is already opened exclusively by another user, or you need permission to view its data.

Any ideas what this might be and how to resolve?

 

[MKH2001]

Redid the same query after update after replaceing xls back to dbf (ran batch to rename all the files) which wouldnt be ideal but would have allowed me to work with data to establish certain processes....

As I know the script worked for DBF I was surprised to find that when i did not enter """Dbase 5.0;"" file I got same error message.
When entered the dbase 5.0 statement it imported fine.

So seems the lock is on them when trying specifically to import excel format? which I assume it must then default to if not specifically specified?
I had entered Excel 8.0 but that might well be the default and it isnt the format which file was created in, which would be a later version.

Is the a list or something available for these format types (ie have csv files also it seems.... again if leave blank then I get the same permission error) if I input dbase 5.0 its is obviously not correct so tells me cannot find file.
What should I enter instead of """Dbase 5.0;"" for csv text delimited?)

If there was a list of these variable then maybe I could come up with one script to handle the multiple variations which might be forthcoming.