Excel spreadsheets

  • Thread starter Thread starter Jon Cosby
  • Start date Start date
J

Jon Cosby

How do you append an Excel spreadsheet? This code

string qry;
string mdbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
"F:\\Databases\\bellpeek.mdb;";
OleDbConnection mdbConn = new OleDbConnection(mdbConnStr);

qry = "SELECT ProductName, OrderDate, Qty, OrderItems.Price " +
"INTO [Excel 8.0;Database=" + Application.StartupPath +
"\\book1.xls].[Orders] " + (sql code here);

OleDbCommand cmd = new OleDbCommand(qry, mdbConn);

mdbConn.Open();
cmd.ExecuteNonQuery();
mdbConn.Close();

only works if I create a new spreadsheet for each user selection. I want to
add the results to the original spreadsheet. Is there a way to do this?


Jon Cosby
 
¤ How do you append an Excel spreadsheet? This code
¤
¤ string qry;
¤ string mdbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
¤ "F:\\Databases\\bellpeek.mdb;";
¤ OleDbConnection mdbConn = new OleDbConnection(mdbConnStr);
¤
¤ qry = "SELECT ProductName, OrderDate, Qty, OrderItems.Price " +
¤ "INTO [Excel 8.0;Database=" + Application.StartupPath +
¤ "\\book1.xls].[Orders] " + (sql code here);
¤
¤ OleDbCommand cmd = new OleDbCommand(qry, mdbConn);
¤
¤ mdbConn.Open();
¤ cmd.ExecuteNonQuery();
¤ mdbConn.Close();
¤
¤ only works if I create a new spreadsheet for each user selection. I want to
¤ add the results to the original spreadsheet. Is there a way to do this?
¤

You would need to use an INSERT INTO statement:

INSERT INTO ... SELECT ... FROM ...


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul Clement" said:
¤ How do you append an Excel spreadsheet? This code
¤
¤ string qry;
¤ string mdbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+
¤ "F:\\Databases\\bellpeek.mdb;";
¤ OleDbConnection mdbConn = new OleDbConnection(mdbConnStr);
¤
¤ qry = "SELECT ProductName, OrderDate, Qty, OrderItems.Price " +
¤ "INTO [Excel 8.0;Database=" + Application.StartupPath +
¤ "\\book1.xls].[Orders] " + (sql code here);
¤
¤ OleDbCommand cmd = new OleDbCommand(qry, mdbConn);
¤
¤ mdbConn.Open();
¤ cmd.ExecuteNonQuery();
¤ mdbConn.Close();
¤
¤ only works if I create a new spreadsheet for each user selection. I want
to
¤ add the results to the original spreadsheet. Is there a way to do this?
¤

You would need to use an INSERT INTO statement:

INSERT INTO ... SELECT ... FROM ...


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...

I'm trying that, but for some reason it can't seem to read the tables. I get
a runtime error saying "The insert into statement contains the following
unknown field: (put field name here)". The connection is ok, it works for
SELECT INTO. Is there something wrong with this syntax:


qry = "INSERT INTO [Products$] IN '' [Excel 8.0;Database=" +
Application.StartupPath + "\\book1.xls] SELECT * FROM Products";


Jon
 
Hi Jon,

You can append an Excel Spreadsheet by using the INSERT INTO syntax. So if
you use:

INSERT INTO [Products$] (ProductName, OrderDate, Qty, Price) VALUES
(‘myproduct’, ‘08/08/1963’, 1, 5.00)

Or

INSERT INTO [Products$] SELECT ProductName, OrderDate, Qty, Price FROM
[Orders$]

It will append the Products sheet in your Excel document.

Keep in mind that there are some rules that you will have to follow for this
to be successful. Let’s take the example that I have provided above. First
for the INSERT INTO…SELECT statement to work you will need to have the same
column names in the Products$ sheet as the columns that you have in the
SELECT list from the Orders$ sheet. If you do a SELECT * FROM [Orders$] you
will have to make sure that all the columns from the [Orders$] sheet exist in
the [Products$] sheet. If you do not want to name the columns (or have a
range, etc…) you can set the parameter HDR=NO in the connection string (which
will result in Excel auto-naming all the columns starting with F1, F2 etc.)

I hope this helps.
 
¤ "Paul Clement" wrote:
¤ >
¤ > ¤ How do you append an Excel spreadsheet? This code
¤ > ¤
¤ > ¤ string qry;
¤ > ¤ string mdbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
¤ > +
¤ > ¤ "F:\\Databases\\bellpeek.mdb;";
¤ > ¤ OleDbConnection mdbConn = new OleDbConnection(mdbConnStr);
¤ > ¤
¤ > ¤ qry = "SELECT ProductName, OrderDate, Qty, OrderItems.Price " +
¤ > ¤ "INTO [Excel 8.0;Database=" + Application.StartupPath +
¤ > ¤ "\\book1.xls].[Orders] " + (sql code here);
¤ > ¤
¤ > ¤ OleDbCommand cmd = new OleDbCommand(qry, mdbConn);
¤ > ¤
¤ > ¤ mdbConn.Open();
¤ > ¤ cmd.ExecuteNonQuery();
¤ > ¤ mdbConn.Close();
¤ > ¤
¤ > ¤ only works if I create a new spreadsheet for each user selection. I want
¤ > to
¤ > ¤ add the results to the original spreadsheet. Is there a way to do this?
¤ > ¤
¤ >
¤ > You would need to use an INSERT INTO statement:
¤ >
¤ > INSERT INTO ... SELECT ... FROM ...
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤
¤ I'm trying that, but for some reason it can't seem to read the tables. I get
¤ a runtime error saying "The insert into statement contains the following
¤ unknown field: (put field name here)". The connection is ok, it works for
¤ SELECT INTO. Is there something wrong with this syntax:
¤
¤
¤ qry = "INSERT INTO [Products$] IN '' [Excel 8.0;Database=" +
¤ Application.StartupPath + "\\book1.xls] SELECT * FROM Products";
¤

It sounds like the Worksheet uses a different number of columns or different column names than the
Access database table. If this is the case, you will need to specify HDR=NO in the Excel connection
string and map the column names by specifying them in the INSERT statement.

Connection string to Excel:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;"""

Insert statement:

"INSERT INTO [Sheet8$] (F1, F2) SELECT F1, F2 FROM [MS Access;DATABASE=E:\My
Documents\db1.mdb].[tblAccess]"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top