Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Linked Excel worksheet

I have added a linked OLE object to a form and the source document is an Excel spreadsheet.  the spreadsheet is dynamic and will grow in length.  Can someone tell me how to have the rows set themselves automatically?  I need to set set a scrollable viewport for the data.  I tried adding a Vscrollbar to my form but all that does is scroll the entire OLE box.  How do I anchor the box and scroll the Data.
0
clarwc
Asked:
clarwc
  • 3
  • 2
1 Solution
 
AjoyCommented:
what you can do is  take the data fom the spreadsheet and display in dbcontrol grid which has the scroll bars.This should solve your problem.

0
 
clarwcAuthor Commented:
Ajoy,

Thanks, but I already have the Data in a FlexGrid but need to keep and Display it in a Spreadsheet as well.  How can I make the Vscroll bar scroll the data and not the entire OLE object.  T
0
 
chdyCommented:
Option Explicit
Dim iSw&

Dim Xobj As Object
Dim XobjApp As Object

Dim A1%, B1%, C1%, D1%, E1%, F1%, G1%, H1%

Dim aCount%: Dim bCount%
Dim cCount%: Dim dCount%
Dim eCount%: Dim fCount%
Dim gCount%: Dim hCount%

Private Sub Form_Load()

Set Xobj = GetObject("c:\my documents\Data1.xls")
Xobj.Activate

End Sub

Private Sub chkStart_Click(value As Integer)

aCount = 0: bCount = 0
cCount = 0: dCount = 0
eCount = 0: fCount = 0
gCount = 0: hCount = 0

Xobj.Parent.Windows(1).Visible = True
Set XobjApp = Xobj.Application
XobjApp.Visible = True

If value = False Then
   
 Do
  DoEvents
 
  If chkStart.value = True Then
     Exit Do
  End If
   
  iSw = Int((8 * Rnd) + 1)      
  Debug.Print iSw

  Select Case iSw
         
         Case 1
              aCount = aCount + 1
              Xobj.Range("A4").value = aCount
             
         Case 2
              bCount = bCount + 1
              Xobj.Range("B4").value = bCount
         Case 3
              cCount = cCount + 1
              Xobj.Range("C4").value = cCount
         Case 4
              dCount = dCount + 1
              Xobj.Range("D4").value = dCount
         Case 5
              eCount = eCount + 1
              Xobj.Range("E4").value = eCount
         Case 6
              fCount = fCount + 1
              Xobj.Range("F4").value = fCount
         Case 7
              gCount = gCount + 1
              Xobj.Range("G4").value = gCount
         Case 8
              hCount = hCount + 1
              Xobj.Range("H4").value = hCount
     
     
      End Select

  Else
     chkStart.value = True
       
  End If

End Sub

Private Sub cmdStart_Click()
 Dim value As Boolean
   value = False
   chkStart_Click (value)
End Sub

I want to be content you....
Good Luck..  ^.^
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
clarwcAuthor Commented:
i notice in the sample you provided a start_click event.  Should I add a "Start" command button to my form that has the OLE object  and relate the code you provided to the command button's click event?
0
 
clarwcAuthor Commented:
chdy,

I inserted your code into the form containing my OLE object.  When I click the start button I got a "Else without if" error.

I commented out the following:

 If chkStart.value = True Then
         Exit Do
     
      'End If and then got a "do without loop" error.  I can't figure out what the loop argument should be or where it should go.  Sorry to ask such basic questions but I am VERY new to this.
0
 
chdyCommented:
Sorry !  clarwc...
 Select case iSw
        Case 1
            .
            .
            .

        Case 8
                     hCount = hCount + 1
                     Xobj.Range("H4").value = hCount
             
             
         End Select

         Loop  'This is the problem part
                   ' Sorry...^.^
         Else
            chkStart.value = True
               
         End If

       End Sub

 I'm Sorry. This case is My mistake about  Loop sentence ....
God blessing you.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now