harris9999
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.
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.
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")
Ok, can you try this and see if this works for you?
- Ardhendu
- 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)
ASKER
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
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
Can you try replacing my previous post with these lines below?
- Ardhendu.
- 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))
ASKER
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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