locked
Please help me, I really Need help RRS feed

  • Question

  • Dear all,

    I have tried to write the COM model in VC++. I could successfully to open a blank new excel, however, I couldn't open my old excel file or even could not save the new blank excel file. 

    The code is as following:

    #include <iostream>
    #include <cmath>
    #include <comutil.h>
    
    // Office 2007 Objects
    #import \
    	"C:\Program Files (x86)\Common Files\microsoft shared\OFFICE12\mso.dll" \
    	rename("DocumentProperties", "DocumentPropertiesXL") \
    	rename("RGB", "RBGXL") \
    
    using namespace Office;
    
    // VBA Objects
    #import \
    	"C:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA6\vbe6ext.olb" 
    
    using namespace VBIDE;
    
    // Excel Application Objects
    #import "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe" \
    	rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") \
    	rename("DocumentProperties", "DocumentPropertiesXL") \
    	rename("ReplaceText", "ReplaceTextXL") \
    	rename("CopyFile", "CopyFileXL") \
    	exclude("IFont", "IPicture") no_dual_interfaces
    
    using namespace std;
    
    int main()  
    {  
        Excel::_ApplicationPtr XL;
    
    	CoInitialize(NULL);
    
    	// Start the Excel Application
    	// XL.CreateInstance(L"Excel.Application");
    
          
        // Open the Excel Workbook, but don't make it visible  
        // pXL->Workbooks->Open( L"C:\\dump\\book.xls" );  
        XL->Visible = true;  
    	
        /*
    	// If a communicatino error is thrown, catch it and complain
    	catch(_com_error &error)
    	{
    		cout << "COM error" << endl;
    	}
    	*/
    	
        // Access Excel Worksheet and return pointer to Worksheet cells  
    	XL->Workbooks->Open("C:\Users\Wilson\Desktop\Book1.xls");
        Excel::_WorksheetPtr pWksheet = XL->ActiveSheet;  
        Excel:: RangePtr pRange = pWksheet->Cells;  
      
        // Read an Excel data cell. (Note Excel cells start from index = 1)  
        double value = pRange->Item[1][1];  
      
        // Write/modify Excel data cells + save. (reopen xls file to verify)  
        pRange->Item[1][1] = 5.4321;  
        pRange->Item[1][2] = 1.1211;  
      //  pWksheet->SaveAs("C:\a.xls");
          
        // Exit the Excel Com object  
        XL->Quit();   
    	
    	// system("PAUSE");
    
        return 0;
    }
    

    The purpose of doing this, is that I have designed a excel model with active x buttons already, which produce some table and curves. My main purpose is to write a windows form application to act as a switch to run the model and present the plot from the excel file directly to the windows form application.

    I know that this means I need to get access b/t excel and VC++, so is there other way that I could directly present the plot to c++ from my excel file?!

    Thank you all, (My programming skills is not that good)

    Best Wishes,

    Wilson 

    Wednesday, January 16, 2013 4:26 AM

Answers

  • Hi Wilson,

    Thank you for posting in the MSDN Forum.

    I'm not a C++ guy, hope I can express myself clear.

    I see that you have the requirement to save a workbook,

    even could not save the new blank excel file

    and have code as below

    pWksheet->SaveAs("C:\a.xls");

    This is not the right way to save a workbook. Worksheet does have a SaveAs method, but it is used to save the sheet as a single file. You can refer to this page for a clear view.

    To be accurate, an Excel file is actually a Workbook which contains at least one worksheet. In order to save a workbook with multiple worksheets, you'll need to call the Save method of Workbook.

    You can define a Workbook variable and assign it with the newly opened file with code below

    Excel::_WorkbookPtr pWkbook = XL->Workbooks->Open("C:\Users\Wilson\Desktop\Book1.xls");

    I'm sure whether the code above could work or not, since I've never used C++ and there could be some syntax error in my code. But the logic goes that way.

    Later you can call Save method of the Workbook, using pWKbook->Save(); I suppose.

    I've noticed that you have commented the line

    pXL->Workbooks->Open( L"C:\\dump\\book.xls" ); 

    which might be the code

    I couldn't open my old excel file

    I've no idea why you have pXL here, I can see nowhere you defined it. Perhaps the code would work if you replace pXL with XL or change the filename parameter to "C:\dump\book.xls" as the format you've used below.

    For more information about Excel Object Model, you can refer to the Excel developer's reference.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Quist Zhang Thursday, January 17, 2013 9:03 AM
    • Proposed as answer by Elegentin Xie Monday, January 21, 2013 4:09 AM
    • Marked as answer by Elegentin Xie Wednesday, January 23, 2013 10:08 AM
    Thursday, January 17, 2013 8:53 AM
  • >I have tried to write the COM model in VC++. I could successfully to open a blank new excel, however, I couldn't open my old excel file or even could not save the new blank excel file. 

    This does not even compile. I saw this "sample code" in several implementations (with WTL classes instead of MFC, for example). Where "Excel" namespace comes from nobody knows (one of the versions even mentions that here "compiler started making growling noises"). In other case (university's paper, by the way) it is noted that "The code in this section has been tested with Visual Studio.NET 2003 and 2005 and Excel 2003, but will not necessarily work with other versions of the software". But who needs it in this way in 2013?

    Small advice - in 2013, Excel (including other MS Office components) or SQL Server are better (much easier) accessed via C# or VB.NET or C++/CLI using .NET classes that handle and cover much of the complex intrinsic aspects from the programmer. If you still want to use native C++ then follow the MSDN samples with COM, how difficult they would not seem to be, like this one. Or use ADO which is Microsoft's own way to access databases including Excel, like here.


    Thursday, January 17, 2013 11:27 AM

All replies

  • Hi Wilson,

    Thank you for posting in the MSDN Forum.

    I'm not a C++ guy, hope I can express myself clear.

    I see that you have the requirement to save a workbook,

    even could not save the new blank excel file

    and have code as below

    pWksheet->SaveAs("C:\a.xls");

    This is not the right way to save a workbook. Worksheet does have a SaveAs method, but it is used to save the sheet as a single file. You can refer to this page for a clear view.

    To be accurate, an Excel file is actually a Workbook which contains at least one worksheet. In order to save a workbook with multiple worksheets, you'll need to call the Save method of Workbook.

    You can define a Workbook variable and assign it with the newly opened file with code below

    Excel::_WorkbookPtr pWkbook = XL->Workbooks->Open("C:\Users\Wilson\Desktop\Book1.xls");

    I'm sure whether the code above could work or not, since I've never used C++ and there could be some syntax error in my code. But the logic goes that way.

    Later you can call Save method of the Workbook, using pWKbook->Save(); I suppose.

    I've noticed that you have commented the line

    pXL->Workbooks->Open( L"C:\\dump\\book.xls" ); 

    which might be the code

    I couldn't open my old excel file

    I've no idea why you have pXL here, I can see nowhere you defined it. Perhaps the code would work if you replace pXL with XL or change the filename parameter to "C:\dump\book.xls" as the format you've used below.

    For more information about Excel Object Model, you can refer to the Excel developer's reference.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Quist Zhang Thursday, January 17, 2013 9:03 AM
    • Proposed as answer by Elegentin Xie Monday, January 21, 2013 4:09 AM
    • Marked as answer by Elegentin Xie Wednesday, January 23, 2013 10:08 AM
    Thursday, January 17, 2013 8:53 AM
  • >I have tried to write the COM model in VC++. I could successfully to open a blank new excel, however, I couldn't open my old excel file or even could not save the new blank excel file. 

    This does not even compile. I saw this "sample code" in several implementations (with WTL classes instead of MFC, for example). Where "Excel" namespace comes from nobody knows (one of the versions even mentions that here "compiler started making growling noises"). In other case (university's paper, by the way) it is noted that "The code in this section has been tested with Visual Studio.NET 2003 and 2005 and Excel 2003, but will not necessarily work with other versions of the software". But who needs it in this way in 2013?

    Small advice - in 2013, Excel (including other MS Office components) or SQL Server are better (much easier) accessed via C# or VB.NET or C++/CLI using .NET classes that handle and cover much of the complex intrinsic aspects from the programmer. If you still want to use native C++ then follow the MSDN samples with COM, how difficult they would not seem to be, like this one. Or use ADO which is Microsoft's own way to access databases including Excel, like here.


    Thursday, January 17, 2013 11:27 AM
  • Thank so much for your support. I am now using the VB to edit and to open the excel file. Therefore, I have solved the problem successfully. 

    Yet, I have 2 questions right now. Right now, cause the windows form application actually open 2 excel files, of which, one of them is the model of our company. I would like to output the curve from the file, that's all. Yet, cause even I compile the VB code, those clients will still need to have the model excel file to have the curve, I am thinking whether there is a way to make the file disappeared, or to make the excel file into mdb file or other stuffs, so that they cant access and see the original data and input of our model file. 

    Also, the second question is that, I don't know how could I find the path that my client will use as when they are using my program, because I need to input diagrams and stuffs. Is there a way to package my program, let say install-shield?! something like that to guarantee an exact path.

    Please see the following code:

    Imports Microsoft.Office.Interop
    
    Class Form1
    
        Dim xlapp As Excel.Application
        Dim xlwb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim xlcht As Excel.Chart
    
        Private Sub PictureBox2_Paint(sender As Object, e As PaintEventArgs) Handles PictureBox2.Paint
    
            PictureBox2.BackgroundImage = System.Drawing.Image.FromFile("C:\VB\Paint_TSI.PNG")
            PictureBox2.BackgroundImageLayout = ImageLayout.Stretch
            ' PictureBox2.SizeMode = PictureBoxSizeMode.StretchImage
            PictureBox2.Visible = True
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            ' Run the Open Dialog
            Dim strFileName As String
            Dim dirFileName As String
            Dim msgtext As String
            Dim row, columnwrite, columnFirerd, columnIARrd, columnt As Integer
            Dim total As Long
    
            Dim totaln As Long
            Dim totalne As Long
            Dim totalc As Long
            Dim totalw As Long
            Dim totale As Long
            Dim totals As Long
    
            openFD.Title = "Load Policy Data"
            openFD.FileName = "CTFM_Template"
            openFD.Filter = "2010 Excel Files(*.xlsm)|*.xlsm|2003 Excel Files(*.xls)|*.xls"
    
            Dim DidWork As Integer = openFD.ShowDialog
            If DidWork = DialogResult.Cancel Then
    
            Else
                strFileName = openFD.FileName
                '   MessageBox.Show(strFileName)
            End If
    
    
    
            ' To open the excel file directed
            ' To run all the range of Fire + IAR exposure
            If Dir("C:\VB\record.bz") = "" Then
                ' Creat Excel Object
                xlapp = CreateObject("Excel.Application")
                xlapp.Visible = True
                xlwb = xlapp.Workbooks.Open(strFileName)
                xlsheet = xlwb.Worksheets(1)
                xlsheet.Activate()
    
                row = 3
    
    
                Do While row <= 79
                    columnwrite = 11
                    columnFirerd = 3
                    columnIARrd = 7
                    Do While columnwrite <= 14
                        xlsheet.Cells(row, columnwrite) = xlsheet.Cells(row, columnFirerd).value + xlsheet.Cells(row, columnIARrd).value
                        columnFirerd = columnFirerd + 1
                        columnIARrd = columnIARrd + 1
                        columnwrite = columnwrite + 1
                    Loop
                    row = row + 1
                Loop
    
    
                ' To sum up the last row in the exposure sheet, which is to find the total
                row = 3
                columnt = 3
                total = 0
    
                Do While columnt <= 14
    
                    Do While row <= 79
                        total = total + xlsheet.Cells(row, columnt).value
                        row = row + 1
                    Loop
                    xlsheet.Cells(row, columnt) = total
                    columnt = columnt + 1
                    total = 0
                    row = 3
                Loop
    
    
                ' To copy the cells into the summary page and sum up by partition
                row = 3
                columnt = 3
                '' Residential
                xlsheet = xlwb.Worksheets(1)
                xlsheet.Activate()
                xlsheet.Range("K3:K80").Copy()
    
                xlsheet = xlwb.Worksheets(2)
                xlsheet.Activate()
                xlsheet.Range("D3:D80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
                '' Commercial
                xlsheet = xlwb.Worksheets(1)
                xlsheet.Activate()
                xlsheet.Range("L3:L80").Copy()
    
                xlsheet = xlwb.Worksheets(2)
                xlsheet.Activate()
                xlsheet.Range("E3:E80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
                '' Industrial
                xlsheet = xlwb.Worksheets(1)
                xlsheet.Activate()
                xlsheet.Range("M3:M80").Copy()
    
                xlsheet = xlwb.Worksheets(2)
                xlsheet.Activate()
                xlsheet.Range("F3:F80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
                '' Warehousing
                xlsheet = xlwb.Worksheets(1)
                xlsheet.Activate()
                xlsheet.Range("N3:N80").Copy()
    
                xlsheet = xlwb.Worksheets(2)
                xlsheet.Activate()
                xlsheet.Range("G3:G80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
                '' Do the subtotal column
                Do While row <= 80
                    xlsheet.Cells(row, columnt) = xlsheet.Cells(row, columnt + 1).value + xlsheet.Cells(row, columnt + 2).value + xlsheet.Cells(row, columnt + 3).value + xlsheet.Cells(row, columnt + 4).value
                    row = row + 1
                Loop
    
                ' Update the TSI by Partition
                '' North
                row = 10
                columnt = 3
                totalc = 0
                totale = 0
                totaln = 0
                totalne = 0
                totals = 0
                totalw = 0
    
                row = 3
                columnt = 3
                '' North
                Do While row <= 11
                    totaln = totaln + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
                '' North East
                Do While row <= 31
                    totalne = totalne + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
                '' Central
                Do While row <= 53
                    totalc = totalc + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
                '' West
                Do While row <= 58
                    totalw = totalw + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
                '' East
                Do While row <= 65
                    totale = totale + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
                '' South
                Do While row <= 79
                    totals = totals + xlsheet.Cells(row, columnt).value
                    row = row + 1
                Loop
    
                row = 3
                xlsheet.Cells(row, 10) = totaln
                xlsheet.Cells(row + 1, 10) = totalne
                xlsheet.Cells(row + 2, 10) = totalc
                xlsheet.Cells(row + 3, 10) = totalw
                xlsheet.Cells(row + 4, 10) = totale
                xlsheet.Cells(row + 5, 10) = totals
                xlsheet.Cells(row + 6, 10) = totaln + totalne + totalc + totalw + totale + totals
                Label2.Text = xlsheet.Cells(row + 6, 10).value
    
                ' To export the TSI chart and to present it into the software
                xlsheet = xlwb.Worksheets(2)
                xlsheet.Activate()
                xlsheet.ChartObjects("Chart 3").chart.Export(Filename:="C:\VB\Click_TSI.PNG", FilterName:="PNG")
    
    
    
    
    
                PictureBox2_Click(sender, e)
    
    
                'PictureBox2.Image = System.Drawing.Image.FromFile("C:\VB\Click_TSI.PNG")
                'PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                'PictureBox1.Visible = True
    
    
    
    
                xlwb.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoOpen)
                ' xlapp.Run("pp")
    
                '      xlcht = xlapp.ActiveChart
    
    
                xlapp.DisplayAlerts = False
                xlwb.Save()
                xlwb.Close()
                xlapp.DisplayAlerts = True
                xlapp.Quit()
                MsgBox("Policy Data Loaded", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
    
            Else
                msgtext = "Error: Excel File already haven been Opened" & vbNewLine & "Please Close the Excel File first"
                MsgBox(msgtext)
    
            End If
    
        End Sub
    
    
    
        Private Sub PictureBox1_Click(sender As Object, e As EventArgs)
    
            PictureBox1.Image = New System.Drawing.Bitmap("C:\VB\EP Curve.BMP")
            PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            PictureBox1.Visible = True
    
    
            'Dim t1, t2, t As Date
    
            't1 = Now
    
    
            'PictureBox1.Image = sht.activechart
    
        End Sub
    
        'Public Sub PictureBox1_Paint(sender As Object, e As PaintEventArgs) Handles PictureBox1.Paint
        '    Dim drawstring = "EP Curve"
        '    Dim drawfont As New Font("Calibri", 18, FontStyle.Bold) '定義字型
        '    Dim x = 150, y = 80 '定義字體左上角位置
        '    Dim mycolor1 As New SolidBrush(Color.FromArgb(25, 25, 25))    '定義字體顏色
        '    e.Graphics.DrawString(drawstring, drawfont, mycolor1, x, y)
        'End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            If Dir("C:\Temp\record.bz") <> "" Then
                xlapp.Quit()
                xlapp = Nothing
            End If
    
        End Sub
    
        
        Private Sub PictureBox1_Paint_1(sender As Object, e As PaintEventArgs) Handles PictureBox1.Paint
    
            PictureBox1.BackgroundImage = System.Drawing.Image.FromFile("C:\VB\Flood_Map.BMP")
            PictureBox1.BackgroundImageLayout = ImageLayout.Stretch
            ' PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            PictureBox1.Visible = True
    
        End Sub
    
        Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
            PictureBox2.Image = New Bitmap("C:\VB\Click_TSI.PNG")
            PictureBox2.BackgroundImageLayout = ImageLayout.Stretch
            PictureBox2.SizeMode = PictureBoxSizeMode.AutoSize
            PictureBox2.Visible = True
        End Sub
    
       
    End Class
    
    

    Thank you anyways for your kindly support. 

    Cheers,

    Wilson

    Monday, January 21, 2013 3:12 AM
  • Hi Wilson,

    Thank you for your last reply.

    For your 1st question,

    Right now, cause the windows form application actually open 2 excel files, of which, one of them is the model of our company. I would like to output the curve from the file, that's all. Yet, cause even I compile the VB code, those clients will still need to have the model excel file to have the curve, I am thinking whether there is a way to make the file disappeared, or to make the excel file into mdb file or other stuffs, so that they cant access and see the original data and input of our model file.

    Actually I'm not quite understand your request. Please re-edit your post to make your questions clear and post in: Excel for Developers

    For your 2nd question, 

    the second question is that, I don't know how could I find the path that my client will use as when they are using my program, because I need to input diagrams and stuffs. Is there a way to package my program, let say install-shield?! something like that to guarantee an exact path.

    As I see install-shield, do you want to make an installation package for your application? If so, you'll need to post your issue on a forum related to Deployment.

    Thank you for your understanding.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 21, 2013 4:07 AM
  • Thank you for your prompt reply. Please allow me to re-edit my post again. My first question is that in my program, I will have call 2 excel files. One of them will be the input and the output, while the other one is for the main processing, our company model. The purpose this time for me to programming is to release a program to our clients, so they can input and get the output directly. However, from what I ve been known, that I need to send out my 2nd modelling excel file to our clients as well, if they want to use the program. So therefore I am asking whether there is a way to hide the second excel file, like binding them with program, or hide the file, or further creating password to protect the file while keeping the access from the program I programmed.

    Thank you very much again.

    You really did me a big favor!

    Yours,

    Wilson

    Tuesday, January 22, 2013 1:08 AM
  • Hi,

    I have marked these replies as answers.
    If you disagree, please unmark them, and post your doubt.

    Regards,


    Elegentin Xie
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 23, 2013 10:08 AM