Importing Excel 2007 document only works when Excel is open

[Rico]

Hello,

I have an Excel 2007 document that I'm trying to create an import function
for using DAO to read the file. The problem is, it only works when I have
the Excel document open. IF I close Excel, I get a err.number 91 "Object
variable or with block...." and if I change the "Excel 8.0" to "Excel 12.0",
I get a "Couldn't find installable ISAM" error. Any ideas? Here is the
code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Douglas J. Steele]

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")

 

[Rico]

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and doesn't
work when it is closed. This is the line that it's having trouble with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

....and my apolobies, the error isn't a 91 error it's a 3274 : External table
is not in the expected format.

Rick

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only works
when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Douglas J. Steele]

Don't know what to say. The "smart" quotes did not work for me in my test,
the square brackets did. I also didn't have any space after the semi-colon.

What happens when you try to import the spreadsheet? Does Access have a
problem with it then?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and
doesn't work when it is closed. This is the line that it's having trouble
with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

...and my apolobies, the error isn't a 91 error it's a 3274 : External
table is not in the expected format.

Rick

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only works
when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Ken Snell MVP]

EXCEL 2007 is not in the EXCEL 8.0 format. It's Excel 12.0:

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 12.0; ")

See http://word.mvps.org/faQs/InterDev/FillListBoxFromXLDAO.htm for example
of this.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and
doesn't work when it is closed. This is the line that it's having trouble
with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

...and my apolobies, the error isn't a 91 error it's a 3274 : External
table is not in the expected format.

Rick

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only works
when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Rico]

Hi Ken,

As I previously stated, when I try to change the type to Excel 12.0, I get
an "Couldn't find installable ISAM" error.

Rick

EXCEL 2007 is not in the EXCEL 8.0 format. It's Excel 12.0:

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 12.0; ")

See http://word.mvps.org/faQs/InterDev/FillListBoxFromXLDAO.htm for
example of this.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and
doesn't work when it is closed. This is the line that it's having
trouble with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

...and my apolobies, the error isn't a 91 error it's a 3274 : External
table is not in the expected format.

Rick

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only
works when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Rico]

Hi Doug,

Access will import it if the spreadsheet is open in Excel, but doesn't if
Excel and the spreadsheet is closed.

Rick

Don't know what to say. The "smart" quotes did not work for me in my test,
the square brackets did. I also didn't have any space after the
semi-colon.

What happens when you try to import the spreadsheet? Does Access have a
problem with it then?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and
doesn't work when it is closed. This is the line that it's having
trouble with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

...and my apolobies, the error isn't a 91 error it's a 3274 : External
table is not in the expected format.

Rick

The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only
works when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Douglas J. Steele]

That's not normal. Have you tried doing a repair (or reinstall) of Office?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Access will import it if the spreadsheet is open in Excel, but doesn't if
Excel and the spreadsheet is closed.

Rick

Don't know what to say. The "smart" quotes did not work for me in my
test, the square brackets did. I also didn't have any space after the
semi-colon.

What happens when you try to import the spreadsheet? Does Access have a
problem with it then?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The quotes work, (keeping in mind it's an Excel worksheet that's being
referenced) but the problem is it only works when Excel is open, and
doesn't work when it is closed. This is the line that it's having
trouble with

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")

...and my apolobies, the error isn't a 91 error it's a 3274 : External
table is not in the expected format.

Rick



The quotes in your SELECT statement look incorrect to me.

Try:

Set rs = dbtmp.OpenRecordset("select * from [" & _
dbtmp.TableDefs(0).Name & "]")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello,

I have an Excel 2007 document that I'm trying to create an import
function for using DAO to read the file. The problem is, it only
works when I have the Excel document open. IF I close Excel, I get a
err.number 91 "Object variable or with block...." and if I change the
"Excel 8.0" to "Excel 12.0", I get a "Couldn't find installable ISAM"
error. Any ideas? Here is the code I'm working with.

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Dim strFirstName As String

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0; ")
Set rs = dbtmp.OpenRecordset("select * from `" &
dbtmp.TableDefs(0).Name & "`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally
Resume

End Function

Thanks!

 

[Ken Snell MVP]

Sorry... my old eyes missed that sentence in your original post.