Solved

How to justify excel cells?

Posted on 2004-04-26
8
2,163 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now