linking excel cell to vb6 form tetxbox

Hi
Is it possible to link an excel cell to a textbox on a vb6 form so that when I change the value in the excel cell it will aslo change in the textbox on the vb6 form. If so could someone please give me an example.

Many Thanks
Andy
samandrewAsked:
Who is Participating?
 
ahammarConnect With a Mentor Commented:
Ok....
1. add a module to your project and add the "Microsoft ActiveX Data object library 2.5" or above to your references (Menu item...Project >> references)

2. Put the code that is in the code snippet window in the module:

3. In your forms load event or any event you want (like behind your refresh button) put this:

Text1.Text = ReadCell("C:\Path\FileName.xls", "SheetName", "A1")

Change Text1 to the name of your text box
The first item in quotes is the path and filename to your file
The second item in quotes is the sheet name you want to read from
The third item in quotes is the cell you want to read

You can use that line of code anywhere in your project to read any file and cell you want by changing the 3 items in quotes to suit...


If you have any questions or problems, just ask...

:-)
Albert




Function ReadCell(FileName As String, SheetName As String, CellAddress As String) As String
'Must add Microsoft ActiveX Data object library 2.5 or above
  Dim cnt As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim stSQL As String
  Dim vaData As Variant
  Dim DataCount As Long
  Dim RowNum As Long
  Dim stCon As String
  
 
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
  & "Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=NO"";"
 
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
        
cnt.Open stCon
  
        stSQL = "SELECT * FROM [" & SheetName & "$" & CellAddress & ":" & CellAddress & "]"
        rst.Open stSQL, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
          
            vaData = rst.GetRows()
            ReadCell = vaData(0, 0)
                    
 
          rst.Close
    Set rst = Nothing
    cnt.Close
    Set cnt = Nothing
 
End Function

Open in new window

0
 
ahammarCommented:
You can't actually create a link to where it automatically changes, but you can have code in your vb6 form that will update the text box.  You just need to know at which point you want it to update.  Would you like it to update the text box when you open your form, when you start your app, or at regular intervals (intervals can be set anywhere from 1 second and above using a timer)?

:-)
Albert
0
 
samandrewAuthor Commented:
Hi Albert

I think I would like to update the textbox on loading the form could you please give me an example code.

Many Thanks
Andy
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ahammarCommented:
Sure...one more question...
There are 2 ways to do it...
Do you want it to read the Excel file as it was last saved, or in it's current state.
By current state, I mean if the Excel file is open and the cell has been changed but the file has not been saved yet...

:-)
Albert
0
 
samandrewAuthor Commented:
In its current state please, would it be also possible to place a cmd REFRESH button on my form then if I altered the value in the cell and I pressed the REFRESH button on my form the txtbox would update?

Many Thanks

Andy
0
 
ahammarCommented:
Ok...give me a few minutes and I will tailor something for that...

:-)
Albert
0
 
samandrewAuthor Commented:
Hi Albert

Many thanks for the code, one last question I have which hopefully you may be able to help me with is when I open Microsoft Office Excel 2007 i cannot find the controls box so I can add a command button to my spreadsheet would you happen to know how I can find these controls.

Many Thanks
Andy
0
 
ahammarCommented:
Ya...in the Developer tab...then the insert button...

Did you test and try the code...is everything ok with it?

:-)
Albert
0
 
samandrewAuthor Commented:
Hi Albert

Will take a little time to test so will get back to you thank you very much for your help

Andy
0
 
ahammarCommented:
Thank you for the points and the grade...I hope it worked out good for you!

:-)
Albert
0
 
aman729_88Commented:
sir i have implemented the readcell solution but my application hangs and if i click ok button it responds after the second or third click i want to ask the expert that how can we implement threading or application.doevents()
plz help
0
 
ahammarCommented:
Hi Andy

I need a little time to get back to you...I am really busy for a couple of days, but I will review this and get back to you as soon as I can...maybe tomorrow night if you don't figure it out before then..

:-)
Albert
0
 
ahammarCommented:
Ok...how have you implemented the readcell function...does it run when you open your app, and when you click the OK button?
Does it always hang, or just sometimes?

When you first open your App and run the readcell line of code, it will take a little bit longer then the rest of the times.  I cannot get my app to hang, but it does take a few seconds the first time when I first open it each time.

You might try adding a label to your form and make the caption say something like "Retrieving Data..." and set the visible property to False.
then just before your readcell line of code make it visible, then after the readcell line, hide it again...something like these 4 lines (of course replace the ReadCell line with the one that you need, and change the name of the label to match yours):


Label1.Visible = True
DoEvents
Text1.Text = ReadCell("C:\albert\findtest3.xls", "xxx", "C1")
Label1.Visible = False

Then the label will become visible at first, and stay visible until the data has been retrieved, then hide again

If you have your ReadCell line of code behind the OK button, and you can make it work by clicking the button more than once, then I don't think your app is hanging...it is just still retrieving the data.  You want to make sure you give it plenty of time to get the data before you click it again.  After the first time it retrieves the data, then the rest of the times will be faster...

If you don't get it to work, I'll try and see if I can help you out further...

:-)
Albert
0
 
aman729_88Commented:
i have set a UI timer for 500ms to readcell function.
30-40 seconds it works just fine and after that
an unhandled exception of type'system.StackoverflowException' occured in first.exe(my app anme)
can threads be implements to solve that...
exception is coming in the following line of code
line 21 in the program above.
 rst.Open stSQL, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
can u help it out plz
0
 
ahammarCommented:
Ok...with what you are doing with your timer, and the error you are getting indicate to me that the ReadCell function is starting again before it finishes.  That is ok for only so many times, but after so many times the  program will get too far behind, and a Stack Overflow error will happen.   I can't say for sure that is what's wrong, but it appears very much like it is.
You need to set your timer to a higher number...a half second like you  have it now is sometimes not long enough to be retrieving data from a file on your computer or especially a network.
I would experiment a little bit by turning your timer off, (make sure you have a button somewhere that you can click to run the ReadCell line of code)...open your app, click the button, and see how long it takes to get the data (You will have to have a message box pop up, or a blank text box that fills or something, so you will know when the data has been retrieved).  Do that a few times, then set the timer at the longest time that it took.

If you set your timer at a high enough time, the problem will probably go away.

I also added some error handlers to the same code above, but I would not try it until you get it working right without them, then use this new function, but that's up to you...

:-)
Albert

Function ReadCell(FileName As String, SheetName As String, CellAddress As String) As String
'Must add Microsoft ActiveX Data object library 2.5 or above
  Dim cnt As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim stSQL As String
  Dim vaData As Variant
  Dim DataCount As Long
  Dim RowNum As Long
  Dim stCon As String
  
On Error Goto ErrHandler
 
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
  & "Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=NO"";"
 
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
        
cnt.Open stCon
  
        stSQL = "SELECT * FROM [" & SheetName & "$" & CellAddress & ":" & CellAddress & "]"
        rst.Open stSQL, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
          
            vaData = rst.GetRows()
            ReadCell = vaData(0, 0)
                    
ErrHandler:
On Error Goto 0
On Error Resume Next
          rst.Close
    Set rst = Nothing
    cnt.Close
    Set cnt = Nothing
 
End Function

Open in new window

0
 
aman729_88Commented:
hi, thanks for your concern,
i can set the timer to more time but the problem with me is the data coming in the excel file is related to stocks and the rate streaming has to be fast.(as soon as it displayed the excel cell it has to be analysed by my software in vb.net.
what i am thinking is that there should be a thread which should call itself after the completions of itself
thread.isalive() function. and we clear the buffer by a method flush or asomething like that....
u will be genius to solve this for me...plz
0
 
aman729_88Commented:
i have researched a lot abt this and this is the case of memory leakage
the memory of excel and my vb.net application increases continuosly and then it hangs..
can we connect it some other way
by some oledb, odbc data adapters if yes? how
there has to be some solution  
plz refer to article 319998  of microsoft help and support..
i am in great confusion no one is able to help abt this.........
0
 
ahammarCommented:
Hi again,
Well, I am afraid if you can't set your timer at a slower rate (I don't know if that would have fixed it anyway for sure), then I don't know what else to do.  I am not an expert on data transfer of any kind, I just know how to read an Excel cell with VB, and this is how I do it if the Excel file is open.  I have it saved in a file that I just modify when I need to use it.  I don't know how it works, I just know it does.  It has always worked for me, but your circumstance is a little different, and your problem is beyond what I know how to fix.
I would post another question in the VB, and the VB.NET zone.  Tell them what you have got so far, what your problem is, and how to fix it, or a better way of doing it.  Refer them back to this question also by the web address if you want.  I'm sorry I don't have an answer, but it is just beyond what I know...:-(

You could look into instead of using a timer, set it up so the ReadCell line of code just runs in a loop...something like:

Do
     Text1.Text = ReadCell("C:\Path\FileName.xls", "SheetName", "A1")
Loop

Then once you start the sub, then it will never end, so it would never have to start again

You might even experiment with adding the Sleep API to put a short pause before it loops and starts over again...

This might work different than with using a timer....the loop may not start again until the function has completed, or if it is a memory leakage problem, then maybe doing it this way will not cause the same problem...I don't know...but it might be worth a shot.

You would probably have to add some code to have some way of stopping it if you had to.

That is all I know to do...which I can help you with that if you need me to, but it appears you probably know how to do that part, but if not, I can help you out...

or, which this is probably not an option, but maybe you could just use Excel to do the work that your VB application is doing now, then you could skip this step alltogether...Excel is powerful, and the VBA is good...although it's not as good as VB.net

Albert

0
All Courses

From novice to tech pro — start learning today.