How to justify excel cells?

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?


LVL 3
sasllcAsked:
Who is Participating?
 
unknown_routineConnect With a Mentor Commented:
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
 
jmwheelerConnect With a Mentor Commented:
.Range("A" & introw.ToString.Trim).HorizontalAlignment = xlHAlignLeft
.Range("B" & introw.ToString.Trim).HorizontalAlignment = xlHAlignRight
0
 
sasllcAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
unknown_routineCommented:
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
 
sasllcAuthor Commented:
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
 
unknown_routineCommented:
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
 
sasllcAuthor Commented:
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
 
unknown_routineCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.