CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2015
    Posts
    12

    Program to modify an excel file

    Hello all,

    I am new to the forum and this is my first post, so please be kind. I am in the middle of writing a program that will ask a user to input a series of values, I then want to export those values into an excel file that has already been created. I am sure this is not too difficult of a task but I would appreciate any help! thanks so much

  2. #2
    Join Date
    Apr 2015
    Posts
    12

    Re: Program to modify an excel file

    I am using Visual studio for this project, FYI.

  3. #3
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,635

    Re: Program to modify an excel file

    Can you be more specific about what you mean by "export those values into an excel file that has already been created".

  4. #4
    Join Date
    Apr 2015
    Posts
    12

    Re: Program to modify an excel file

    I have a design table driving parameters in catia. For instance, I have a label on my excel sheet, lets say "Span" in A1 and the value "5" in A2. On my form I have a label that also says "Span" and a textbox next to it. When the user enter a number in that text box, I want to export that value into cell A2 of that particular excel sheet.

  5. #5
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,635

    Re: Program to modify an excel file

    It's been a long time since I did it, but you can use ODBC to update an Excel spreadsheet. If you're using MFC, look at the CRecordset class. If the spreadsheet is already open in Excel, you can look into automation to do it.

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Program to modify an excel file

    Quote Originally Posted by jaredmiller View Post
    I have a design table driving parameters in catia. For instance, I have a label on my excel sheet, lets say "Span" in A1 and the value "5" in A2. On my form I have a label that also says "Span" and a textbox next to it. When the user enter a number in that text box, I want to export that value into cell A2 of that particular excel sheet.
    In such a case you can consider an excel worksheet as a database table and just update/insert its fields.
    The connection string parameters for an excel worksheet you will find here.
    Victor Nijegorodov

  7. #7
    Join Date
    Apr 2015
    Posts
    12

    Re: Program to modify an excel file

    Quote Originally Posted by VictorN View Post
    In such a case you can consider an excel worksheet as a database table and just update/insert its fields.
    The connection string parameters for an excel worksheet you will find here.
    Could you elaborate further? I am not able to use any of the code on that page. Is there more that I need to add to it?

  8. #8
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Program to modify an excel file

    Quote Originally Posted by jaredmiller View Post
    Could you elaborate further? I am not able to use any of the code on that page. Is there more that I need to add to it?
    This page explains you how to create the connection strings for Excel to use with ODBC, ADO, ....
    It's up to you choose the means to access a database (in your case - the excel database): whether it would be ODBC with MFC CRecordset and CDatabase classes (as GCDEF already mentioned) or ADO or something else...
    Victor Nijegorodov

  9. #9
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    Re: Program to modify an excel file

    Alternatively, instead of "pushing" your data into an excel.
    you could also modify the excel and add macro's/script to "pull" the data you need into the excel sheet.

    If you are going for the push method.
    this requires excel is installed on the machine
    - you can either do that by using an ODBC approach, this approach will be limited in what you can do in the excel (typically you can only set values)
    - or you can acceess the Excel DOM via the Excel COM objects, the advantage to that is that (while a bit more involved ot get working), allows you to do anything you want, including setting formatting, colors, borders, fonts etc.

  10. #10
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Program to modify an excel file

    Quote Originally Posted by OReubens View Post
    ...
    If you are going for the push method.
    this requires excel is installed on the machine
    - you can either do that by using an ODBC approach, this approach will be limited in what you can do in the excel (typically you can only set values)
    - or you can acceess the Excel DOM via the Excel COM objects ...
    If using an ODBC approach the Excel installation id NOT needed. Only excel ODBC drivers must be installed.
    Victor Nijegorodov

  11. #11
    Join Date
    Apr 2015
    Posts
    12

    Re: Program to modify an excel file

    I was able to use this information to get my text boxes to the excel sheet, the problem is, it prints the text to the next row. How to I export the text to a specific cell or row so it replaces the current data instead of adding to it? I am assuming it has something to do with the .executenonquery()

    Code:
     Try
                With cm
                    .Connection = cn
                    .CommandText = "insert into [Sheet1$]values('" & TextBox1.Text & "','" & TextBox8.Text & "','" & TextBox4.Text & "','" & TextBox6.Text & "','" & TextBox11.Text & "','" & TextBox5.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox10.Text & "')"
                    .ExecuteNonQuery()
                End With

  12. #12
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,635

    Re: Program to modify an excel file

    Quote Originally Posted by jaredmiller View Post
    I was able to use this information to get my text boxes to the excel sheet, the problem is, it prints the text to the next row. How to I export the text to a specific cell or row so it replaces the current data instead of adding to it? I am assuming it has something to do with the .executenonquery()

    Code:
     Try
                With cm
                    .Connection = cn
                    .CommandText = "insert into [Sheet1$]values('" & TextBox1.Text & "','" & TextBox8.Text & "','" & TextBox4.Text & "','" & TextBox6.Text & "','" & TextBox11.Text & "','" & TextBox5.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox10.Text & "')"
                    .ExecuteNonQuery()
                End With
    What language is that?

  13. #13
    Join Date
    Apr 2015
    Posts
    12

    Re: Program to modify an excel file

    Quote Originally Posted by GCDEF View Post
    What language is that?
    .Net I realized I posted in the wrong section but couldn't move it.

  14. #14
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Program to modify an excel file

    [moved thread...]
    Victor Nijegorodov

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured