?
Solved

pass data from excel to vb

Posted on 2005-03-21
7
Medium Priority
?
405 Views
Last Modified: 2012-06-22
hi expert

i am facing some difficulty regarding passing the data from excel to vb automatically

i am running my vb exe and in one text box i need a value of some excel cell, now this cell value is not pri defined so
i am go to excel and copy the cell value of appropriat cell and pest in textbox. this task is more time consuming because
i need this lots time

so i need like this ... first i keep my cursore position on vb exe on one text box on which i want my value  now i go to excel and select some cell value
and from there i transfer directly that value to vb textbox without copy and pest, now the question is how can i do this ??
i am not want to work with  Excel -VBA programming because each time my excel sheet is not remain same it changes
i need that type of solution which is more customize mins i choose any excel workbook select some sheet select some cell value and transfer to vb exe

any one giveme the guideline how can i solve this problem
thanks



0
Comment
Question by:vsvb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 13596316
'set a reference to Microsot Excel Library (from the VB IDE menu click Project >references... and scroll down to check the box left to the excel library)

Option Explicit

Public Function GetFromExcel(ByVal fnWorkBook As String, fnSheet As String, fnCell As String) As String
Dim excelApp As Excel.Application, excelWB As Workbook, excelWS As Worksheet
Set excelApp = New Excel.Application
Set excelWB = excelApp.Workbooks.Open(fnWorkBook)

Set excelWS = excelWB.Worksheets(fnSheet)
GetFromExcel = excelWS.Range(fnCell).Value
   
excelWB.Close
excelApp.Quit
   
Set excelWS = Nothing
Set excelWB = Nothing
Set excelApp = Nothing
End Function

'usage:
Private Sub Command1_Click()
MsgBox GetFromExcel("c:\yourpath&file.xls", "yourworksheetname", "yourcell")
End Sub

S
0
 
LVL 3

Author Comment

by:vsvb
ID: 13599554
hi shauli thanks

here you explained the calling the excel form vb but i have idea about this so this example is not a answer that i expected
my issue is
1) I am in VB exe i put my cursor on one textbox ( as you know each active window remember cursor position even you open another window)
2) Now i am opening my excel sheet and select one cell
3) now this value of cell should be transfer directly to that my vb exe  textbox ( on that my cursor is currently positioned)  form excel

i had think about this may be this is possible if i creat one vb addin only for this purpose and add in excel menu
so when i select cell or perticuler sheet i click that menu and run that addin
now the addin first see the active cell value and then search openwindow based on my vbexe name and then
find active control on that window and pest the value.

this is just i am thinking but i not figure it out how?
in that there is serval issue
1) in addin first i have to get current cell value
2)transfer that value to my vbexe window at active control

but still i am waiting from some good advice


0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 13601206
>> now this cell value is not pri defined

Do you mean the content of the cell is not defined or the cell that you are using is not defined?  If the cell you are using is always the same (e.g. you always have to click on C14), I do not see why you cannot use Shauli's solution.  If the cell you are using is different (e.g. sometimes C14, sometimes A2, etc) you'll never be able to automate it.

Let's see if I have the model right:

        A process creates/modifies an Excel sheet (either human input or programmatical)
        You open the VB app
        VB app prompts for a value
        You open Excel sheet and copy the value you want
        You paste into VB app's prompt
        You continue the VB app execution

If I am correct about the model, you have to have something common to the cell you are clicking.  (e.g. it is the same cell every time, or it is the last cell in the third column, or it is the cell where the column is headed "Sales" and the row is headed with today's date, etc).  If you can say something that defines that cell, we can automate the process.

Additionally, how do you receive the prompt in the VB app (e.g. a text box on a form, or an InputBox statement, etc)?  If it is an InputBox, you could set the default value of the prompt.  If it is a text box, you could set its Text property on Form_Load.

Of course, if we know what data you need from where, we could automate the entire process, removing the user input stage altogether...

HTH

J.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 13601321
Also, just a thought, but there is a control that ships with Office.  

It's usually stored in %programfiles%\Common Files\Microsoft Shared\Web Components\xx\OWCxx.DLL where xx is your Office version (2000 = 9, XP = 10, 2003 = 11).  

The control is Microsoft Office Spreadsheet xx.0 and is part of the Office Web Components.  If you right-click on the VB toolbar and click Components, you should be able to select it.

This would put a spreadsheet object directly into your VB app, for direct selection and manipulation without needing to change windows...

J.
0
 
LVL 3

Author Comment

by:vsvb
ID: 13603167
hi again

this is right modal and i am follow same way but still there is some explanation about my query
my excel value is not predefined mins i select any cell value form the sheet accourding to my requirement
and that value i need to send directly to vb application

i give you one simple example
in vb we are using common dialog box for select file from harddrive.
now i my vb application there is one command button , one textbox and one commondialog conrol
on command button click event i call select file method of commondialog and when i select the file
any where form my hard drive and i return the file name in textbox on form.
now my issue of this problem is pretty much same i am in vb application click on the textbox so
i activate my excel sheet and select one of the cell accourding to my wish , now this cell contain some value
this value i want to return in text box directly through excel on single click ( same like file select operation in Commondialogbox)
i know for this solution i need to be some flexibal mins this prob solve by some another method but i am ready for that
but i not want to decide in which cell value you want form vb exe and click it and get this value in textbox ... this is very simple
just i use the excel 10.00 object library i do what ever i want  regarding fetching . but my point is i need to send data from excel to
vb appliaciton
i hope you getting me

0
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 1200 total points
ID: 13605382
Then you'll have to load the sheet in the Web Component control (Microsoft Office Spreadsheet xx.0) and handle a cell-click using it's events...

J.
0
 
LVL 3

Author Comment

by:vsvb
ID: 13611060
hi jimbobmcgee
thanks, using web componant i mostly solved this issue
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question