Error Message 3011 in Access VB code when Importing Excel files

[Eavie]

Hi everyone,
Still relatively new to this so bare with me a bit.

I am making a database that provides the user with a list of files to import at the click of a button.
It works perfectly for the other databases I have made but for some reason it now won't work. I've tried starting the database from scratch, taking a copy of old ones, importing the old forms so the code is the same and it still won't work.

When I click the 'Import' button it brings up the form to view the Excel list but there is no list...
It also bring up the error message 'The Microsoft Jet database engine could not find the object 'F:\Ribnew\Returns 2011-2012\June 2011\KH08$D62_J92.XLS' Make sure the object exists and that you spell its name and path name correctly.
The names are definitely spelt correctly and the object exists.

I don't understand why it has stopped working all of a sudden when my previous ones, made last week are working fine.

I've included some of my code below;

Private Sub cmdRunImport_Click()
On Error GoTo errHandler
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "IMPORT_DATA", pubPath(1) & lstExcelFiles.Value, False, pubRange(1)
appImport
DoCmd.OpenForm "frmEditKH092_Import", acNormal
DoCmd.Close acForm, "frmImportKH092"
errHandler:
If Err.Number > 0 Then
    If Err.Number = 3011 Then
        MsgBox "The Spreadsheet " & lstExcelFiles.Value & " is not in the format required for import"
   ElseIf Err.Number = 3274 Then
       MsgBox "The Workbook " & lstExcelFiles.Value & " is protected.  Unprotect the Workbook for Import."
    Else
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
End If
End Sub

Any help or advice would be much appreciated

Thanks, Eavie

 

[vbaInet]

acSpreadsheetTypeExcel3 - isn't that the very first Excel application that was made? I think it is. Use something like acSpreadsheetTypeExcel9

 

[Eavie]

I've tried that too...
The error is coming up at the DoCmd.TransferSpreadsheet line of the code and I tried running it in the Immediate window pane and it throws up a different error 'Runtime Error 3051 Reserved Error'

 

[vbaInet]

Check the values of your pubPath(x) values. Use a msgbox to test them.

 

[Eavie]

Everything comes up fine for that, I just don't understand why that exact code works for my previous databases and not this one... I don't know if maybe it has something to do with my permissions maybe changing... I can still access the files though so I don't know why they would have been changed

 

[vbaInet]

Is the working db and the failing db in the same location? That is, the same directory?

 

[Eavie]

Yeah they're on the same drive, in the same folder

 

[vbaInet]

And they are both the same version of Access?

 

[Eavie]

Yep, I had problems with my old database because I only had Access2000 but it was upgraded there to Access2003 and it works fine with no problems.
I just don't know if it's a problem with my code or a problem with my permissions as a user on the network...

 

[vbaInet]

Upload a quick sample for me to test it here. But before uploading test that the db you're about to upload to ensure that it doesn't work.