Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell?
Centering data in an Excel cell? Centering data in an Excel cell?
Centering data in an Excel cell?
Go Back  Xtreme Visual Basic Talk > > > Centering data in an Excel cell?


Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2008, 03:44 PM
brainycat brainycat is offline
Newcomer
 
Join Date: Apr 2008
Posts: 10
Default Centering data in an Excel cell?


Greetings,
I am writing to an excel file from a vb.net program and am having a difficult time figuring out how to align the data in the center of a cell (or align left for that matter). Can anyone tell me how to do this for a column (cell, row, etc..)?
Thanks!
Reply With Quote
  #2  
Old 05-01-2008, 04:55 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi BC,

Welcome to the forum.

I usually try to figure this stuff out by using the VBA Macro recorder. In Excel 2003 and below you'll find it in Tools > Macro > Record New Macro... In Excel 2007 you'll have to display the Developer Tab, which you can do by choosing File (aka "Office Button") > Excel Options > Popular > 'Show Developer Tab in the Ribbon'.

Anyway, once you do that, you can record entering data and then changing the aligment, and the VBA recorder will make use of the HorizontalAlignment property to do this, setting it to such enum values as 'xlLeft', 'xlCenter' or the like. Then you'll need to clean up the code to convert it to VB.NET. The only real change is that VBA can use unqualified enum constants like 'xlLeft', while VB.NET would need a fully qualified 'Excel.Constants.xlLeft', unless you used an Imports statement.

The resultant VB.NET code should look something like this:
Code:
Dim rng As Excel.Range rng = xlApp.Range("A1") rng.Value = "Hello" rng.HorizontalAlignment = Excel.Constants.xlLeft
The constants you'll likely use are 'xlLeft', 'xlRight', 'xlCenter', 'xlCenterAcrossSelection', and 'xlGeneral'. The last one, 'xlGeneral', is the default: text is left-aligned, while numbers are right-aligned.

Here's an example of some Automation code that puts this all together:
Code:
Imports System.Runtime.InteropServices Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles Button1.Click Dim xlApp As New Excel.Application xlApp.Visible = True Dim wb As Excel.Workbook = xlApp.Workbooks.Add() Dim ws As Excel.Worksheet = CType(xlApp.Worksheets(1), Excel.Worksheet) Dim rng As Excel.Range rng = xlApp.Range("A1") rng.Value = "Hello" rng.HorizontalAlignment = Excel.Constants.xlLeft MessageBox.Show("Left Aligned") rng.HorizontalAlignment = Excel.Constants.xlRight MessageBox.Show("Right Aligned") rng.HorizontalAlignment = Excel.Constants.xlCenter MessageBox.Show("Center Aligned") rng.HorizontalAlignment = Excel.Constants.xlGeneral MessageBox.Show("General Alignment") MessageBox.Show("Ready to Quit?") GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() Marshal.FinalReleaseComObject(ws) wb.Close(False) Marshal.FinalReleaseComObject(wb) xlApp.Quit() Marshal.FinalReleaseComObject(xlApp) End Sub End Class

Hope this gets you going!
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 05-02-2008, 08:18 AM
brainycat brainycat is offline
Newcomer
 
Join Date: Apr 2008
Posts: 10
Default

Thanks Mike! Fantastic Tip about the macro recorder. Also, your tutorial on setting up the excel output was quite useful.
Reply With Quote
  #4  
Old 05-02-2008, 12:02 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

No problem BC...

I'm not sure which tutorial that you're talking about, but if you haven't read it yet, then you might want to take a look at the Automating Office Programs with VB.NET tutorial. (Was this the one?)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell? Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell? Centering data in an Excel cell?
Centering data in an Excel cell?
Centering data in an Excel cell?
 
Centering data in an Excel cell?
Centering data in an Excel cell?
 
-->