Solved

VB - Excel Row Height

Posted on 2002-06-19
8
1,234 Views
Last Modified: 2008-03-04
We use a VB application which takes a Excel file which was created in Excel (used as a template) and inserts several rows of data into this Excel file.

The problem is on some PCs the row height of the rows inserted by the VB application is the default (12.75) while on others it is the maximum which Excel allows (409.5).

Any ideas on this one?
0
Comment
Question by:Geoffro
  • 5
  • 2
8 Comments
 
LVL 3

Expert Comment

by:Elmo_
ID: 7095146
Geoffro,

After you have inserted your Data into your excel spreadsheet. Use the AutoFit command. e.g.

Select the Rows you want - in this case rows 1 thru 20
And then use the Autofit Command.


    Rows("1:20").Select
    Selection.Rows.AutoFit

Hope this helps,

Cheers,

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7095183
Geoffro,

This should be a little better for you.  I am supplying code which will enable you to open the excel spreadsheet and set the rows to the correct height.  In this example the excel spreadsheet is made visible.

If you would like to test it, create a XLS file on your C Drive called c:\Book1.xls

Create new vb app and Add a command button to the form, insert this code:
--------------------------
Private Sub Command1_Click()
    Call Module1.GetExcel
End Sub
--------------------------

Now add a module and insert the following code:
-----------------------------------------------
'Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As Long _
) As Long

Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
    ByVal hWnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long _
) As Long

Sub GetExcel()
Dim MyXL As Object  ' Variable to hold reference to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean   ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next    ' Defer error trapping.
' Getobject function called without the first argument returns a reference to an instance of
'the application. If the application isn't running, an error occurs.
   
    Set MyXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear   ' Clear Err object in case error occurred.

    ' Check for MS Excel. If MS Excel is running, enter it into the Running Object table.
    DetectExcel

    'Set the object variable to reference the file you want to see.
    Set MyXL = GetObject("C:\Book1.XLS")

    ' Show Microsoft Excel through its Application property. Then show the actual window
    'containing the file using the Windows collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True
   
    ' Selects the first twenty Rows and then sets the Rowheight to  what is requireed in the Cell.
   MyXL.Application.Rows("1:20").Select
   MyXL.Application.Selection.Rows.AutoFit
   'Saves Doc
   MyXL.Application.Save

    ' If this copy of Microsoft Excel was not running when you started, close it using the
    'Application property's Quit method. Note that when you try to quit MS Excel, the title
    'bar blinks and a message is displayed asking if you want to save any loaded files.
    If ExcelWasNotRunning = True Then
        MyXL.Application.Quit
    End If

    Set MyXL = Nothing  ' Release reference to the application and spreadsheet.
End Sub

Sub DetectExcel()
   
    ' Procedure dectects a running Excel and registers it.
    Const WM_USER = 1024
    Dim hWnd As Long
   
    ' If Excel is running this API call returns its handle.
    hWnd = FindWindow("XLMAIN", 0)
    If hWnd = 0 Then    ' 0 means Excel not running.
        Exit Sub
    Else
   
    ' Excel is running so use the SendMessage API function to enter it in the Running Object Table.
        SendMessage hWnd, WM_USER + 18, 0, 0
    End If
End Sub


Hope this is more helpful.
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 7096267
Hi Geoffro,

in your code you insert a row you say

so it should be easy to select the inserted row called i in this case but i guess you're doing code that inserts after a number of rows or something so it should be easy to get the current rownumber or just use activecell instead

with Rows(i & ":" & i).Select
Selection.RowHeight = 13

or

Rows(ActiveCell.Row).Select
Selection.RowHeight = 13

HAGD:O)Bruintje
0
 

Author Comment

by:Geoffro
ID: 7097515
Thank you for your suggestions Elmo and Bruintje however do you know why this problem is happening in the first place, i.e. the row height is different on various PCs?
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 3

Expert Comment

by:Elmo_
ID: 7098004
Geoffro,

I could not exactly tell you why this is happening like this but I would suggest, that you try to replicate the error on your machine and step through the code in your visual basic app to see where and why.

There could be some extra spaces, returns or it could possibly be different font sizes.  There is no way to be sure until you go through your code.

Cheers,

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7113490
Geoffro,

Any Update?

Cheers,

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7171978
Geoffro,

Nearly a month now, Could you please close out the question?

Cheers,

Ed.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7173051
:O))))))))))) read my profile i guess
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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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

23 Experts available now in Live!

Get 1:1 Help Now