-
April 30th, 2015, 08:27 AM
#1
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
-
April 30th, 2015, 08:27 AM
#2
Re: Program to modify an excel file
I am using Visual studio for this project, FYI.
-
April 30th, 2015, 09:17 AM
#3
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".
-
April 30th, 2015, 09:43 AM
#4
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.
-
April 30th, 2015, 10:16 AM
#5
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.
-
April 30th, 2015, 10:20 AM
#6
Re: Program to modify an excel file
Originally Posted by jaredmiller
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
-
April 30th, 2015, 11:15 AM
#7
Re: Program to modify an excel file
Originally Posted by VictorN
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?
-
April 30th, 2015, 11:27 AM
#8
Re: Program to modify an excel file
Originally Posted by jaredmiller
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
-
May 4th, 2015, 06:46 AM
#9
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.
-
May 4th, 2015, 06:53 AM
#10
Re: Program to modify an excel file
Originally Posted by OReubens
...
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
-
May 7th, 2015, 06:32 AM
#11
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
-
May 7th, 2015, 10:43 AM
#12
Re: Program to modify an excel file
Originally Posted by jaredmiller
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?
-
May 8th, 2015, 06:43 AM
#13
Re: Program to modify an excel file
Originally Posted by GCDEF
What language is that?
.Net I realized I posted in the wrong section but couldn't move it.
-
May 12th, 2015, 02:57 AM
#14
Re: Program to modify an excel file
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|