Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C++/CLIC#.NET
Hi mates,
 
I currently find a problem when I export values from my windows forms application to an excel-file. I've made an empty excel-file firstly and I want the excel to be able to get the values on my window. E.g. Cell[1,3] gets value of textBox1; Cell[2,4] gets value of the label1 etc.
 
It works well when the excel-file not to be opened during the "data writing"-process. But I noticed, when I open the excel-file and then control my windows forms, the values would not be writen in the excel-file. Instead of this they were writen in another excel-file(has the same name) under "My documents".
 
I thought that this happened because I used method Workbooks.Open(). I wondered if I should change the objects within the methods Workbooks.Open(), which in my prog all "Type::Missing" are. And I've tried a lot combinations. For example to set UpdateLinks to 3, set Editable as true and so on. But it didn't work still.
Refering msdn adress for Workbooks.Open():
http://msdn.microsoft.com/en-us/library/ms260757(v=office.11)?cs-save-lang=1&cs-lang=cpp#code-snippet-2[^]
 
Here is my code, it shows how I "write" values to an excel-file. I hope you'll see it and tell me how to solve my problem:
#define Excel Microsoft::Office::Interop::Excel
using namespace Excel;
.
.
.
private: System::Void button1_Click(System::Object^  sender, System::EventArgs^  e) {
Excel::Application^ xlApp;
Excel::Workbook^ wb;
Excel::Sheets^ sheets;
Excel::Worksheet^ ws;
Excel::Range^ range;
 
xlApp =gcnew Excel::ApplicationClass();
try
{
 wb = xlApp->Workbooks->Open("C:\\test.xlsx", Type::Missing, false,
Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing,
Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing,Type::Missing,Type::Missing);
 
 sheets = wb->Sheets;
 ws = (Excel::Worksheet^) sheets[1];
 range = (Excel::Range^)ws->Range["A1:Z100", Type::Missing];
 
 range->Cells[1,1] = "GiveToFirstCell";
 range->Cells[2,10] = "I'm in cell I2!";
 
 DateTime^ dt = System::DateTime::Now;
 String^ time = dt->ToString("HH:mm:ss");
 range->Cells[3,5]= time; // I used DateTime, because it's a chagngable value
                  
 
 wb->Save();
}
 
catch (Exception^ ex)
{
          MessageBox::Show("Error writing to excel:\r\n"+ex->Message, "Error",MessageBoxButtons::OK, MessageBoxIcon::Error);
}
 
finally
{
          System::Runtime::InteropServices::Marshal::FinalReleaseComObject(range);
          System::Runtime::InteropServices::Marshal::FinalReleaseComObject(ws);
          System::Runtime::InteropServices::Marshal::FinalReleaseComObject(sheets);
          wb->Close(true,false, Type::Missing);
          System::Runtime::InteropServices::Marshal::FinalReleaseComObject(wb);
          xlApp->Quit();
          System::Runtime::InteropServices::Marshal::FinalReleaseComObject(xlApp);
}
 
}
 
If my problem wasn't clear explained, or you have questions to my code. Just tell me, I'm glad to answer.
Thank you in advance!
 
I freshly tried, when you open a being opened excel-file again, the system will tell you that it is read-only, and when you edit sth. in this read-only one, the system requires to save the file under another folder.
And this is also just what my code does.
Altough I set the read-only as false(third place within Workbooks.Open()).
It has no effects. So do I need another way instead of "OPEN" the excel-file???
Posted 27-Nov-12 5:21am
Edited 28-Nov-12 20:53pm
v6

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The problem would appear to be in
wb->Close(true, false, Type::Missing);
Second parameter is supposed to be "Filename" not a boolean. Try the following instead ...
wb->Close(true, Type::Missing, Type::Missing);
You won't need the
wb->Save();
line in this instance - or keep the save and change the first parameter on the close to false.
 
I think you got the read-only error despite all the FinalReleaseComObject calls because there was still a reference to the original spreadsheet - the release should probably be in loop - see http://support.microsoft.com/kb/317109[^]
  Permalink  
Comments
christmars at 3-Dec-12 6:06am
   
Hi CHill60, sorry for my late reply. thank you for your correction! I wrote the Filename as "false" refered what I read by msdn example. You're right, when it's a name, it should be a Type::Missing, or a string of the filename. And obviously I used "save" and "save the chages", which can be seen as unnecessary. I did this just for safer. I have to say, you got my problem, but the article doesn't help to solve this. The solution should be like that, when the excel file is opened, the datas are also abled to be written on it. I've also coded in another version to check if the excel is being opened more times(Read-Only), if so the user gets a warning. But that's not the best solutiion for an user who wants to get datas as well as see the excel sheet.
CHill60 at 3-Dec-12 6:15am
   
Ah, sounds like you want to be able to see the sheet while this is going on - you need to make it visible when it is opened in the code (the default is now for it to be not visible). So after the line xlApp =gcnew Excel::ApplicationClass(); try putting xlApp::Visible = true;

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 690
1 Dnyaneshwar@Pune 540
2 ravikhoda 325
3 OriginalGriff 279
4 Manas Bhardwaj 230
0 Sergey Alexandrovich Kryukov 10,974
1 OriginalGriff 6,777
2 Peter Leow 4,785
3 Abhinav S 3,813
4 Maciej Los 3,575


Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 30 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid