Query an Excel spreadsheet from Access

  • Thread starter Thread starter Judy Ward
  • Start date Start date
J

Judy Ward

I would like to query an Excel spreadsheet using code behind an Access form
to check a value in the spreadsheet before importing it.

This is what I am using--but it's not working:
(importFile is the path/filename and I'm sure that is correct)
Dim db As Database
Dim strQry As String
Dim rs As DAO.Recordset
Set db = CurrentDb
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"
Set rs = db.OpenRecordset(strQry)

I get: Run-time error '3011':
The Microsoft Jet database engine could not find the object
'SWIT_SAR_Status'. Make sure the object exists and that you spell its name
and the path correctly.

I am sure that the object exists and that the name is correct. I have even
tried renaming the spreadsheet to something more simple. I have tried using
SWIT_SAR_Status$. I am using Access 2003 SP3.

If I were able to get the recordset, I was just going to go the the first
record and check the value of the field "SWIT_IR_Tag".

Does anyone know what I am doing wrong or have a suggestion for another way
to accompllish this?

Any help would be appreciated.

Thank you,
Judy
 
why are you importing the spread sheet why dont you just make it a
linked table then you dont have to manually import it each time

the linked table will to that for you and you can treat it as a table
for queries and what not.
 
I want to check one field for correct data before importing the contents of
the Excel spreadsheet into a table in the database. After importing, the
Excel file is renamed and the process is repeated over again (frequently).

I realize that I can temporarily link to the spreadsheet or import the data
to a temporary table, but if there is a way to query the spreadsheet, that is
what I would like to do.
 
mmm you may have to reference the excel program and run the tests
through vba as an example of this i have ms word opening access and
running commands in access from word and then the same in excel it
coudl eb something you may want to try

what seems to be the problem is that the file reference is wrong try
to see if your string is correct

run this vba code

Dim db As Database
Dim strQry As String
Dim rs As DAO.Recordset
Set db = CurrentDb
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"
msgbox strqry
'Set rs = db.OpenRecordset(strQry)

note i added msgbox and commented out the set rs

have a look at the msgbox output because there may be somethign wrong
in there that is not allowing the spreadsheet swit_sar_status to be
found

regards
Kelvan
 
Back
Top