Link to home
Start Free TrialLog in
Avatar of harris9999
harris9999Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Fine name of sheet/Table of uploaded XLS File (ASP)

I have script which uploads a xls document and inserts them into an access database by asp.
I select the records from the xls document with the statement:

set ExcelRS = ExcelConn.Execute ("SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM Contacts")

This works fine if the sheet is called Contacts.  But crashes if it is not.  Is it possible to find out the sheet name when the file is uploaded.
Avatar of Ardhendu Sarangi
Ardhendu Sarangi
Flag of United States of America image

Since you haven't given your complete code.. this is just a guess...

try the following code:

xlsName = thisworkbook.name
set ExcelRS = ExcelConn.Execute ("SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM" &xlsName)

and see if this works..

Ardhendu
Avatar of harris9999

ASKER

My code is:
		
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open filepath & "\" & filename
 
set ExcelRS = ExcelConn.Execute ("SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM Contacts")

Open in new window

Ok, can you try this and see if this works for you?

- Ardhendu
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open filepath & "\" & Filename
 
Set ExcelRS = ExcelConn.Execute("SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM " & Filename)

Open in new window

No getting the following error:

SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM csv-sample(3).xls
Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

/contacts/add/addcsv.asp, line 87

Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open filepath & "\" & filename
 
'get data from sheet
'set ExcelRS = ExcelConn.Execute ("SELECT FirstName, LastName, [EmailAddress], Country, Company FROM [csv-sample$]")
sSQL="SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM " & filename
response.Write("<br>" & sSQL)
set ExcelRS = ExcelConn.Execute (sSQL)     'Line 87

Open in new window

Can you try replacing my previous post with these lines below?

- Ardhendu.
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open filepath & "\" & Filename
 
Set ExcelRS = ExcelConn.Execute("SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM " & Left(Filename, Len(Filename) - 4))

Open in new window

No still gettin the error:

SELECT FirstName, LastName, EmailAddress, HomeCountry, Company FROM csv-sample(4)
Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

Hi Harris,
Is "cvs-sample(4)" the exact name of excel file where you want to run the Query on? I am not sure why this is erroring out when the original file Contact.xls is working fine... I am not sure what I am missing here...

- Ardhendu
ASKER CERTIFIED SOLUTION
Avatar of harris9999
harris9999
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial