Excel File From Asp

Discussion in 'ASP General' started by Stan Sainte-Rose, Jun 11, 2004.

  1. Hi
    I have to import Excel file from ASP.
    I do it well with the following code
    Const adUseClient = 3
    Const adClipString = 2
    Const Repertoire = "w:\wwwroot\look\j_consultation\j06_suivi\modules_stan\"
    Set adoCn = CreateObject("ADODB.Connection")
    Set adoRs = CreateObject("ADODB.Recordset")
    With adoCn
    ..Provider = "Microsoft.Jet.OLEDB.4.0"
    ..ConnectionString = _
    "Data Source=" & Repertoire & "Essai.xls;" & _
    "Extended Properties=Excel 8.0;"
    ..CursorLocation = adUseClient
    ..Open
    End With
    strQuery = "SELECT * FROM [CONTROLE INTERNE$]"
    With adoRs
    Set .ActiveConnection = adoCn
    ..Open strQuery
    End With


    My question is how to get the Sheet Tab Name.
    Right now, with my code I have to write [CONTROLE INTERNE$]
    but how to do when you don't know the name ?

    Thanks for your help

    Stan
     
    Stan Sainte-Rose, Jun 11, 2004
    #1
    1. Advertising

  2. "Stan Sainte-Rose" wrote ...

    > My question is how to get the Sheet Tab Name.
    > Right now, with my code I have to write [CONTROLE INTERNE$]
    > but how to do when you don't know the name ?


    Use the Connection object's OpenSchema method e.g. (untested):

    Const adUseClient = 3
    Const adClipString = 2
    Const Repertoire = "w:\wwwroot\look\j_consultation\j06_suivi\modules_stan\"
    Const adSchemaTables = 20
    Set adoCn = CreateObject("ADODB.Connection")
    Set adoRs = CreateObject("ADODB.Recordset")
    With adoCn
    ..Provider = "Microsoft.Jet.OLEDB.4.0"
    ..ConnectionString = _
    "Data Source=" & Repertoire & "Essai.xls;" & _
    "Extended Properties=Excel 8.0;"
    ..CursorLocation = adUseClient
    ..Open
    Set adoRs = .OpenSchema(adSchemaTables)
    End With
    With adoRs
    Do Until .EOF
    MsgBox !TABLE_NAME
    .MoveNext
    Loop
    End With

    --
     
    Jamie Collins, Jun 11, 2004
    #2
    1. Advertising

  3. Hi Jamie!

    Are you sure with the exlamation (!) ?

    Microsoft VBScript compilation error '800a03ea'
    Syntax error
    /test.asp, line 19
    MsgBox !TABLE_NAME
    -------^

    I'd like to solve it, but I'm an ASP (and VBScript) newbie :)

    Thanx

    --
    R.V.


    "Jamie Collins" wrote:

    > "Stan Sainte-Rose" wrote ...
    >
    > > My question is how to get the Sheet Tab Name.
    > > Right now, with my code I have to write [CONTROLE INTERNE$]
    > > but how to do when you don't know the name ?

    >
    > Use the Connection object's OpenSchema method e.g. (untested):
    >
    > Const adUseClient = 3
    > Const adClipString = 2
    > Const Repertoire = "w:\wwwroot\look\j_consultation\j06_suivi\modules_stan\"
    > Const adSchemaTables = 20
    > Set adoCn = CreateObject("ADODB.Connection")
    > Set adoRs = CreateObject("ADODB.Recordset")
    > With adoCn
    > ..Provider = "Microsoft.Jet.OLEDB.4.0"
    > ..ConnectionString = _
    > "Data Source=" & Repertoire & "Essai.xls;" & _
    > "Extended Properties=Excel 8.0;"
    > ..CursorLocation = adUseClient
    > ..Open
    > Set adoRs = .OpenSchema(adSchemaTables)
    > End With
    > With adoRs
    > Do Until .EOF
    > MsgBox !TABLE_NAME
    > .MoveNext
    > Loop
    > End With
    >
    > --
    >
     
    Radovan Vojtek, Aug 4, 2004
    #3
  4. "Radovan Vojtek" wrote ...

    > Hi Jamie!
    >
    > Are you sure with the exlamation (!) ?
    >
    > Microsoft VBScript compilation error '800a03ea'
    > Syntax error
    > /test.asp, line 19
    > MsgBox !TABLE_NAME
    > -------^


    Yes, but only within the With clause i.e. could be rewritten as

    MsgBox adoRs!TABLE_NAME

    or

    MsgBox adoRs.Fields("TABLE_NAME").Value

    Jamie.

    --
     
    Jamie Collins, Aug 5, 2004
    #4
  5. Thanx, Jamie, it works!!! godlike

    R.V.

    "Jamie Collins" wrote:

    > "Radovan Vojtek" wrote ...
    >
    > > Hi Jamie!
    > >
    > > Are you sure with the exlamation (!) ?
    > >
    > > Microsoft VBScript compilation error '800a03ea'
    > > Syntax error
    > > /test.asp, line 19
    > > MsgBox !TABLE_NAME
    > > -------^

    >
    > Yes, but only within the With clause i.e. could be rewritten as
    >
    > MsgBox adoRs!TABLE_NAME
    >
    > or
    >
    > MsgBox adoRs.Fields("TABLE_NAME").Value
    >
    > Jamie.
    >
    > --
    >
     
    Radovan Vojtek, Aug 5, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,405
    Carl Prothman [MVP]
    Jan 12, 2005
  2. Anonieko
    Replies:
    2
    Views:
    7,346
    JackStraw73
    Feb 12, 2008
  3. Replies:
    1
    Views:
    1,805
    extentechjohn
    Nov 21, 2006
  4. Robbe Morris [C# MVP]

    asp.net extracting excel charts from an Excel file

    Robbe Morris [C# MVP], Dec 15, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    492
    Robbe Morris [C# MVP]
    Dec 15, 2006
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,548
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page