Click here to Skip to main content
15,877,145 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
}


}
}
Posted
Updated 10-Jul-11 20:52pm
v3
Comments
Debojyoti Majumder 11-Jul-11 2:46am    
can you show us the GetExcelDriver() code...??
Sun-Mi Kang 11-Jul-11 2:53am    
OK I upload new post

You can verify your connection string from here[^] to make sure nothing is wrong with it.
 
Share this answer
 
Your code asked
CString sFile = "*.xlsx";    //is it right?
The answer is "no", you can't have it open "All XLSX Files". You have to come up with the file names one at a time.
 
Share this answer
 
Comments
Sampath579 2-Aug-22 0:45am    
Does you get the solution. I am also facing same issue.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900