Programming Office 2010 using Visual Studio (not VBA): Burn before reading

  • Article

See the previous blog: Office 2010: C#, Excel, Word, PowerPoint, and Outlook. Burn before reading

Back in the day (1972) when I was a bureaucrat in uniform (Air Force dude), we had this printer that would print real fast, but occasionally caught on fire.  So if you were the Airman who caught the worse job on the punch list, you got to change the ribbon, add ink and stand by with a fire extinguisher in case the fire started (which it did on a regular basis).  You were also guaranteed that no one was happy with anything, your uniform looked like an inkbomb had went off, but generally you could sit there doing your college homework, as the fire usually set off a sensor before you responded.  So not so bad.  Got through Economics on those watches.

Aren’t we lucky that using Skydrive makes Office collaboration so much simpler!

Just how hard is it to program an Excel application to connect to a database?  Not very, in fact generating the NorthWind Database is the most difficult thing.

So first of all, open Access and select the Northwind template, make sure you know what folder it is in.  This template adds some information that you can use for this project.  What does this have to do with Phone Apps?  Not much, but I needed to do this for someone in the company and thought that I would use in my blog.

Now open Visual Studio Pro or Visual Studio Ultimate and select the Office templates, then select Excel Templates 2010 image
Accept the default name image
Now add the data source image
Accept default image
Accept default image
Click New Connection… image
Browse to the Access database (in this example it is NorthWind)There is no password for the database unless you put one in.Which I hope you did and didn’t forget. imageimage
Click yes, read it first, but the correct answer is yes image
Accept the default image
We will use the Products table only, then click finish. image
Now the fun stuff, click on the Products and then on the LEFT side click on the triangle pointing directly to the RIGHT.Then select the Product Name and from the list select NamedRangeDrag ProductName (not NamedRange) over to Cell A2 imageimage
You will see the following.Now we will use the C# developers friend, the button!Add four buttons side by sideDouble click on Button 1 and the code behind shows up. image
You will be switched to the code behind for this spreadsheet when you double click and you can add the data management code, intellisense will help you out imageAdd code if you are doing exactly what I am doing, if you are using a different database then use the intellisense to help you out. 
         private void button1_Click(object sender, EventArgs e)         {             this.productsBindingSource.MoveFirst();                          }
For button2 (double click on button2)productsBindingSourcemay be different if are using a differently named table 
 private void button2_Click(object sender, EventArgs e)         {             this.productsBindingSource.MoveNext();         }
  
For button3 (double click on button3) 
         private void button3_Click(object sender, EventArgs e)         {             this.productsBindingSource.MovePrevious();         }
For button4 (double click on button4) 
         private void button4_Click(object sender, EventArgs e)         {             this.productsBindingSource.MoveLast();         }
Add these four lines to the Sheet1_Startup method 
 button1.Text = "First";             button2.Text = "Next";             button3.Text = "Back";             button4.Text = "Last";
The Sheet1_Startup method should look like the right. 
         private void Sheet1_Startup(object sender, System.EventArgs e)         {             button1.Text = "First";             button2.Text = "Next";             button3.Text = "Back";             button4.Text = "Last";             if (this.NeedsFill("northwind1DataSet"))             {                 this.productsTableAdapter.Fill(this.northwind1DataSet.Products);             }                     }

Run your worksheet by pressing F5 or the the green triangle and you should see, press the buttons, if you are on the first record then you won’t see a change when pressing First or Back, those will work when you are the second and subsequent records.  You could add code to check for those conditions and disable the buttons till the recordset is in the right position.

image

Hey now that you read this blog, check out my other blogs:

 

Take a look at my colleagues blogs!