read spreadsheet with dao

[J Miller]

I would like load a table in Access 2000 from an Excel Spreadsheet using
DAO. Something like this:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.opendatabase("mytable")

open spreadsheet/worksheet
read each spreadsheet record
mytable.addnew
add the fields from the spreadsheet
mytable.update

How do I open the spreadsheet, point to the correct worksheet, and read each
record?


Many many thanks.

Jeff

 

[Dirk Goldgar]

I would like load a table in Access 2000 from an Excel Spreadsheet
using DAO. Something like this:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.opendatabase("mytable")

open spreadsheet/worksheet
read each spreadsheet record
mytable.addnew
add the fields from the spreadsheet
mytable.update

How do I open the spreadsheet, point to the correct worksheet, and
read each record?


Many many thanks.

Jeff

While you can do what you want, Jeff, I think it would be a lot easier
either to import the spreadsheet wholesale to a new table, or else link
to the spreadsheet as a table and then use an append query to copy the
records from this linked table into your target table.

You can link or import the spreadsheet by way of the user interface,
using the File -> Get External Data... menu options. If you need to do
it in code, you can use the DoCmd.TransferSpreadsheet method. Have you
considered thse approaches?

 

[J Miller]

Actually right now I am using the DoCmd.TransferSpreadsheet method, but the
spreadsheets will be password protected. Can you use
DoCmd.TransferSpreadsheet method with password protected spreadsheets? If so
how?


Jeff

 

[Dirk Goldgar]

Actually right now I am using the DoCmd.TransferSpreadsheet method,
but the spreadsheets will be password protected. Can you use
DoCmd.TransferSpreadsheet method with password protected
spreadsheets? If so how?

Hmm, probably not, but you may be able to use DAO to create a tabledef
with a connect string that will get you in. Exactly how is the
spreadsheet protected? What steps were taken in Excel to protect it?
Is it the workbook or just the worksheet that was password-protected?

 

[J Miller]

The workbook has been protected by doing a 'save as'/tools/general options'
and then entering the password. The worksheets are not protected. I have the
password, but right now I have to open the workbook and do my own 'save as'
and remove the password and save it so I can process it with the
TransferSpreadsheet method. I am dealing with a truckload of separate
workbooks so it will take forever to do this to each one. Each workbook is
layed out in the same format except some have more rows then others.

I was shooting for something like this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sfilename As String
sfilename = "c:\myfile.xls"
Set db = DBEngine.OpenDatabase(sfilename, False, True, "Excel
8.0;PWD=xxx")

But when this code runs I get a 'Run time error 3161 could not decrypt file'
on the 'Set db' line.

Hence my delimna!


Jeff

 

[Douglas J. Steele]

According to http://support.microsoft.com/?id=257819 "If the Excel workbook
is protected by a password, you cannot open it for data access, even by
supplying the correct password with your connection settings, unless the
workbook file is already open in the Microsoft Excel application."

 

[J Miller]

I came across that but was hoping there was some magic that someone knew of.
It's starting to look pretty bleak.

Jeff

 

[Dirk Goldgar]

I came across that but was hoping there was some magic that someone
knew of. It's starting to look pretty bleak.

Jeff

Have you tried automating Excel to open the workbook in question? I'm
not sure if that would allow you to link with DAO then, or whether you'd
have to loop through the rows in the worksheet and get the data that
way.

 

[Brooke Armendarez]

Hmm, probably not, but you may be able to use DAO to create a tabledef
with a connect string that will get you in. Exactly how is the
spreadsheet protected? What steps were taken in Excel to protect it?
Is it the workbook or just the worksheet that was password-protected?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Ken Snell [MVP]]

Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like). You'd need to add the
password argument to the Workbooks.Open method.



Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

 

[J Miller]

lol. Great minds think alike!

Yesterday I started dabbling with an Excel macro to do just that. I figure I
can loop and open every workbook and then save it without a password. I have
never coded in Excel so am not real familiar with all the methods, etc., but
it can't be much different then VB. I will take a look at the code below.

Thanks!


Jeff

 

[Jamie Collins]

Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like).

Another approach might be to open the workbook with the password and
save the relevant sheets to a temporary workbook without a password.
The temp workbook could then be closed and queried then deleted.
Reduced run time but OP would have to decide whether this would raise
a security issues.

Jamie.

--