VB - Excel Row Height

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?
GeoffroAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bruintjeConnect With a Mentor Commented:
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
 
Elmo_Commented:
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
 
Elmo_Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GeoffroAuthor Commented:
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
 
Elmo_Commented:
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
 
Elmo_Commented:
Geoffro,

Any Update?

Cheers,

Ed.
0
 
Elmo_Commented:
Geoffro,

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

Cheers,

Ed.
0
 
bruintjeCommented:
:O))))))))))) read my profile i guess
0
All Courses

From novice to tech pro — start learning today.