OLE embed Excel-Sheet displayed cols and rows


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: OLE embed Excel-Sheet displayed cols and rows

  1. #1
    Daniel Teufert Guest

    OLE embed Excel-Sheet displayed cols and rows


    Hello!!

    I've got a problem using VB 6 and an embedded OLE Excel Worksheet:

    On a form in my app I created an OLE Control ("Create new --> Excel Worksheet").

    ------------------------------------------------------------------------
    Dim xlAppl As Excel.Application
    Dim xlWB As Object 'Excel.Workbook
    Dim xlWS As Object 'Excel.Worksheet

    OLE1.CreateEmbed vbNullString, "Excel.Sheet"
    Set xlWB = OLE1.object
    Set xlWS = xlWB.Sheets(1)
    ------------------------------------------------------------------------

    Now, the size of my OLE-Control is 9500x5900, but if I add data to the sheet,
    the follwing happens:

    Adding data by following code:

    ------------------------------------------------------------------------
    Private Sub GetMessages()

    Dim nRow As Long
    Dim strSqlStatement As String

    g_objError.Clear

    xlWS.UsedRange.Clear

    If Not (g_objIdcDBArchive Is Nothing) Then

    strSqlStatement = "Select * From tblIdcMessageText Where nLanguageId
    = " & CLng(cmbLanguages.ItemData(cmbLanguages.ListIndex)) & " AND nMsgGroupLink
    = " & CLng(cmbMessageGroups.ItemData(cmbMessageGroups.ListIndex)) & " AND
    nIsNewest = 1"
    Set oRS = g_objIdcDBArchive.Recordset(IDS_SQLPASSTHROUGH, -1, strSqlStatement)

    If Not g_objError.iserror Then
    If oRS.Rows > 0 Then
    If Not (oRS Is Nothing) Then
    For nRow = 0 To oRS.Rows - 1

    xlWS.Cells(nRow + 1, 1).Value = cmbLanguages.ItemData(cmbLanguages.ListIndex)
    xlWS.Cells(nRow + 1, 2).Value = oRS(nRow, 0)
    xlWS.Cells(nRow + 1, 3).Value = cmbMessageGroups.ItemData(cmbMessageGroups.ListIndex)
    xlWS.Cells(nRow + 1, 4).Value = oRS(nRow, 4)

    Next nRow
    End If
    End If
    End If
    End If

    Set oRS = Nothing

    End Sub
    ---------------------------------------------------------------------

    The data is filled in correctly, but only 7 columns and 10 rows are displayed.
    Ca. 3/4 of the OLE-Control-Size is left blank.


    Adding data by...

    --------------------------------------------------------------------------
    Private Sub btnImport_Click()

    Dim FileName As String

    cdImport.ShowOpen

    If cdImport.FileName <> "" Then
    FileName = cdImport.FileName
    frmLanguage.OLE1.CreateEmbed FileName
    End If

    End Sub
    --------------------------------------------------------------------------

    Now the Excel-Sheet automatically expands the number of rows until the OLE
    Control ist completely filled, but it cuts the columns after the last used.
    As the data maybe longer than the cell, I need to resize the third column
    to "best fit" (like if you doubleclick on the line between "C" and "D" at
    top of the Sheet).

    Can anyone of you "masters of the keyboard" tell me a solution for both problems?

    Thanks a lot in advance!!

    Daniel

  2. #2
    Join Date
    Oct 2008
    Posts
    9
    BUMP!!!!

    I have the same problem as this guy and have found nothing about how to fix it.

    Simply put i have made an embedded excel worksheet, but iam unaware of how to adjust the displayed screen size of the worksheet. Even if i resize the ole container only a certain number of rows and columns are shown on screen.

    If the user click on the excel worksheet (when the app is running) then it can be resized, but obviously i want the size to be set initially.

    I have very little knowledge of vb6 im afraid so im sure there must be some simple way to do this, but its a mystery to me.

    Please i would appreciate any help.

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I never used the OLE container control before. I always just opened up the spreadsheet itself.

    However, I played around with this, and in design and loaded a spreadsheet I have that is so wide it needs to be printed on legal size paper. I couldn't get it all to show even if the form is maximized, but I can scroll around the spreadhsheet. I'm guessing that might be the best you can do. How large is this?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Oct 2008
    Posts
    9
    Thanks for the reply,

    Well at the moment iam just playing around , but i will need to know this for an app that is needed.

    The help i was directed to when asking about how to open an embedded worksheet was

    http://support.microsoft.com/kb/242243#

    I have basicly just used this code to create a spreadsheet on the fly....this is code is great as it is pretty much exactly what i will need, however i will need to create a bigger spreadsheet than the one in the example. When i do add more rows and columns to the spreadsheet then what i find is that it simply cuts some of it off.....it is still there but just cannot be shown without the user resizing the window in runtime.

    Scroll bars would be ok (not ideal) but at the moment i dont know how to make it scrollable.

    If you follow the link and just copy, paste the code there u will have pretty much what i do and my problem should be more apparent. Im a .net man really so vb6 is a bit difficult to work with

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by lameness
    Im a .net man really so vb6 is a bit difficult to work with
    I have two years with .NET and 14 years with VB (3.0 through 6.0) and I found .NET difficult at first, but that was only because I wasn't used to it. I'm sure the same will happen with you in terms of VB6

    My real question is why in the world would you be doing this in VB6 if you are a .NET guy?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Oct 2008
    Posts
    9
    Ah its an dietion to an old vb6 program......we do ahave plans to upgrade it all to .net.....but the powers that be say that this isnt the right time


    do u understand what my problem is?

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I believe I understand the problem but I'm not being able to replicate it.

    As long as I have enough screen I can resize the form in design to accommodate the spreadsheet that I'm throwing at it.

    If you have a spreadsheet that you can't fit onto your screen, then you won't be able to in VB either.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Oct 2008
    Posts
    9
    Yeah thats almost my problem......but not quite. I have the form maximised as it will need to be for this app. It is not a case that the form isnt big enough, the worksheet simply shrinks smaller than the size of my ole container, and smaller than what is required in order to display the worksheet. The worksheet itself could quite easily fit within the ole container without scrolling if only the visible dipslay of it didnt seem to shrink.

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Put the spreadhsheet you are playing with in a zip file and attach it.

    I'll mess around with it and see what I can come up with.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Oct 2008
    Posts
    9
    Thanks for the time your taking to help me out

    The idea is that iam to create an addition to an existing app that will take the info extracted and generate a spreadsheet. The user might also want to edit the data....The spreadsheet will then be saved.

    Il post the code im using to make the spreadsheet with just some example info. Just try making a maximised form with an ole container (as big as you like) and a command button, then just copy paste code.

    As you can see the i have populated the spreadsheet with jan-dec but only the first 7 columns are being displayed. any ideas what is needed to display them all??

    Thanks for taking the time to look

    Code:
    Option Explicit
    
    Dim oBook As Object
    Dim oSheet As Object
    
    
    
    Private Sub Command1_Click()
          On Error GoTo Err_Handler
    
     ' Create a new Excel worksheet...
       OLE1.CreateEmbed vbNullString, "Excel.sheet"
    
       
     ' Now, pre-fill it with some data you
     ' can use. The OLE.Object property returns a
     ' workbook object, and you can use Sheets(1)
     ' to get the first sheet.
       Dim arrData(1 To 13, 1 To 13) As Variant
       Dim i As Long, j As Long
       
       Set oBook = OLE1.object
       Set oSheet = oBook.Sheets(1)
       
    
       
     ' It is much more efficient to use an array to
     ' pass data to Excel than to push data over
     ' cell-by-cell, so you can use an array.
     
     ' Add some column headers to the array...
       arrData(1, 2) = "January"
       arrData(1, 3) = "Febuary"
       arrData(1, 4) = "March"
       arrData(1, 5) = "April"
       arrData(1, 6) = "May"
       arrData(1, 7) = "June"
       arrData(1, 8) = "July"
       arrData(1, 9) = "August"
       arrData(1, 10) = "Spetember"
       arrData(1, 11) = "October"
       arrData(1, 12) = "November"
       arrData(1, 13) = "December"
       
     ' Add some row headers...
       arrData(2, 1) = "John"
       arrData(3, 1) = "Sally"
       arrData(4, 1) = "Charles"
       arrData(5, 1) = "Toni"
       
     ' Now add some data...
       For i = 2 To 13
          For j = 2 To 5
             arrData(i, j) = 350 + ((i + j) Mod 3)
          Next j
       Next i
       
     ' Assign the data to Excel...
       oSheet.Range("A3:M7").Value = arrData
       
       oSheet.Cells(1, 1).Value = "Test Data"
       oSheet.Range("B9:M9").FormulaR1C1 = "=SUM(R[-5]C:R[-2]C)"
       
     ' Do some auto formatting...
       oSheet.Range("A1:M9").Select
       oBook.Application.Selection.AutoFormat
       
       Command1.Enabled = False
      
       
       Exit Sub
       
    Err_Handler:
       MsgBox "An error occurred: " & Err.Description, vbCritical
    
       
    End Sub
    
    
    Private Sub Command2_Click()
       On Error GoTo Err_Handler
          
        ' Create an embedded object using the data
        ' stored in Test.xls.<?xm-insertion_mark_start author="v-thomr" time="20070327T040420-0600"?> If this code is run in Microsoft Office
        ' Excel 2007, <?xm-insertion_mark_end?><?xm-deletion_mark author="v-thomr" time="20070327T040345-0600" data=".."?><?xm-insertion_mark_start author="v-thomr" time="20070327T040422-0600"?>change the file name to Test.xlsx.<?xm-insertion_mark_end?>
       OLE1.CreateEmbed "C:\Test.xls"
       
       Command1.Enabled = False
       Command2.Enabled = False
       Command3.Enabled = True
       Exit Sub
       
    Err_Handler:
       MsgBox "The file 'C:\Test.xls' does not exist" & _
              " or cannot be opened.", vbCritical
    
    End Sub
    
    Private Sub Command3_Click()
     On Error Resume Next
       
     ' Delete the existing test file (if any)...
       Kill "C:\Test2.xls"
       
     ' Save the file as a native XLS file...
       oBook.SaveAs "C:\Test.xls"
       Set oBook = Nothing
       Set oSheet = Nothing
       
     ' Close the OLE object and remove it...
       OLE1.Close
       OLE1.Delete
       
       Command1.Enabled = True
       Command2.Enabled = True
       Command3.Enabled = False
    
    End Sub
    
    Private Sub Form_Load()
       Command1.Caption = "Create"
    
    End Sub
    Last edited by Hack; 10-22-2008 at 12:56 PM. Reason: Added Code Tags

  11. #11
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I edited your post and added code tags.

    The syntax the tags on this site is [code]your code goes here[/code] -
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  12. #12
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I'll play around with it a little bit.

    For one thing, I was not programmatically creating a sheet and adding it at run time. I dropped an OLE control on a form, and added a spreadsheet to it, and sized it out, all in design. That is what I thought you were doing.

    Now I'm understanding more of the issue that you are face.

    Before I start playing around with this I have a question and that is why use an OLE control? Would there be a problem in simply creating this spreadsheet out of the ether, populating it with whatever you need to populate it with, and simply open it up in using Excel itself?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  13. #13
    Join Date
    Oct 2008
    Posts
    9
    Thanks for taking the time to look. This is beginning to really annoy me.

    Let me know if you figure out how to get it working.

  14. #14
    Join Date
    Oct 2008
    Posts
    9
    Well i have found a workaround.....i have saved a template worksheet which i can then load up and add the info to. It works OK that way.

    BTW to anyone who is looking for info on this topic this is a great link

    http://www.ssuet.edu.pk/taimoor/book...-1/index13.htm


    Thanks again for your time Hack, and if u do figure out how to solve the problem properly then im sure im not the only one who would be interested to know what it is.

  15. #15
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by lameness
    Thanks again for your time Hack, and if u do figure out how to solve the problem properly then im sure im not the only one who would be interested to know what it is.
    So far, I've not had any more luck with this that you did, but I haven't given up totally yet.

    I'm going to keep playing with it for a while longer.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center