Solved

VB - Excel Row Height

Posted on 2002-06-19
8
1,233 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…

708 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

18 Experts available now in Live!

Get 1:1 Help Now