set cn=CreateObject("adodb.connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.xls;Extended Properties=""Excel 8.0"";" Set cat=CreateObject("ADOX.Catalog") cat.ActiveConnection = cn for each tbn in cat.Tables if right(tbn.name,1)="$" or (left(tbn.name,1)="'" and right(tbn.name,1)="'") then msgbox tbn.name end if next set cat=nothing
class CTables : public CRecordset { DECLARE_DYNAMIC(CTables)public: CTables(CDatabase* pDatabase = NULL); BOOL Open(UINT nOpenType = forwardOnly, LPCSTR lpszSQL = NULL, DWORD dwOptions = readOnly);// Field/Param Data //{{AFX_FIELD(CTables, CRecordset) CString m_strQualifier; CString m_strOwner; CString m_strName; CString m_strType; CString m_strRes; //}}AFX_FIELD CString m_strQualifierParam; CString m_strOwnerParam; CString m_strNameParam; CString m_strTypeParam;// Implementation protected: virtual CString GetDefaultConnect(); // default connection string virtual CString GetDefaultSQL(); // default SQL for Recordset virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support };IMPLEMENT_DYNAMIC(CTables, CRecordset)CTables::CTables(CDatabase* pDatabase) : CRecordset(pDatabase) { //{{AFX_FIELD_INIT(CTables) m_strQualifier = ""; m_strOwner = ""; m_strName = ""; m_strType = ""; m_strRes = ""; m_nFields = 5; //}}AFX_FIELD_INIT m_strQualifierParam = ""; m_strOwnerParam = ""; m_strNameParam = ""; m_strTypeParam = ""; }BOOL CTables::Open(UINT nOpenType, LPCSTR lpszSQL, DWORD dwOptions) { ASSERT(lpszSQL == NULL); RETCODE nRetCode; // Cache state info and allocate hstmt SetState(nOpenType,NULL,noDirtyFieldCheck); if (!AllocHstmt()) return FALSE; TRY { OnSetOptions(m_hstmt); AllocStatusArrays(); // call the ODBC catalog function with data member params AFX_SQL_ASYNC(this, (::SQLTables)(m_hstmt, (m_strQualifierParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strQualifierParam), SQL_NTS, (m_strOwnerParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strOwnerParam), SQL_NTS, (m_strNameParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strNameParam), SQL_NTS, (m_strTypeParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strTypeParam), SQL_NTS)); if (!Check(nRetCode)) ThrowDBException(nRetCode, m_hstmt); // Allocate memory and cache info AllocAndCacheFieldInfo(); AllocRowset(); // Fetch the first row of data MoveNext(); // If EOF, result set is empty, set BOF as well m_bBOF = m_bEOF; } CATCH_ALL(e) { Close(); THROW_LAST(); } END_CATCH_ALL return TRUE; }CString CTables::GetDefaultConnect() { return "ODBC;"; }CString CTables::GetDefaultSQL() { // should SQLTables directly, so GetSQL should never be called ASSERT(FALSE); return "!"; }void CTables::DoFieldExchange(CFieldExchange* pFX) { //{{AFX_FIELD_MAP(CTables) pFX->SetFieldType(CFieldExchange::outputColumn); RFX_Text(pFX, "table_qualifier", m_strQualifier); RFX_Text(pFX, "table_owner", m_strOwner); RFX_Text(pFX, "table_name", m_strName); RFX_Text(pFX, "table_type", m_strType); RFX_Text(pFX, "res", m_strRes); //}}AFX_FIELD_MAP }// 使用 CTables tables(m_pDatabase); // db passed via constructor tables.Open(); // closed on table's // destruction while (!tables.IsEOF()) { TRACE("table name = %s\n", tables.m_strName);
// reject queries and system tables if (_stricmp(tables.m_strType, "TABLE") == 0) { pLB->AddString(tables.m_strName); } tables.MoveNext(); }
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.xls;Extended Properties=""Excel 8.0"";"
Set cat=CreateObject("ADOX.Catalog")
cat.ActiveConnection = cn
for each tbn in cat.Tables
if right(tbn.name,1)="$" or (left(tbn.name,1)="'" and right(tbn.name,1)="'") then
msgbox tbn.name
end if
next
set cat=nothing
{
DECLARE_DYNAMIC(CTables)public:
CTables(CDatabase* pDatabase = NULL);
BOOL Open(UINT nOpenType = forwardOnly, LPCSTR lpszSQL = NULL,
DWORD dwOptions = readOnly);// Field/Param Data
//{{AFX_FIELD(CTables, CRecordset)
CString m_strQualifier;
CString m_strOwner;
CString m_strName;
CString m_strType;
CString m_strRes;
//}}AFX_FIELD CString m_strQualifierParam;
CString m_strOwnerParam;
CString m_strNameParam;
CString m_strTypeParam;// Implementation
protected:
virtual CString GetDefaultConnect(); // default connection string
virtual CString GetDefaultSQL(); // default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support
};IMPLEMENT_DYNAMIC(CTables, CRecordset)CTables::CTables(CDatabase* pDatabase)
: CRecordset(pDatabase)
{
//{{AFX_FIELD_INIT(CTables)
m_strQualifier = "";
m_strOwner = "";
m_strName = "";
m_strType = "";
m_strRes = "";
m_nFields = 5;
//}}AFX_FIELD_INIT
m_strQualifierParam = "";
m_strOwnerParam = "";
m_strNameParam = "";
m_strTypeParam = "";
}BOOL CTables::Open(UINT nOpenType, LPCSTR lpszSQL,
DWORD dwOptions)
{
ASSERT(lpszSQL == NULL); RETCODE nRetCode; // Cache state info and allocate hstmt
SetState(nOpenType,NULL,noDirtyFieldCheck);
if (!AllocHstmt())
return FALSE; TRY
{
OnSetOptions(m_hstmt);
AllocStatusArrays(); // call the ODBC catalog function with data member params
AFX_SQL_ASYNC(this, (::SQLTables)(m_hstmt,
(m_strQualifierParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strQualifierParam), SQL_NTS,
(m_strOwnerParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strOwnerParam), SQL_NTS,
(m_strNameParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strNameParam), SQL_NTS,
(m_strTypeParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strTypeParam), SQL_NTS));
if (!Check(nRetCode))
ThrowDBException(nRetCode, m_hstmt); // Allocate memory and cache info
AllocAndCacheFieldInfo();
AllocRowset(); // Fetch the first row of data
MoveNext(); // If EOF, result set is empty, set BOF as well
m_bBOF = m_bEOF;
} CATCH_ALL(e)
{
Close();
THROW_LAST();
}
END_CATCH_ALL return TRUE;
}CString CTables::GetDefaultConnect()
{
return "ODBC;";
}CString CTables::GetDefaultSQL()
{
// should SQLTables directly, so GetSQL should never be called
ASSERT(FALSE);
return "!";
}void CTables::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CTables)
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX, "table_qualifier", m_strQualifier);
RFX_Text(pFX, "table_owner", m_strOwner);
RFX_Text(pFX, "table_name", m_strName);
RFX_Text(pFX, "table_type", m_strType);
RFX_Text(pFX, "res", m_strRes);
//}}AFX_FIELD_MAP
}// 使用
CTables tables(m_pDatabase); // db passed via constructor
tables.Open(); // closed on table's
// destruction
while (!tables.IsEOF()) {
TRACE("table name = %s\n", tables.m_strName);
// reject queries and system tables
if (_stricmp(tables.m_strType, "TABLE") == 0) {
pLB->AddString(tables.m_strName);
}
tables.MoveNext();
}