Jamie Collins - Excel to Access

  • Thread starter Thread starter Michael_Colby
  • Start date Start date
M

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
 
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
 
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
Click to expand...
[quoted text clipped - 12 lines]
Thanks,
Colby
Click to expand...
Click to expand...
 
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,


Michael_Colby said:
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
Click to expand...
[quoted text clipped - 12 lines]
Thanks,
Colby
Click to expand...
Click to expand...
Click to expand...
 
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!)


Michael_Colby said:
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
Click to expand...
[quoted text clipped - 12 lines]
Thanks,
Colby
Click to expand...
Click to expand...
Click to expand...
 
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"
On Error GoTo said:
Thanks for the quick reply but it is still not quite right.
The clean up part of the code bombs on...
Click to expand...
[quoted text clipped - 32 lines]
Click to expand...
 
I am trying to import an xls sheet into an existing table.

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;] "
Click to expand...

What about:

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

Jamie.

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

Jamie said:
I am trying to import an xls sheet into an existing table.
Click to expand...
[quoted text clipped - 8 lines]
"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;] "
Click to expand...

What about:

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

Jamie.

--
Click to expand...
 
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



Jamie said:
I am trying to import an xls sheet into an existing table.
Click to expand...
[quoted text clipped - 8 lines]
"SELECT status, serviceable " & _
"FROM [Excel 8.0;HDR=Yes,Database=C:\MyWorkbook.xls;] "
Click to expand...

What about:

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

Jamie.

--
Click to expand...
 
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
Click to expand...

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.

--
 
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.


Jamie Collins said:
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
Click to expand...

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.
Click to expand...
 
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


'69 Camaro said:
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.


Jamie Collins said:
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
Click to expand...

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.
Click to expand...
Click to expand...
 
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


Jamie said:
no luck. I get the err.msg that it can not find the input table/query.
Click to expand...
[quoted text clipped - 5 lines]
=) I checked and the file does exist as C:\MyWorkbook.xls
Click to expand...

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.

--
Click to expand...
 
Back
Top