How can connect to excel 2010 in ODBC ?

See more:

C++

MFC

Hi All!

I'm making Excel ODBC program using in List control.
When I run the program, I opened the excel file.
One message box showed to me.
It said that "failed to log in ODBC Excel Driver".
I think I make a mistake in file name (.xls), or (.xlsx).
I write down this form in my code. What;s wrong with my code?

Please, give any advices and tips to me.

<pre>

void CFormSecond::OnChange()
{
	CFormThird file;
	CString strSheet = DEFAULT_SHEET;
	CDatabase database;
	CString sSql;
	CString sItem1, sItem2;
	CString sDriver; 
	CString sDsn;
	CString sFile = "*.xlsx";	 //is it right?
	
	// Clear the contents of the listbox
	m_List.DeleteAllItems();
	
	sDriver = GetExcelDriver();
	if( sDriver.IsEmpty() )
	{
		// Blast! We didn큧 find that driver!
		AfxMessageBox("No Excel ODBC driver found");
		return;
	}
	
	sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);

	TRY
	{
		CRecordset recset( &database );

		sSql.Format(_T("SELECT * FROM [%s$A1:IV65535]"), strSheet);
		
		int nFieldCount = recset.GetODBCFieldCount();
		int nRowCount =0;
		int nIndex = 0;
		CDBVariant m_DBVariant;

		// Browse the result
		while( !recset.IsEOF() )
		{
			CString strFieldSum; 
			CString Add, Field,Value;
			
			recset.GetFieldValue((short)0, Add);
			recset.GetFieldValue((short)1, Field);
			recset.GetFieldValue((short)2, Value);

			
			if(Value.Right(2) == _T(".0"))

			Value = Value.Left(Value.GetLength() - 2);
			InsertRow(Add, Field,Value);
			recset.MoveNext();
		}

		// Close the database
		
							 
	}
	CATCH(CDBException, e)
	{
		database.Close();
		// A database exception occured. Pop out the details...
		AfxMessageBox("Database error: "+e->m_strError);
	}
	END_CATCH;

}

CString CFormSecond::GetExcelDriver()<br />
{<br />
	TCHAR szBuf[2048]={0,};<br />
	WORD cbBufMax = 2047;<br />
	WORD cbBufOut;<br />
	LPCTSTR pszBuf = szBuf;<br />
	CString strExcelDriver;<br />
<br />
		if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))<br />
		return _T("");<br />
<br />
		do<br />
	{<br />
		if( _tcsstr(pszBuf, _T("Excel") ) != 0 )<br />
		{<br />
			//찾음<br />
			strExcelDriver = CString( pszBuf );<br />
			break;<br />
		}<br />
		pszBuf = _tcschr( pszBuf, _T('\0') ) + 1;<br />
	}<br />
	while( pszBuf[1] != _T('\0') );<br />
<br />
	return strExcelDriver;<br />
}

This is my open and save code

void CFormThird::OnXlSave()
{
//BOOL bRead;

int mrowNum, mcolNum;

int rowNum = 1;
int colNum = 1;

char temp [10];


CXLEzAutomation XL(FALSE);

//m_SFileName ="MODBUS DATA FILE";


//칼럼 데이터

XL.SetCellValue(colNum++, 1, "Address");
XL.SetCellValue(colNum++, 1, "Field");
XL.SetCellValue(colNum++, 1, "Value");


rowNum= m_cList.GetItemCount();
// colNum = m_cList.GetItemCount();
colNum = 3;


// XL.SetCellValue(mcolNum+1,mrowNum+2,m_cList.GetItemText(mcolNum,mrowNum));
for (mrowNum=0; mrowNum<rowNum ; mrowNum++)
{
for(mcolNum=0; mcolNum<colNum; mcolNum++)
{
CString mText;
mText = m_cList.GetItemText(mrowNum,mcolNum);
XL.SetCellValue(mcolNum+1,mrowNum+2,mText);//m_cList.GetItemText(mcolNum,mrowNum));
}
}

char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";

//cel File(*.xls) |*.xls| (*.xlsx) |*.xlsx | 모든파일(*.*) |*.*| ";


CFileDialog DataWrite(FALSE, "Excel File(*.xlsx)", "*.xlsx", OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);
// 저장 창을 Modal()로 띄움


if(DataWrite.DoModal() == IDOK)
{
//XL.SaveFileAs(DataWrite.GetPathName());

m_SFileName = DataWrite.GetPathName();
//Use Excel SaveFile function to save worksheet
XL.SaveFileAs(m_SFileName);
XL.ReleaseExcel();

}

}


void CFormThird::OnXlOpen()
{



char chThisPath[256];

CString strThisPath ;


char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";


CFileDialog DataRead(TRUE, "Excel File(*.xlsx)",m_SFileName, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);

UpdateData(TRUE);



GetCurrentDirectory(256, chThisPath);
strThisPath.Format("%s\\%s.xlsx",chThisPath,m_SFileName);
GetModuleFileName( NULL, chThisPath, 256);

int nFileRead = DataRead.DoModal();




if(IDOK == nFileRead)
{

CXLEzAutomation XL;
CString sFile = DataRead.GetPathName();

CString address, field, value;


for(int i=1; ; i++)
{
address=XL.GetCellValue(1,i+1);
field=XL.GetCellValue(2,i+1);
value=XL.GetCellValue(3,i+1);

if ( address == "")
break;

m_cList.InsertItem(i-1,address);
m_cList.SetItemText(i-1,1,field);
m_cList.SetItemText(i-1,2,value);


}



if(!XL.OpenExcelFile(sFile))
{

// XL.SaveFileAs(strThisPath);
// m_cList.DeleteAllItems();
XL.ReleaseExcel();
return;
}


}
}