Solved

How to justify excel cells?

Posted on 2004-04-26
8
2,160 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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

14 Experts available now in Live!

Get 1:1 Help Now