Excel File From Asp

[Stan Sainte-Rose]

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

 

[Jamie Collins]

...

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]

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.

...

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]

...

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]

Thanx, Jamie, it works!!! godlike

R.V.

...


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.