Solved

How to justify excel cells?

Posted on 2004-04-26
8
2,168 Views
Last Modified: 2010-04-12
I have a simple VB program that reads in a text file, maniputes the data fields, and writes out an excel spreadsheet.  Up until now, if I wanted to left-justify a cell value, I would precede it with a quote mark.  That seems to work fine, as excel apparently sees it as the old Lotus convention of quote for LJ.  But supposedly excel should also see a leading ^ to mean centering, and a " to mean right justify.  However, I can't get these two to work.

Is there an easy way to make it center or RJ, using a preceding character as described above?

If not, I need help on making justification work the 'right way'.  I have looked at some of the answers posted previously, but being new to vb, I just don't understand where and how I would insert the proper syntax.  Below I will show examples of some of the statements I am using, so maybe someone can tell me how to do this:

ExcelApp.Visible = True
ExcelApp.Workbooks.Add()
ExcelApp.Workbooks(1).Worksheets(1)
.Activate()

Private Sub WriteToSheet(By Val intRow As Integer, By Val strA as String, By val strB as String)

With ExcelApp.Workbooks(1).Worksheets(1)

.Range("A1").Value = "CITY"
.Range("B1").Value = "STATE"
ExcelApp.Workbooks(1).Worksheets(1).Name = "Locality"

.Range("A" & introw.ToString.Trim).value = strA
.Range("B" & introw.ToString.Trim).value = strB

End With

...and from there, I go on to define the values of strA and strB, and then I:

WriteToSheet(intRow, strA, strB)

---------------------

My question is this: exactly how and where would I define the excel cell justification characteristics when I write out values for strA and strB?  For example, how would I make strA LJ (left-justify), while strB is RJ?


0
Comment
Question by:sasllc
  • 4
  • 3
8 Comments
 
LVL 11

Assisted Solution

by:jmwheeler
jmwheeler earned 50 total points
ID: 10920789
.Range("A" & introw.ToString.Trim).HorizontalAlignment = xlHAlignLeft
.Range("B" & introw.ToString.Trim).HorizontalAlignment = xlHAlignRight
0
 
LVL 3

Author Comment

by:sasllc
ID: 10921839
No luck yet.  Questions:

Would the syntax you're showing go on it's own line?  Could I put the first line you wrote just below my existing line that says: .Range("A" & introw.ToString.Trim).value = strA   ?

That's what I tried, but I get an error at the end of the line; there is the squiggly line below the xlHAlignLeft word, and when I point to it, it tells me that "Name xlHAlignLeft is not declared".

Specifically, what I've got now is:

.Range("A" & introw.ToString.Trim).value = strA
.Range("A" & introw.ToString.Trim).HorizontalAlignment = xlHAlignRight

...and I'm getting the syntax error below the word 'xlHAlignRight'.  What am I missing?

(In case it matters, I'm actually working in vb.net)

0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10923377
I have not seen xlHAlignRight and don't know where it come from but,
use this which is correct:

.Range("A" & introw.ToString.Trim).HorizontalAlignment =  xlRight


xlCenter, xlLeft are 2 other parameters
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 3

Author Comment

by:sasllc
ID: 10923485
Believe it or not, even with this I'm still getting the "not declared" error.

Do I need to be "declaring" something somewhere?  If so, what and where and how?

Is this supposed to be two lines now, or is to be somehow combined into one?  The code shown below is what I am trying to use:

.Range("A" & introw.ToString.Trim).value = strA
.Range("A" & introw.ToString.Trim).HorizontalAlignment = xlRight

...but it underlines the 'xlRight' and says "Name xlRight is not declared".

Possibly you're assuming that I've 'done something' elsewhere in the program, like declaring something...but that's not a safe assumption, since I obviously don't know what I'm doing here!

Thanks for your continued help...
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10923653
I  completely beleive it, lol

and i think I know how to solve this problem.

Step 1: what kind of Excel object are are you referencing?


Excel object library 9?  or 10? write down the exact name of excel library you are using?


0
 
LVL 3

Author Comment

by:sasllc
ID: 10923756
I'm not sure...I guess it's 9 based on the info below.  The framework of this program was written for me by a vb programmer, so I really don't know what I'm looking for.  But if the info below does not answer the question, tell me where to look and I'll find it!

FWIW, I'm using Excel 2000.

Here are some lines from the top part of the program:
----------------------------------------------------------------------------------
Imports System.IO
Imports System.Text
Imports Excel            'must reference the COM Excel 9.0 Obj Lib

    'declare excel application
    Dim ExcelApp As New Excel.Application

    'counter to keep up with next row for 1st sheet
    Dim intRow As Integer = 2

    Public Function TranslateFile(ByVal strSourceFile As String) As Boolean
        Try
            'excel app visible
            ExcelApp.Visible = True

            'create a blank workbook
            ExcelApp.Workbooks.Add()

            With ExcelApp.Workbooks(1).Worksheets(1)
                'set the workseet active
                .Activate()
--------------------------------------------------------------------------------------

0
 
LVL 15

Accepted Solution

by:
unknown_routine earned 450 total points
ID: 10923899
Very good.

as I suspected Vb.net constants are different.

Now:

Set the values in your code before using them:

dim xlLeft ,xlCenter ,xlRight
xlLeft = -4131
xlCenter = -4108
xlRight = -4152




0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10923947
a neater way if to use Constant collection of excel directly:

so you can use this directly:


(.NEt only)
 Excel.Constants.xlCenter

Excel.Constants.xlLeftr
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question