Need Help writinig to a OLE inserted Excel Spreadsheet

[jazlady]

I inserted an pre-formated spreadsheet into my VB 6 form, but I want to be able to write data to the spreadsheet for display and saving. How can I write data to my OLE inserted spreadsheet

I tried ActiveWorkbook.Sheets.Cell(A2).Value but that doesn't work. Can someone help me.

how do I call the sheet that I want to write to
Sheets1.sheets.cell(a2)?

I am not sure if I need to call Excel objects in to play. Need help.

[Siddharth Rout]

'Cells(2,1) represents A2 i.e Cells(Row,Column)
Workbooks(1).Worksheets(1).Cells(2,1).Value

See the Exmaple below... In this example, the Object property returns an object reference to the Worksheet object that represents the embedded Microsoft Excel worksheet. Once you have a reference to a Worksheet object, you can set and retrieve its properties and apply its methods through Automation.

If you've embedded or linked a document created in a component that also supports Automation, such as Microsoft Excel, you can work with objects associated with that document much as you would if you were performing Automation. You can use the Object property to set and retrieve the properties of an object and apply its methods. The Object property returns an object reference to an Automation object that is related to the linked or embedded document.

To illustrate this concept, create a Microsoft Excel spreadsheet with some data in the first worksheet and embed the .xls document in an unbound object frame named OLEUnbound0 on a form. Create a button named EditSpreadsheet on the form, set its OnClick event property to [Event Procedure], and enter the following code into the form module. When the user clicks on the EditSpreadsheet button, the Microsoft Excel worksheet is activated and the data in cells in the first row is formatted in bold.

Private Sub EditSpreadsheet_Click()
   Dim ctl As Control, wks As Excel.Worksheet
   Dim intI As Integer
   
   ' Return reference to control containing OLE object.
   Set ctl = Me!OLEUnbound0
   ' Activate OLE object.
   ctl.Action = acOLEActivate
   ' Return reference to embedded Worksheet object.
   Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
   ' Initialize integer variable to 1.
   intI = 1
   ' Check whether there is data in cells in first row.
   Do Until IsEmpty(wks.Cells(1, intI).Value)
      ' Set font to bold.
      wks.Cells(1, intI).Font.Bold = True
      ' Increment integer variable.
      intI = intI + 1
   Loop
End Sub

[jazlady]

I tried to use the code above. I have a pre-created spreadsheet that contains only a column header in A1 and nothing else. When I try to insert data in A2 - K5 nothing is displayed in the Form embedded spreadsheet, but after I stop the run and I double click on spreadsheet an go in edit mode - i see the data that I wrote but it doesn't show on the embed spreadsheet when I am in run mode. Can you help me. All i see is blank rows after the column header. I did a test and tried to update to column header row and it worked but no other data will be displayed on the form embed spreadsheet at runtime. Can you help me with this? Also Set ctl = Me!OLEUnbound0 doesn't work I had to use ctrl = Me.OLE1 in VB6. Please help me to insert data on the spreadsheet inside the form. I tried to insert rows but it keeps giving me an error saying that I can't insert a new row with blank rows showing. I don't know how to get rid of the blank rows so that my data will appear inside the form spreadsheet at runtime.

Dim ctl As Control, wks As Excel.Worksheet
   Dim intI As Integer
   
   ' Return reference to control containing OLE object.
   Set ctl = Me.OLE1
   ' Activate OLE object.
   ctl.Action = acOLEActivate
   ' Return reference to embedded Worksheet object.
   Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
   ' Initialize integer variable to 1.
   intI = 2
   ' Check whether there is data in cells in first row.
   'Do Until intI <= 9 'IsEmpty(wks.Cells(1, intI).Value)
      ' Set font to bold.
      'wks.Cells(1, intI).Font.Bold = True
      'wks.Cells(1, intI).Font.Color = vbBlack
      wks.Cells(2, 1).Value = 234
      wks.Cells(3, 3).Value = "Somewhere"
      wks.Cells(4, 5).Value = "Nowhere"
      'wks.Range("A2:K2").Font.Color = vbRed
      ' Increment integer variable.
      intI = intI + 1
   'Loop
End Sub