Solved

How to justify excel cells?

Posted on 2004-04-26
8
2,171 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

828 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