Display UsedRange of Excel spreadsheet VBScript
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Display UsedRange of Excel spreadsheet VBScript

  1. #1
    Join Date
    Jul 2008
    Location
    DC
    Posts
    37

    Display UsedRange of Excel spreadsheet VBScript

    How would I display the used range of an excel spreadsheet?
    I tried:
    Code:
    MsgBox objExcel.Worksheets(1).UsedRange
    This did not work, so how would I do something like this? Is there also some way to get the total number of used columns and total number of used rows? If you could tell me how to do this in Visual Basic Script that would be great. Visual Basic 6 or VB.Net code is ok. I'm assuming they are going to be similar?
    Last edited by dizou; August 22nd, 2008 at 01:35 PM.

  2. #2
    Join Date
    Aug 2005
    Location
    Imperial College London, England
    Posts
    490

    Re: Display UsedRange of Excel spreadsheet VBScript

    What do you mean by "display", and what do you mean by "did not work".
    As far as I can tell, the UsedRange property works perfectly - it represents the smallest possible rectangle which contains all the calls without null values.

    What exactly do you want to get?
    Help from me is always guaranteed!*
    VB.NET code is made up on the spot with VS2008 Professional with .NET 3.5. Everything else is just made up on the spot.
    Please Remember to rate posts, use code tags, send me money and all the other things listed in the "Before you post" posts.

    *Guarantee may not be honoured.

  3. #3
    Join Date
    Jul 2008
    Location
    DC
    Posts
    37

    Re: Display UsedRange of Excel spreadsheet VBScript

    Sorry, I meant how to show what the used range is. Like if there is data in the spreadsheet from A1 to C12, then
    Code:
    objExcel.Worksheets(1).UsedRange
    would be A1:C12. I would like to display this in a MsgBox. The code I had above didn't work because
    Code:
    objExcel.Worksheets(1).UsedRange
    isn't in a format that MsgBox can display. So how do I get that into a format I can display with a MsgBox. Also, how do I get the total number of rows and columns used in a spreadsheet?

  4. #4
    Join Date
    Aug 2005
    Location
    Imperial College London, England
    Posts
    490

    Re: Display UsedRange of Excel spreadsheet VBScript

    Code:
    Starting Column: UsedRange.Column
    Number of Columns: UsedRange.Columns.Count
    Starting Row: UsedRange.Row
    Number of Rows: UsedRange.Rows.Count
    
    Public Function ExcelColHead(ByVal ColNum As Integer) As String
      Dim T As Byte
      If ColNum > 26 Then
        T = (ColNum Mod 26)
        ColNum = (ColNum - T) / 26
        ExcelColHead = Chr(64 + ColNum) & Chr(64 + T)
      Else
        ExcelColHead = Chr(64 + ColNum)
      End If
    End Function
    Last edited by javajawa; August 23rd, 2008 at 05:43 AM.
    Help from me is always guaranteed!*
    VB.NET code is made up on the spot with VS2008 Professional with .NET 3.5. Everything else is just made up on the spot.
    Please Remember to rate posts, use code tags, send me money and all the other things listed in the "Before you post" posts.

    *Guarantee may not be honoured.

  5. #5
    Join Date
    Jul 2008
    Location
    DC
    Posts
    37

    Re: Display UsedRange of Excel spreadsheet VBScript

    That's great thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center