check before import Excel file

[Judy Ward]

I make it easy for users to import data from an Excel file to an Access
database by clicking a button on a form (in the database) that has this code
behind it:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblStatus",
importFile, True

I want to check the contents of a field in the Excel importFile and warn the
user if it is not what is expected.

Does anyone know how I would go about doing this?

Thank you,
Judy

 

[Judy Ward]

Chris,

Thank you very much for responding. This looks like exactly what I need,
except that I cannot get it to work .

The error that I am getting is: 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 name correctly.

This is what I am using:
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"

I used a Debug.Print statment to make sure that importFile path\name is
correct. I can open the file and see that the sheetname is "SWIT_SAR_Status"
(no typos).

I don't need a where condition because the "SWIT_IR_Tag" is the same for
every row of data (but that is the value I need to check).

Any idea what I am doing wrong?

Thank you,
Judy

You can run a query if you know the field name and have a way to identify the
row.

Dim db As Database
Dim strqry As String
Dim rs As DAO.Recordset

Set db = CurrentDb
strqry = "SELECT fieldname FROM [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile &"].sheetname " & _
"WHERE rowidentifier = 'Yankees'"
Set rs = db.OpenRecordset(strqry)
If (rs!fieldname >= 100) Then
MsgBox "It's high enough."
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "tblStatus", importFile, True
Else
MsgBox "It's not high enough. Don't import the spreadsheet."
End If

rs.Close
Set rs = Nothing
Set db = Nothing


Chris
Microsoft MVP

I make it easy for users to import data from an Excel file to an Access
database by clicking a button on a form (in the database) that has this code
behind it:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblStatus",
importFile, True

I want to check the contents of a field in the Excel importFile and warn the
user if it is not what is expected.

Does anyone know how I would go about doing this?

Thank you,
Judy

 

[Judy Ward]

Thanks again for responding.

Unfortunately, neither one of the suggestions worked. The first one results
in "Syntax error in FROM clause." The second one gives me the error:
"'SWIT_SAR_Status$' is not a valid name."

I also tried:
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status]"
and got the same error I already posted.

I'm using Access 2003 SP3.

I know that there must be a way to get this to work. Thank you for trying!
Judy

I don't think you're doing anything wrong. I think my service pack level let
me use a different syntax for the spreadsheet name because it shouldn't have
worked without a trailing $. Yet it does (for me). Try this:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status$"

and if that doesn't work for you try this:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status$]"


Chris
Microsoft MVP

Chris,

Thank you very much for responding. This looks like exactly what I need,
except that I cannot get it to work .

The error that I am getting is: 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 name correctly.

This is what I am using:
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"

I used a Debug.Print statment to make sure that importFile path\name is
correct. I can open the file and see that the sheetname is "SWIT_SAR_Status"
(no typos).

I don't need a where condition because the "SWIT_IR_Tag" is the same for
every row of data (but that is the value I need to check).

Any idea what I am doing wrong?

 

[Judy Ward]

I have officially entered the Twilight Zone.

I very carefully created a C:\db folder, copied the Excel file there,
renamed it test.xls and renamed the sheet "Status" (was even careful with
upper/lower case even though I don't think Windows cares). I also did what
you suggested and copied the name of the sheet to the code--just in case.

The code passes this condition:
If Dir(C:\db\test.xls) <> "" Then
so I know it can find the Excel file.

I used a Debug.Print on strSQL:
SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;DATABASE=c:\db\test.xls].Status

I still get the same error. 'Status' cannot be found. This is making me
crazy. Thank you very much for all your time and effort. If something else
occurs to you, please let me know. I may have to give up and either import
the data to a temporary table or link to the spreadsheet to run the query.

Thanks again,
Judy

I found a pc with Access 2003 sp3 installed to test with. I tried these
three syntax styles and they worked for me:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status$]"

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status]"

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"

The fact that none of these work for you means either the sheet name is
misspelled or the path and file name are misspelled.

Things to try:

1 - open the spreadsheet and double click on the sheet name at the bottom.
Push ctrl+c to copy it and paste it over the sheet name in your vba code.
Run the procedure again. Does that work? If not,

2 - Create a new folder c:\db and copy/paste the spreadsheet in it. Rename
this copy as test.xls. Rename the sheet in this copy as Status. Use this
code in your procedure for the query:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=c:\db\test.xls].Status"

Run the procedure again. Does that work?

Chris
Microsoft MVP

Thanks again for responding.

Unfortunately, neither one of the suggestions worked. The first one results
in "Syntax error in FROM clause." The second one gives me the error:
"'SWIT_SAR_Status$' is not a valid name."

I also tried:
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status]"
and got the same error I already posted.

I'm using Access 2003 SP3.

I know that there must be a way to get this to work. Thank you for trying!

 

[Judy Ward]

I did a google search and found this syntax:
strQry = "SELECT SWIT_IR_Tag FROM [Excel
8.0;Database=C:\db\test.xls;HDR=Yes].[Status$]"

This worked!!! You had already suggested this syntax, but I guess I tried
the other two examples (with brackets only and with a $ only and not with
both together).

This is the ONLY syntax that works for me (I retried the others).

Thank you for all your help!
Judy

I found a pc with Access 2003 sp3 installed to test with. I tried these
three syntax styles and they worked for me:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status$]"

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status]"

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].SWIT_SAR_Status"

The fact that none of these work for you means either the sheet name is
misspelled or the path and file name are misspelled.

Things to try:

1 - open the spreadsheet and double click on the sheet name at the bottom.
Push ctrl+c to copy it and paste it over the sheet name in your vba code.
Run the procedure again. Does that work? If not,

2 - Create a new folder c:\db and copy/paste the spreadsheet in it. Rename
this copy as test.xls. Rename the sheet in this copy as Status. Use this
code in your procedure for the query:

strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=c:\db\test.xls].Status"

Run the procedure again. Does that work?

Chris
Microsoft MVP

Thanks again for responding.

Unfortunately, neither one of the suggestions worked. The first one results
in "Syntax error in FROM clause." The second one gives me the error:
"'SWIT_SAR_Status$' is not a valid name."

I also tried:
strQry = "SELECT SWIT_IR_Tag from [Excel 8.0;HDR=Yes;" & _
"DATABASE=" & importFile & "].[SWIT_SAR_Status]"
and got the same error I already posted.

I'm using Access 2003 SP3.

I know that there must be a way to get this to work. Thank you for trying!