Jamie Collins - Excel to Access

[Michael_Colby]

I am trying to import an xls sheet into an existing table. I found some
ref's to you and your answers but have not had any luck. I am using the
below string; however, I get the err.msg that the jet db can not find the
input table .....

any help will be greatly appreciated.

sSQL = "" & _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;] "

CurrentDb().Execute (sSQL)

Thanks,
Colby

 

[Chergh]

Try this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tbl_xl_import", "C:\MyWorkbook.xls", True

Then

sSQL = "" & _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM tbl_xl_import"

then include this next bit of code to clean up.

dim tbl as tabledef

For Each tbl In TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete ("tbl_xl_import")
Next tbl

 

[Michael_Colby]

Thanks for the quick reply but it is still not quite right.
The clean up part of the code bombs on...
For Each tbl In TableDefs

it tells me object required. when I step thru tabledef and tbl are both
empty; any guesses.

Thanks again,
Colby

Try this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tbl_xl_import", "C:\MyWorkbook.xls", True

Then

sSQL = "" & _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM tbl_xl_import"

then include this next bit of code to clean up.

dim tbl as tabledef

For Each tbl In TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete ("tbl_xl_import")
Next tbl

I am trying to import an xls sheet into an existing table. I found some
ref's to you and your answers but have not had any luck. I am using the

[quoted text clipped - 12 lines]

Thanks,
Colby

 

[George Nicholson]

Tabledef(s) are DAO objects. In the VBE, under Tools>References, make sure
you have the reference for "Microsoft DAO 3.6 Object Library" checked.

HTH,

Thanks for the quick reply but it is still not quite right.
The clean up part of the code bombs on...
For Each tbl In TableDefs

it tells me object required. when I step thru tabledef and tbl are both
empty; any guesses.

Thanks again,
Colby

Try this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tbl_xl_import", "C:\MyWorkbook.xls", True

Then

sSQL = "" & _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM tbl_xl_import"

then include this next bit of code to clean up.

dim tbl as tabledef

For Each tbl In TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete ("tbl_xl_import")
Next tbl

I am trying to import an xls sheet into an existing table. I found some
ref's to you and your answers but have not had any luck. I am using the

[quoted text clipped - 12 lines]

Thanks,
Colby

 

[Douglas J. Steele]

For Each tbl In db.CurrentDb()

However, I don't see the point in looping through the entire TableDefs
collection to delete a single table. At the very least, that should be:

For Each tbl In db.TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete "tbl_xl_import"
Exit For
End If
Next tbl

I'd be more inclined, though, to turn off error trapping and just delete the
table:

On Error Resume Next
db.TableDefs.Delete "tbl_xl_import"
On Error GoTo <label>


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick reply but it is still not quite right.
The clean up part of the code bombs on...
For Each tbl In TableDefs

it tells me object required. when I step thru tabledef and tbl are both
empty; any guesses.

Thanks again,
Colby

Try this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tbl_xl_import", "C:\MyWorkbook.xls", True

Then

sSQL = "" & _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM tbl_xl_import"

then include this next bit of code to clean up.

dim tbl as tabledef

For Each tbl In TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete ("tbl_xl_import")
Next tbl

I am trying to import an xls sheet into an existing table. I found some
ref's to you and your answers but have not had any luck. I am using the

[quoted text clipped - 12 lines]

Thanks,
Colby

 

[Michael_Colby via AccessMonster.com]

George, thanks for the suggestion; however, I already had the dao ref checked.

Doug - you got it. for each tbl in currentdb().tabledefs solved the
problem

Thanks again for all the help.

For Each tbl In db.CurrentDb()

However, I don't see the point in looping through the entire TableDefs
collection to delete a single table. At the very least, that should be:

For Each tbl In db.TableDefs
If tbl.Name = "tbl_xl_import" Then
db.TableDefs.Delete "tbl_xl_import"
Exit For
End If
Next tbl

I'd be more inclined, though, to turn off error trapping and just delete the
table:

On Error Resume Next
db.TableDefs.Delete "tbl_xl_import"

Thanks for the quick reply but it is still not quite right.
The clean up part of the code bombs on...

[quoted text clipped - 32 lines]

 

[Michael_Colby via AccessMonster.com]

I will give it a shot and post back with the results. Thanks

I am trying to import an xls sheet into an existing table.

[quoted text clipped - 8 lines]

"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;] "

What about:

sSQL = _
"SELECT status, serviceable" & _
" INTO [Audit Summary]" & _
" FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;];"

Jamie.

--

 

[Michael_Colby via AccessMonster.com]

Jamie, no luck. I get the err.msg that it can not find the input table/query.

sSQL = _
"SELECT status, serviceable" & _
" INTO [Audit Summary]" & _
" FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;];"
CurrentDb().Execute (sSQL)

=) I checked and the file does exist as C:\MyWorkbook.xls

I am trying to import an xls sheet into an existing table.

[quoted text clipped - 8 lines]

"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;] "

What about:

sSQL = _
"SELECT status, serviceable" & _
" INTO [Audit Summary]" & _
" FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;];"

Jamie.

--

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Aaron using the same IP address on this post as
he does when he posts as (e-mail address removed).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

no luck. I get the err.msg that it can not find the input table/query.

sSQL = _
"SELECT status, serviceable" & _
" INTO [Audit Summary]" & _
" FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;];"
CurrentDb().Execute (sSQL)

=) I checked and the file does exist as C:\MyWorkbook.xls

Oops! You're missing the *source* table i.e. a worksheet or Name that
defines a Range a.k.a. "named Range"

e.g.

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].[MySheet$];

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].MyNamedRange;

Jamie.

 

['69 Camaro]

Oops! Pardon me. My mistake. Jamie Collins and (e-mail address removed)
use the same computer in the UK. You picked a bad time to post when Aaron's
been busy impersonating others in the newsgroup, Jamie. I thought it was
him.

Gunny

Everyone please note that Aaron Kem.pf is attempting to impersonate one
of our regular posters again. Aaron using the same IP address on this
post as he does when he posts as (e-mail address removed).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

no luck. I get the err.msg that it can not find the input table/query.

sSQL = _
"SELECT status, serviceable" & _
" INTO [Audit Summary]" & _
" FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;];"
CurrentDb().Execute (sSQL)

=) I checked and the file does exist as C:\MyWorkbook.xls

Oops! You're missing the *source* table i.e. a worksheet or Name that
defines a Range a.k.a. "named Range"

e.g.

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].[MySheet$];

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].MyNamedRange;

Jamie.

 

[Michael_Colby via AccessMonster.com]

Thanks Jamie.

I ended up w/
sSQL = _
"INSERT INTO [Audit Summary] " & _
"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=YES,Database=H:\MyWorkbook.xls;].MyData;"
CurrentDb().Execute (sSQL)

..MyData is a named range b/c I could not get the sheet reference to work.

Thanks again,
Colby

no luck. I get the err.msg that it can not find the input table/query.

[quoted text clipped - 5 lines]

=) I checked and the file does exist as C:\MyWorkbook.xls

Oops! You're missing the *source* table i.e. a worksheet or Name that
defines a Range a.k.a. "named Range"

e.g.

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].[MySheet$];

SELECT *
FROM [Excel 8.0;DATABASE=C:\Tempo\db.xls;].MyNamedRange;

Jamie.

--