[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-17
8
Medium Priority
?
472 Views
Last Modified: 2013-11-19
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.
0
Comment
Question by:harris9999
  • 4
  • 4
8 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 23666713
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
0
 
LVL 3

Author Comment

by:harris9999
ID: 23668388
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

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 23669597
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:harris9999
ID: 23670470
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

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 23672466
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

0
 
LVL 3

Author Comment

by:harris9999
ID: 23673332
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.

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 23680615
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
0
 
LVL 3

Accepted Solution

by:
harris9999 earned 0 total points
ID: 23686184
Hi,
Yes that is the correct filename.  Is it something to do with the Contacts Exported from outlook having a "named Range"?

I have changed the driver and it works with that by a different method.  Maybe this is a poor sql statement.  What do you think?
Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
    "Excel 8.0; DBQ=" & filepath & filename & "; "
 
sSQL = "SELECT * FROM A1:BL10000"
 
response.Write("<br>" & sSQL)
set ExcelRS = ExcelConn.Execute (sSQL)

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question