Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Join NowFor Free!

Thread: Classic ASP: read Excel file into Recordset

Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
Share This Thread →
  1. May 15th, 2007, 01:22 PM #16
    mehere's Avatar
    mehere
    mehere is offline Senior Sarcasm Wizardess ASP Free God (5000+ posts)
    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2461
    yes ... as long as table name is worksheet name, and you have column headings. it's as if it's a regular database.
    Quote of the Month:
    Planning: Much work remains to be done before we can announce our total failure to make any progress.

    Questions to Ponder:
    If convenience stores are open 24 hours a day, 365 days a year, why are there locks on the doors?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright© 2008 sbenj69
    Give Rep
  2. May 15th, 2007, 01:33 PM #17
    stscac
    stscac is offline Registered User ASP Free Newbie (0 - 499 posts)
    Join Date
    May 2007
    Posts
    11
    Rep Power
    0
    Great. Thanks for the help

    -Steve
    Give Rep
  3. May 22nd, 2007, 11:42 AM #18
    Ben Hofmann
    Ben Hofmann is offline Registered User ASP Free Newbie (0 - 499 posts)
    Join Date
    May 2007
    Posts
    2
    Rep Power
    0
    Anybody know if you can sort by multiple criteria?

    Here is code from a project I'm working on for a employee list by department.

    Code:
    strSQL = "SELECT * from photorange ORDER BY [Department]"
    I would like to order first by department, then by last name similar to what Excel will do with a data sort with multiple criteria. How would I do this?

    -Ben
    Give Rep
  4. May 22nd, 2007, 11:47 AM #19
    mehere's Avatar
    mehere
    mehere is offline Senior Sarcasm Wizardess ASP Free God (5000+ posts)
    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2461
    just add the additional sort options at the end
    Code:
    strSQL = "SELECT * from photorange ORDER BY [Department], [LastName]"
    Give Rep
  5. May 22nd, 2007, 05:32 PM #20
    Ben Hofmann
    Ben Hofmann is offline Registered User ASP Free Newbie (0 - 499 posts)
    Join Date
    May 2007
    Posts
    2
    Rep Power
    0
    That was easy. Thanks!

    -Ben
    Give Rep
  6. April 9th, 2008, 09:18 AM #21
    ajayp40
    ajayp40 is offline Contributing User ASP Free Newbie (0 - 499 posts)
    Join Date
    Aug 2007
    Posts
    142
    Rep Power
    6
    Small tip for people reading over this this line of code

    Code:
    strSQL = "SELECT * FROM A2:Q10000"
    Is where you can change the tables cords depending on how many rows you have in your excel spreadsheet allowing you to have more rows or collumns.

    A2:Q10000
    Give Rep
  7. April 15th, 2010, 09:20 AM #22
    emzipoo4u
    emzipoo4u is offline Contributing User ASP Free Newbie (0 - 499 posts)
    Join Date
    Oct 2007
    Posts
    175
    Rep Power
    14
    Sorry to bump an old thread, will this work with open office installed and not excel ?

    Is there anyway in achieving the same without office installed e.g. by reading a csv or text file.
    Give Rep
  8. April 17th, 2010, 03:17 PM #23
    Shadow Wizard's Avatar
    Shadow Wizard
    Shadow Wizard is offline Moderator From Beyond ASP Free God (5000+ posts)
    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,123
    Rep Power
    2920
    Quote Originally Posted by emzipoo4u
    Sorry to bump an old thread, will this work with open office installed and not excel ?

    Is there anyway in achieving the same without office installed e.g. by reading a csv or text file.
    no and no.
    you'll have to find proper driver, if exists at all, for Open Office documents.
    to read pure text file, use FSO, which is File System Object.

    good luck!



    Visit The Windows Scripting Guru***There Is No Magic***I didn't reply recently? Click here to see why
    Give Rep
  9. July 15th, 2010, 09:42 AM #24
    jonyBravo
    jonyBravo is offline Contributing User ASP Free Newbie (0 - 499 posts)
    Join Date
    Jul 2007
    Posts
    174
    Rep Power
    6

    ASP & Escel

    Hi there

    I've bee using some script given on this forum by Shadow Wizard to get information from a exel document to a MSSQL database.
    It has been working very well until last month when our boss upgrade our machine with office 2007. Unless users ask to save
    as excel 2003 when they upload the document it gives an error. I think is because the webserver is running on server 2003.
    Is there any way I can sort this out?

    Thank you
    If this help you please show your gratitude by adding reputation points.

    Thanks

    JonyBravo
    Give Rep
  10. July 20th, 2010, 05:46 AM #25
    Shadow Wizard's Avatar
    Shadow Wizard
    Shadow Wizard is offline Moderator From Beyond ASP Free God (5000+ posts)
    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,123
    Rep Power
    2920
    I guess you mean this thread? If not sorry, but it's related still.

    Anyway I now edited the first post in the thread with code supporting
    Excel 2007 files as well, just comment your original objConn.Open lines
    and add the lines I've added in there.

    To successfully open XLSX file you simply need to specify different
    driver in the connection string, that's all..
    Give Rep
  11. May 6th, 2011, 04:12 AM #26
    gillianreynolds
    gillianreynolds is offline Registered User ASP Free Newbie (0 - 499 posts)
    Send a message via ICQ to gillianreynolds Send a message via MSN to gillianreynolds Send a message via Yahoo to gillianreynolds
    Join Date
    May 2011
    Posts
    2
    Rep Power
    0
    se the below code to read from Excel file as if it was database. note, Excel must be installed on the Server in order for this code to work, and the IUSR account must have permissions over the Excel file. Code: <% Option Explicit %> <html> <body> <% Dim objConn, objRS, strSQL Dim x, curValue Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DRIVER={Microsoft Excel Driver (*....

    Domain info for: aspfree.com
    Give Rep
  12. May 6th, 2011, 04:16 AM #27
    gillianreynolds
    gillianreynolds is offline Registered User ASP Free Newbie (0 - 499 posts)
    Send a message via ICQ to gillianreynolds Send a message via MSN to gillianreynolds Send a message via Yahoo to gillianreynolds
    Join Date
    May 2011
    Posts
    2
    Rep Power
    0
    Right information and knowledge.



    Thanks!!

    Gillian Reynolds
    Give Rep
  13. January 12th, 2012, 05:51 AM #28
    asipoy
    asipoy is offline Registered User ASP Free Newbie (0 - 499 posts)
    Join Date
    Jan 2012
    Posts
    2
    Rep Power
    0

    Question

    Hi All,

    I am adding to the old post.

    I modified the code a bit and tried to update access database after uploading excel file but I am getting the following error message:

    Error Type: Provider (0x80020005)
    Type mismatch.
    line 61

    the error that I receive is on the below line

    Code:
    rsAdd.Fields(xIndex).value=arrMy(xIndex)
    I have double checked that the data types for each field in Access and Excel are same.

    Could any one please look into this and assist me.

    Thanks and Regards
    Give Rep
  14. January 12th, 2012, 06:06 AM #29
    Shadow Wizard's Avatar
    Shadow Wizard
    Shadow Wizard is offline Moderator From Beyond ASP Free God (5000+ posts)
    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,123
    Rep Power
    2920
    arrMy(x) will always be a string - it means you have one or more fields holding other data type so you'll have to cast the value using functions like CLng, CDate etc according to the type you have and the field index.
    Give Rep
  15. February 21st, 2012, 12:54 AM #30
    asipoy
    asipoy is offline Registered User ASP Free Newbie (0 - 499 posts)
    Join Date
    Jan 2012
    Posts
    2
    Rep Power
    0
    Thanks Shadow Wizard for your support and time
    I resolved this problem...

    The problem occurs when you have a column in the excel file which is not present in the MSAccess table and vice versa.

    Also there should be no empty fields in the excel file. (this happen even if the column is not a primary key)

    Thanks to all the members again....


    Quote Originally Posted by Shadow Wizard
    arrMy(x) will always be a string - it means you have one or more fields holding other data type so you'll have to cast the value using functions like CLng, CDate etc according to the type you have and the field index.
    Give Rep
Page 2 of 2 FirstFirst 12
Share This Thread →
« Previous Thread | Next Thread »

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Xml file read but html values not coming
    By shaileshk in forum ASP Development
    Replies: 10
    Last Post: June 7th, 2006, 04:50 AM
  2. Get a excel file from client machine
    By satyajit in forum ASP Development
    Replies: 1
    Last Post: April 21st, 2006, 07:25 AM
  3. IIS 6.0 Crashing Daily on Production Server
    By JMelsoner in forum Microsoft IIS
    Replies: 5
    Last Post: April 12th, 2006, 05:43 PM
  4. kindly check what is wrong in checking an excel file open or not
    By kiran_karnati in forum Visual Basic Programming
    Replies: 1
    Last Post: September 17th, 2004, 07:30 AM
  5. How can I read the data from txt file and insert these into sql table
    By Steve Schofield in forum ASP Development
    Replies: 1
    Last Post: February 4th, 2002, 03:51 AM

ASP Free Advertisers and Affiliates