these code for connect with excel sheet but here give me error plz cheak it.in line no. 9 give me error
Dim cn As ADODB.Connectio n
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connectio n '(************* *****HERE give error that INVALID OUTSIDE PROSEDURE)
With cn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = "Data Source=" & App.Path & _
"\ExcelSrc.xls; Extended Properties=Exce l 8.0;"
'.Provider = "MSDASQL"
'.ConnectionStr ing = "Driver={Micros oft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc. xls; "
.CursorLocation = adUseClient
.Open
End With
Set rsT = cn.OpenSchema(a dSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Coun t
List1.AddItem "Tables: " & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
strTbl = rsT.Fields("TAB LE_NAME").Value
List1.AddItem vbTab & "Table #" & t & ": " & strTbl
List1.AddItem vbTab & "--------------------"
For f = 0 To intTblFlds - 1
List1.AddItem vbTab & rsT.Fields(f).N ame & _
vbTab & rsT.Fields(f).V alue
Next
List1.AddItem "--------------------"
Set rsC = cn.OpenSchema(a dSchemaColumns, Array(Empty, Empty, strTbl, Empty))
intColCnt = rsC.RecordCount
intColFlds = rsC.Fields.Coun t
For c = 1 To intColCnt
strCol = rsC.Fields("COL UMN_NAME").Valu e
List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
List1.AddItem vbTab & vbTab & "--------------------"
For f = 0 To intColFlds - 1
List1.AddItem vbTab & vbTab & rsC.Fields(f).N ame & _
vbTab & rsC.Fields(f).V alue
Next
List1.AddItem vbTab & vbTab & "--------------------"
rsC.MoveNext
Next
rsC.Close
List1.AddItem "--------------------"
rsT.MoveNext
Next
rsT.Close
cn.Close
Dim cn As ADODB.Connectio n
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connectio n '(************* *****HERE give error that INVALID OUTSIDE PROSEDURE)
With cn
.Provider = "Microsoft.Jet. OLEDB.4.0"
.ConnectionStri ng = "Data Source=" & App.Path & _
"\ExcelSrc.xls; Extended Properties=Exce l 8.0;"
'.Provider = "MSDASQL"
'.ConnectionStr ing = "Driver={Micros oft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc. xls; "
.CursorLocation = adUseClient
.Open
End With
Set rsT = cn.OpenSchema(a dSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Coun t
List1.AddItem "Tables: " & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
strTbl = rsT.Fields("TAB LE_NAME").Value
List1.AddItem vbTab & "Table #" & t & ": " & strTbl
List1.AddItem vbTab & "--------------------"
For f = 0 To intTblFlds - 1
List1.AddItem vbTab & rsT.Fields(f).N ame & _
vbTab & rsT.Fields(f).V alue
Next
List1.AddItem "--------------------"
Set rsC = cn.OpenSchema(a dSchemaColumns, Array(Empty, Empty, strTbl, Empty))
intColCnt = rsC.RecordCount
intColFlds = rsC.Fields.Coun t
For c = 1 To intColCnt
strCol = rsC.Fields("COL UMN_NAME").Valu e
List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
List1.AddItem vbTab & vbTab & "--------------------"
For f = 0 To intColFlds - 1
List1.AddItem vbTab & vbTab & rsC.Fields(f).N ame & _
vbTab & rsC.Fields(f).V alue
Next
List1.AddItem vbTab & vbTab & "--------------------"
rsC.MoveNext
Next
rsC.Close
List1.AddItem "--------------------"
rsT.MoveNext
Next
rsT.Close
cn.Close
Comment