Hi,

I am trying to create an process that programatically links in an  Excel
spreadsheet or is able to open it using DAO.  I have been able to do it for
dBASE files, but for Excel it just doesn't want to work.  Check out what I
have tried and let me know if you have any ideas.  Thanks!

I can link the table manually but when trying to execute the following code
I get error 3051:  The Microsoft Jet database engine cannot open the file
'C:\QDSRsp\Data'  It is already opened exclusively by another user, or you
need permission to view its data.  (There is no password on the XLS file and
there aren't any other processes that have it open.)

Dim db As DATABASE, tdf As TableDef

    Set db = CurrentDb
    Set tdf = db.CreateTableDef("Test")
    tdf.Connect = "Excel 5.0;Database=C:\QDSRsp\Data"
    tdf.SourceTableName = "Test.xls"
    db.TableDefs.Append tdf
    Set dbs = Nothing

I have also tried open the XLS file using DAO with the same result.  (With
the dBASE file this also works fine.)

    Set dbImport = DBEngine.Workspaces(0).OpenDatabase("C:\QDSRsp\Data,
False, True, "Excel 5.0")
    Set rsSource = dbImport.OpenRecordset("Test.xls", dbOpenSnapshot)

I even tried a SQL statement.  (Once again, this didn't work for Excel but
worked perfectly for dBASE)

    SELECT * FROM Test.xls IN "C:\QDSRsp\Data" "Excel5.0;"

Yes I have tried the transferspread method and it works fine...but what's
going on with the other methods?
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5,
"tblTempDigitalPrinting","C:\QDSRsp\Data\Test.xls", True

--
Doug Rhoten
Quality Data Systems

Hi,

Try using the TransferSpreadsheet method/action instead.  It allows you to
either link or import a spreadsheet, some "air-code" to link an Excel 97
file,

Function fLinkXLFile(strFileToLink As String, _
                        strWhatTable as string)
    on error resume next
    DoCmd.TransferSpreadsheet acLink, _
            acSpreadsheetTypeExcel97, _
            strWhatTable, strFileToLink
End Function

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

I think your are putting the wrong stuff in the Connect and SourceTableName
properties.  Try this... Link an Excel file manually with the Link Table
dialog.  Then check the Connect and SourceTableName properties of the
resulting TableDef.  I tried it with a sample Excel file and got:

Connect:

Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\Program Files\Microsoft
Office\Office\Examples\SAMPLES.XLS

SourceTableName:

Contents$

"Contents" was the name of the workbook page or named range to link to, I
chose Yes to have the first row be field names (HDR=YES), and I have no idea
what IMEX=2 means, but I think you need it.

Hope this provides a little insight.

Alden

Yes, I had already tried that as I wrote in my original post.  Thanks, I
appreciate the suggestion.

Doug Rhoten
Quality Data Systems

Alden -

<Snip>

Can you say deer in the headlights?!

Yes, it worked out great.  As for the IMEX=2 (if I were to guess) is the
import export specification for Excel as shown in the file type list when
manually linking the spreadsheet.

Thanks again!

Doug Rhoten
Quality Data Systems

Hi Doug
You're using the wrong parameters for the connect string and the
sourcetablename

Try something like this

    Dim db As Database, tdf As TableDef

    Set db = CurrentDb
    Set tdf = db.CreateTableDef("XLTest")
    tdf.Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=C:\QDSRSP\data\Test.xls"
    tdf.SourceTableName = "sheet1$"
    db.TableDefs.Append tdf
    Set db = Nothing

Note: the connect string includes the filename (which is why you were
getting the error) and that the SourceTableName property is the WorkSheet or
Named range you want to connect to (if it is a sheet name then you append a
$ sign to the name as in the example above).