Re: Inserting data into a excel spread*** using Visual C++

"John Hutton" <huttonj@xxxxxxxx> wrote in message
news:G8adnSv2dtVS-0fenZ2dnUVZ_sKdnZ2d@xxxxxxxxxxx
> From within a Visual C++ app, using the following code snippet I am trying
> to write data into an Excel spread***. I get an error stating that I
> must use an "updateable query". I have searched everywhere to find a code
> sample for exporting to excel from Visual C but every Microsoft sample
> seems to use VB.
>
> Any assistance would be appreciated.
>
>
> _RecordsetPtr spRS;
> _ConnectionPtr spCON;
>
> CREATEiNSTANCE(spCON,Connection);
> spCON->ConnectionString = L"DRIVER={Microsoft Excel Driver (*.xls)};"
> L"DBQ=names.xls; DefaultDir=c:\\data;";
>
> spCON->Open( "", "", "", -1 );
> CREATEiNSTANCE(spRS,Recordset)
> spRS->PutRefActiveConnection( spCON );
> spRS->Open("INSERT INTO [Sheet1$] (firstname, lastname) values ('John',
> 'Dennis')", vtMissing,
> adOpenDynamic, adLockOptimistic, -1);
>
>
>

Here is a chunk of code from one of my demo projects, it should get you
pointed in the right direction...

ADODB::_ConnectionPtr pDBCon= NULL;
ADODB::_CommandPtr pCmd= NULL;
ADODB::_RecordsetPtr pRS= NULL;

CString strConn;
strConn.Format( _T("Provider='Microsoft.JET.OLEDB.4.0';Data
Source='%s';Extended Properties=\"Excel 8.0;HDR=YES;\""), strExportFile );
_bstr_t bstrConn( strConn );
HRESULT hr;
try
{
TESTHR( hr= pDBCon.CreateInstance( __uuidof( ADODB::Connection ) ) );
pDBCon->Open( bstrConn, "", "", NULL );
TESTHR( hr= pCmd.CreateInstance( __uuidof( ADODB::Command ) ) );
pCmd->ActiveConnection= pDBCon;
pCmd->CommandText=
_T("CREATE TABLE Customer_Listing (Customer_ID char(50), Customer_Name
char(50), Account_Nbr char(99))");
pCmd->Execute( NULL, NULL, ADODB::adCmdText );
TESTHR( hr= pRS.CreateInstance( __uuidof( ADODB::Recordset ) ) );
pRS->Open( _T("SELECT * FROM Customer_Listing WHERE Customer_ID = '0'"),
_variant_t( (IDispatch*)pDBCon ), ADODB::adOpenKeyset,
ADODB::adLockOptimistic, ADODB::adCmdText );
for( int nItem= 0; nItem < m_pListCtrl->GetItemCount(); nItem++ )
{
CString strID, strName, strAcctNbr;
strID= m_pListCtrl->GetItemText( nItem, 0 );
strName= m_pListCtrl->GetItemText( nItem, 1 );
strAcctNbr= m_pListCtrl->GetItemText( nItem, 2 );
pRS->AddNew();
pRS->Fields->GetItem( _T("Customer_ID") )->Value= _variant_t(
strID );
pRS->Fields->GetItem( _T("Customer_Name") )->Value= _variant_t(
strName );
pRS->Fields->GetItem( _T("Account_Nbr") )->Value= _variant_t(
strAcctNbr );
pRS->Update();
m_Progress.StepIt();
}
}
catch( _com_error& e )
{
// Notify the user of errors if any...
_bstr_t bstrSource( e.Source() );
_bstr_t bstrDescription( e.Description() );
CString strMsg;
strMsg.Format( _T("\nError\n\tSource : %s \n\tdescription : %s \n"),
(LPCSTR)bstrSource, (LPCSTR)bstrDescription );
AfxMessageBox( strMsg, MB_ICONSTOP );
}
catch( ... )
{
AfxMessageBox( _T("Error occured working with ADO/ADOX!"),
MB_ICONSTOP );
}

--
============
Frank Hickman
Microsoft MVP
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.


.

Loading