Solved

VB - Excel Row Height

Posted on 2002-06-19
8
1,237 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting warning: You are about to delete 1 row(s) 9 48
Access Object Property from VBA Module in Excel 2010 2 31
Added a column screws up code 5 57
Child Form in front 4 47
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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