HOW TO LINK VALUES IN A FORM OF VISUAL BASIV 6 with an EXCEL TABLE

Dear Sir:
I create an application in Visual Basic 6 that in a form I get  12 rows with values corresponding to 6 columns ( col. I  - col. QS - col. QM - col. HM - col. HF - col. VF)
and I want to transfer them to an EXCEL2000 table with 12 rows and 6 columns.
Please, if possible, can you tell me what must I do?
Thanks in advance
Saturday, May 20 2006 11 55 hs. (GMT -3:00)
SYMOTOMAsked:
Who is Participating?
 
leonstrykerCommented:
I you feel that a question is answered, then select the post that reflects this and assign the grade based on the quality of the answer you received. Normally there is no issue, as long as the post selected really does deal with the answer to your question.

Leon
0
 
SYMOTOMAuthor Commented:
Dear Sir:
Please, if possible, send me again the existing answer to my email address, because I had troubles with the address in my Outlook Express program and I had not received any email from Experts-Exchange about "HOW TO LINK VALUES IN A FORM OF VISUAL BASIC 6 with an EXCEL TABLE
asked by SYMOTOM on 05/20/2006 07:56AM PDT "
Thanks and best regards
SYMOTOM
Wednesday, May 24 2006 19 08 hs. (GMT -3:00)
 
 
0
 
leonstrykerCommented:
So, you have a grid on your form with some data in it and you want to place this data on an Excel spread sheet?  Is that correct?

Leon
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
SYMOTOMAuthor Commented:
Yes Leon, that is correct, I want to place the data en an excel spread sheet that stays dinamically linked.
I am waitin your reply
Thank you
Moises
Friday,  May 26 2006 13 45 hs. (GMT -3:00)
0
 
leonstrykerCommented:
>dynamically linked

This is possible, but not very practical. It is much easier to copy the data based on user action or event.  The general idea here is to read the datafrom the grid into a two dimentional array which is then assigned to an Excel range.

Leon
0
 
SYMOTOMAuthor Commented:
I think is a good idea to read the datafrom the grid into a two dimentional array which is then assigned to an Excel range. But unfortunatly I do not how to do it.
Could it be possible, you tell me what must I learn to do it?
Moises
Friday, May 26 2006 14 00 hs. (GMT -3:00)
0
 
leonstrykerCommented:
Ok, what kind of grid are you using?
0
 
SYMOTOMAuthor Commented:
I use one form with lbl  and txt controls

0
 
leonstrykerCommented:
Is the data in labels and text boxes or is it in the grid?
0
 
SYMOTOMAuthor Commented:
one part of the data to transfer is in labels and other part is in text boxes
0
 
leonstrykerCommented:
In that case you will need to something like this:

Dim aArray(5, 11) As String

    aArray(0, 0) = lblFirstRow.Caption
    aArray(0, 1) = txtFirst1.Text
    aArray(0, 2) = txtSecond1.Text

    aArray(1, 0) = lblSecondRow.Caption
    aArray(1, 1) = txtFirst2.Text
    aArray(1, 2) = txtSecond2.Text

Once you have filled the array we can then go to the next step.

Leon
0
 
SYMOTOMAuthor Commented:
I have an Image .jpg of the form with the labels ans text boxes of my
application, but I do not know how to send you it. Using YouSendIt It is easy to
send you, but is necessary an address like yourname@server.com
unless Experts-Exchange have another way to do it.
0
 
leonstrykerCommented:
SYMOTOM,

I would be happy to help you with your project, but you need to start writting the code yourself.

Leon
0
 
SYMOTOMAuthor Commented:
I think, it must be only two arrays
one like this:
Dim aArray(?, ??) As String
    aArray(?, ??) = lblFirstRow.Caption
    aArray(0, 0) = lblFirst1.Caption
    aArray(0, 1) = lblSecond1.Caption
    aArray(0, 2) = lblThird1.Caption
    aArray(0, 3) = lblFourth1.Caption
    aArray(0, 4) = lblFith.Caption
    aArray(0, 5) = lblSixth1.Caption
       
    The above must be for the first row of six elements of labels. Please, if possible, correct it, because I think I must do similars for the other eleven rows.
0
 
SYMOTOMAuthor Commented:
I have no problem to write a code but it must declare  twelve arrays similar
to de above, and but the moment  I only need one to be correct, that is why I
ask you to give me the first one corrected
0
 
leonstrykerCommented:
>The above must be for the first row of six elements of labels. Please, if possible, correct it, because I think I must do similars for the other eleven rows.

No, you should only need one array.  In the array the first number will be the row and the second number will be the column. So the last element will be aArray(11, 5) assuming your data has 12 rows and 6 columns.

Leon
0
 
SYMOTOMAuthor Commented:
Leon, thank you very much for your assistance. I will thank you to tell me what must I do to contact you after some days about the present theme
0
 
leonstrykerCommented:
Sure, just post here and I will be notified.
0
 
SYMOTOMAuthor Commented:
Please, if possible, contact me with leonstryker
Thanks
Lunes Mayo 29 2006 20 02 hs. (GMT -3:00)
0
 
leonstrykerCommented:
Hi SYMOTOM,

What is the problem?  BTW, Please do not accept post which are not related to your question as answers.  I have un-accepted 'Sure, just post here and I will be notified.' again.

leonstryker
Page Editor, Visual Basic
0
 
SYMOTOMAuthor Commented:
Excuse me, but I do not understand the comment.
I asked to Leon what must I do to contact him after som days, and he answered me how to do it. I need to contact him about the same question "HOW TO LINK VALUES IN A FORM OF VISUAL BASIC 6 with an EXCEL TABLE" because I needed time to code de array he told me I must do.
Regards
Tuesday, May 30 2006 11 04 hs. (GMT -3:00)
0
 
leonstrykerCommented:
SYMOTOM, basically the only time you should accept a comment is when it answers your technical question. The comment you have been accepting in no way resolves the issue.  This rule is setup so that anyone else, with the same problem,  looking at the question later on may benefit from this.

Now what is your problem? How is the code going?

Leon
0
 
SYMOTOMAuthor Commented:
I created the ARRAY below and I do not which are the following steps for getting the objet of LINKING VALUES IN A FORM OF VISUAL BASIV 6 TO AN EXCEL WORKPAGE":
Can you help me?
Best regards
Moises
Tuesday, May 30 2006 12 04 hs. (GMT -3:00)
-------------------------- The Array of Labels to show of my application -------------
Private Sub cmdARRAY_Click()
Dim lbl_I0 As Variant
Dim lblQA_0 As Variant
Dim lblQM_0, lblHF_0, lblVF_0 As Variant
Dim lbl_I1, lblQA_1, lblQM_1, lblHF_1, lblVF_1 As Variant
Dim lbl_I2, lblQA_2, lblQM_2, lblHF_2, lblVF_2 As Variant
Dim lbl_I3, lblQA_3, lblQM_3, lblHF_3, lblVF_3 As Variant
Dim lbl_I4, lblQA_4, lblQM_4, lblHF_4, lblVF_4, lbl_I5, lblQA_5, lblQM_5, lblHF_5, lblVF_5 As Variant
Dim lbl_I6, lblQA_6, lblQM_6, lblHF_6, lblVF_6, lbl_I7, lblQA_7, lblQM_7, lblHF_7, lblVF_7 As Variant
Dim lbl_I8, lblQA_8, lblQM_8, lblHF_8, lblVF_8, lbl_I9, lblQA_9, lblQM_9, lblHF_9, lblVF_9 As Variant
Dim lbl_I10, lblQA_10, lblQM_10, lblHF_10, lblVF_10, lbl_I11, lblQA_11, lblQM_11, lblHF_11, lblVF_11 As Variant
Dim aArray(11, 4) As Variant

   
    aArray(0, 0) = lbl_I0.Caption
    aArray(0, 1) = lblQA_0.Caption
    aArray(0, 2) = lblQM_0.Caption
    aArray(0, 3) = lblHF_0.Caption
    aArray(0, 4) = lblVF_0.Caption
    aArray(1, 0) = lbl_I1.Caption
    aArray(1, 1) = lblQA_1.Caption
    aArray(1, 2) = lblQM_1.Caption
    aArray(1, 3) = lblHF_1.Caption
    aArray(1, 4) = lblVF_1.Caption
    aArray(2, 0) = lbl_I2.Caption
    aArray(2, 1) = lblQA_2.Caption
    aArray(2, 2) = lblQM_2.Caption
    aArray(2, 3) = lblHF_2.Caption
    aArray(2, 4) = lblVF_2.Caption
    aArray(3, 0) = lbl_I3.Caption
    aArray(3, 1) = lblQA_3.Caption
    aArray(3, 2) = lblQM_3.Caption
    aArray(3, 3) = lblHF_3.Caption
    aArray(3, 4) = lblVF_3.Caption
    aArray(4, 0) = lbl_I4.Caption
    aArray(4, 1) = lblQA_4.Caption
    aArray(4, 2) = lblQM_4.Caption
    aArray(4, 3) = lblHF_4.Caption
    aArray(4, 4) = lblVF_4.Caption
    aArray(5, 0) = lbl_I5.Caption
    aArray(5, 1) = lblQA_5.Caption
    aArray(5, 2) = lblQM_5.Caption
    aArray(5, 3) = lblHF_5.Caption
    aArray(5, 4) = lblVF_5.Caption
    aArray(6, 0) = lbl_I6.Caption
    aArray(6, 1) = lblQA_6.Caption
    aArray(6, 2) = lblQM_6.Caption
    aArray(6, 3) = lblHF_6.Caption
    aArray(6, 4) = lblVF_6.Caption
    aArray(7, 0) = lbl_I7.Caption
    aArray(7, 1) = lblQA_7.Caption
    aArray(7, 2) = lblQM_7.Caption
    aArray(7, 3) = lblHF_7.Caption
    aArray(7, 4) = lblVF_7.Caption
    aArray(8, 0) = lbl_I8.Caption
    aArray(8, 1) = lblQA_8.Caption
    aArray(8, 2) = lblQM_8.Caption
    aArray(8, 3) = lblHF_8.Caption
    aArray(8, 4) = lblVF_8.Caption
    aArray(9, 0) = lbl_I9.Caption
    aArray(9, 1) = lblQA_9.Caption
    aArray(9, 2) = lblQM_9.Caption
    aArray(9, 3) = lblHF_9.Caption
    aArray(9, 4) = lblVF_9.Caption
    aArray(10, 0) = lbl_I10.Caption
    aArray(10, 1) = lblQA_10.Caption
    aArray(10, 2) = lblQM_10.Caption
    aArray(10, 3) = lblHF_10.Caption
    aArray(10, 4) = lblVF_10.Caption
    aArray(11, 0) = lbl_I11.Caption
    aArray(11, 1) = lblQA_11.Caption
    aArray(11, 2) = lblQM_11.Caption
    aArray(11, 3) = lblHF_11.Caption
    aArray(11, 4) = lblVF_11.Caption
    'MsgBox aArray(0, 0)  
End Sub

----------------------------------------------------------------------------------------
0
 
leonstrykerCommented:
>>Dim lbl_I1, lblQA_1, lblQM_1, lblHF_1, lblVF_1 As Variant

Why are you declaring all of these? Your labels should already be on the form and should already have names associated with them. What are these names?

Leon
0
 
SYMOTOMAuthor Commented:
those are the names of the labels that give me an integer as data. and I like to transfer them to an excel worksheet. Or there are other way to do it without an array?
Moises
 
0
 
leonstrykerCommented:
There is a way to do it without an array as well, but the point I am trying to make is that you do not need to declare all of the labels.  Just use the names as they are on the form.
0
 
SYMOTOMAuthor Commented:
the names as they are on the form, by example,of someones, are:

    ·················
    lblVF_4.Caption
    lbl_I5.Caption
    lblQA_5.Caption
    lblQM_5.Caption
    lblHF_5.Caption
    lblVF_5.Caption
    lbl_I6.Caption
    lblQA_6.Caption
    ··················
but how I use the names as it are to get the transfer?
Moises

0
 
leonstrykerCommented:
The first part is to get the values assigned to a varriable, or and array of variables:

Private Sub cmdARRAY_Click()
Dim aArray(11, 4) As String

    aArray(0, 0) = lbl_I0.Caption
    aArray(0, 1) = lblQA_0.Caption
    aArray(0, 2) = lblQM_0.Caption
    aArray(0, 3) = lblHF_0.Caption
    aArray(0, 4) = lblVF_0.Caption
    aArray(1, 0) = lbl_I1.Caption
    aArray(1, 1) = lblQA_1.Caption
    aArray(1, 2) = lblQM_1.Caption
    aArray(1, 3) = lblHF_1.Caption
    aArray(1, 4) = lblVF_1.Caption
    aArray(2, 0) = lbl_I2.Caption
    aArray(2, 1) = lblQA_2.Caption
    aArray(2, 2) = lblQM_2.Caption
    aArray(2, 3) = lblHF_2.Caption
    aArray(2, 4) = lblVF_2.Caption
    aArray(3, 0) = lbl_I3.Caption
    aArray(3, 1) = lblQA_3.Caption
    aArray(3, 2) = lblQM_3.Caption
    aArray(3, 3) = lblHF_3.Caption
    aArray(3, 4) = lblVF_3.Caption
    aArray(4, 0) = lbl_I4.Caption
    aArray(4, 1) = lblQA_4.Caption
    aArray(4, 2) = lblQM_4.Caption
    aArray(4, 3) = lblHF_4.Caption
    aArray(4, 4) = lblVF_4.Caption
    aArray(5, 0) = lbl_I5.Caption
    aArray(5, 1) = lblQA_5.Caption
    aArray(5, 2) = lblQM_5.Caption
    aArray(5, 3) = lblHF_5.Caption
    aArray(5, 4) = lblVF_5.Caption
    aArray(6, 0) = lbl_I6.Caption
    aArray(6, 1) = lblQA_6.Caption
    aArray(6, 2) = lblQM_6.Caption
    aArray(6, 3) = lblHF_6.Caption
    aArray(6, 4) = lblVF_6.Caption
    aArray(7, 0) = lbl_I7.Caption
    aArray(7, 1) = lblQA_7.Caption
    aArray(7, 2) = lblQM_7.Caption
    aArray(7, 3) = lblHF_7.Caption
    aArray(7, 4) = lblVF_7.Caption
    aArray(8, 0) = lbl_I8.Caption
    aArray(8, 1) = lblQA_8.Caption
    aArray(8, 2) = lblQM_8.Caption
    aArray(8, 3) = lblHF_8.Caption
    aArray(8, 4) = lblVF_8.Caption
    aArray(9, 0) = lbl_I9.Caption
    aArray(9, 1) = lblQA_9.Caption
    aArray(9, 2) = lblQM_9.Caption
    aArray(9, 3) = lblHF_9.Caption
    aArray(9, 4) = lblVF_9.Caption
    aArray(10, 0) = lbl_I10.Caption
    aArray(10, 1) = lblQA_10.Caption
    aArray(10, 2) = lblQM_10.Caption
    aArray(10, 3) = lblHF_10.Caption
    aArray(10, 4) = lblVF_10.Caption
    aArray(11, 0) = lbl_I11.Caption
    aArray(11, 1) = lblQA_11.Caption
    aArray(11, 2) = lblQM_11.Caption
    aArray(11, 3) = lblHF_11.Caption
    aArray(11, 4) = lblVF_11.Caption
    '/ This will do the actual transfer
    TransferData
End Sub

This should work as long as your label names are correct.  The next step will be to open an Excel file to which you wish transfer these variables.  First you must establish a reference to the Excel object. To that go to Project/References and look for:  Microsoft Excel 10.0 Object Library or something similar. After checking that off, use this code:

Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Add
    Set wsh = wkb.Worksheets(1)

    wsh.Cells(1,1) = aArray
    xl.Visible = True
End Sub

Leon

0
 
SYMOTOMAuthor Commented:
Dear Leon
First I verified the labels are correct. After had opened an Excel file, named Libro1.xls to which I want to transfer those variables. Also I went to Projects/References and fill the checkbox for Microsoft Excel 9.0 Object Library
and when I click the aArray command bottom it appears an screen telling "The copy of atenuacion is correct - (0-1) and appears aaaaaa new excel worksheet named Libro3, but empty.

I wait your kind answer
Moises
Martes Mayo 30 2006 18 36 hs. (GMT -3:00)
0
 
leonstrykerCommented:
If you want the data to be transfered to Libro1.xls, then you need to modify your code to as follows:

Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim strPath As string
    strPath = "C:\"                       ' Change this with the path to your file
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Open(strPath & "Libro1.xls")
    Set wsh = wkb.Worksheets(1)

    wsh.Cells(1,1) = aArray
    xl.Visible = True
End Sub

Leon
0
 
SYMOTOMAuthor Commented:
Dear Leon:
Your first code:
Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Add
    Set wsh = wkb.Worksheets(1)

    wsh.Cells(1,1) =lblHF_11.Caption  ' (=3802999)                    'aArray
    xl.Visible = True
End Sub
runs quite well, but only for one label and I get the real value but without dot.
as you can see: (3802999 instead of 380.2999)
-----------------------------------------------------------------------------------------
About your second code:
Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim strPath As string
    strPath = "C:\"                       ' Change this with the path to your file
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Open(strPath & "Libro1.xls")
    Set wsh = wkb.Worksheets(1)

    wsh.Cells(1,1) = aArray
    xl.Visible = True
End Sub
I enter strPath=" The path I thought was the complete aArray"
and I get a warming box like this:
-------------------------------------------
Microsoft Visual Basic  
Icon: yellow triangle with an !
User-defined type not defined (It refer to:"Sub TransferData()" and to: "Dim xl As Excel.Application")
Botton: OK           Botton: Help
----------------------------------------
I have a doubt about  the expresion (strPath = "C:\" ), because my aArray is  in
 
strPath = "DISCO 2 ME (D:)/_____ATENUACION DE CRECIDAS v3.3.10 cc/Copia de ATENUACION CORRECTA CON ARRAY- 12QA - 22-MAYO-06 - 17 18 hs/Copia de ATENUACION CORRECTA.frm"
-------------------------------------------
Thank you very much for the important assistance you are giving me. I think that we are near from the correct final solution.
Moises
Wednesday, May 31 2006 12 55 hs. (GMT -3:00)







-------------------------------------------------------------------------------
I apllied
0
 
SYMOTOMAuthor Commented:
Dear Leon:
I hope you do not forget me an as soon as possible you send me  the steps to follow for get the link
Best regards
Moises
Wednesday, May 31 2006 20 57 hs. (GMT -3:00)
0
 
SYMOTOMAuthor Commented:
Dear Leon:
Excuse me,I tell you I am waiting for your assitance to follow with the code
Best regards
Moises
Thursday, June 1 2006 20 08 hs. (GMT -3:00)
0
 
leonstrykerCommented:
strPath <-- This variable should hold the path to your Excel workbook, where you want the data to appear

>User-defined type not defined (It refer to:"Sub TransferData()" and to: "Dim xl As Excel.Application")

Did you establish a reference to the Excel object from your VB applicaiton
0
 
SYMOTOMAuthor Commented:
Dear Leon
Because I had get many obstacles with this question assistance and with jimhorn expert I want to delete this question, but  I do not  know how to do it in a correctly way. Please, if possible tell me how I can delete this question: HOW TO LINK VALUES IN A FORM OF VISUAL BASIV 6 with an EXCEL TABLE
I wait for your notices
Moises
Friday, June 2 2006 17 57 hs. (GMT -3:00)
0
 
leonstrykerCommented:
You will not be able to delete this question.

You have two options, you can either ask for this question to be unaccepted and we can try to finish this, or you can leave it the way it is, in which case I will ask for it to be unaccepted and we can try to finish this.

Leon
0
 
SYMOTOMAuthor Commented:

I went to http://www.experts-exchange.com/help.jsp#hi71
and I got the below answer, but I do not find any link
As you can see, there is great lost of time with this question.
Friday, June 2 2006 19 05 hs. (GMT -3:00)
-------------------------------------------------------------
Accepted Answer from GranMod
Date: 05/17/2006 10:39AM PDT
Grade: A
 Accepted Answer  
I already posted a link to our helpfile in your question.
Closing this request, but feel free to ask more.

GranMod
The Experts Exchange
Community Support Moderator of all Ages
------------------------------------------------------------------
0
 
SYMOTOMAuthor Commented:
Leon
I am very interested to continue with your assistance, but I do not receive any
email from you in may Outlook Express. I do not give you my email address because it is prohibit. Really I do not what to do.
Regards
SYMOTOM
Moises
Sábado Junio 3 2006 12 25 hs. (GMT -3:00)
0
 
SYMOTOMAuthor Commented:
Well, I have just received again this  Experts-Exchange email. I will be glade to continue having your assistance so I am waiting an answer from you.
Regards
SYMOTOM
Sábado Junio 3 2006 13 14 hs. (GMT -3:00)
0
 
SYMOTOMAuthor Commented:
Dear Leon
I have problem with the following expresion:
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim strPath As String
    I have problem with this: strPath = "Libro1.xls"
    Set xl = New Excel.Application
     I have problem with this: Set wkb = xl.Workbooks.Open(strPath & "Libro1.xls")
        Set wsh = wkb.Worksheets(1)
    wsh.Cells(1, 1) = aARRAY(11, 4)
Then I ask you:
1 - Where must I put in my PC "Libro1" to get an acceptable "strPath="
2 - After 1, which will be the expresion for " Set wkb = xl.Workbooks.Open(strPath & "Libro1.xls")"
I am waiting for your reply
Thanks
SYMOTOM
Saturday, June 3 2006 20 16 hs. (GMT -3:00)
0
 
leonstrykerCommented:
strPath should contain teh full path to your file.  This path would include the drive location, subdirectories and thene the file name. Since I do not know where your file is located, I used C:\ as an example.  What is the path to your Excel file?

Leon
0
 
SYMOTOMAuthor Commented:
Dear León:
I used the code you gave me:
-------------------------------------------- The initial Code --------------------------
"Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim strPath As string
    strPath = "C:\"                       ' Change this with the path to your file
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Open(strPath & "Libro1.xls")
    Set wsh = wkb.Worksheets(1)

    wsh.Cells(1,1) = aArray
    xl.Visible = True
End Sub"
-------------------------------------------------------------------------------------
and with it I have built the following code:
----------------------------- The code I have built ------------------------------
Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Add
    Set wsh = wkb.Worksheets(1)
   
    wsh.Cells(2, 1) = lbl_I0.Caption
    wsh.Cells(2, 2) = lblQA_0.Caption
    wsh.Cells(2, 3) = lblQM_0.Caption
    wsh.Cells(2, 4) = lblHF_0.Caption
    wsh.Cells(2, 5) = lblVF_0.Caption
   
    wsh.Cells(3, 1) = lbl_I1.Caption
    wsh.Cells(3, 2) = lblQA_1.Caption
    wsh.Cells(3, 3) = lblQM_1.Caption
    wsh.Cells(3, 4) = lblHF_1.Caption
    wsh.Cells(3, 5) = lblVF_1.Caption
   
    wsh.Cells(4, 1) = lbl_I2.Caption
    wsh.Cells(4, 2) = lblQA_2.Caption
    wsh.Cells(4, 3) = lblQM_2.Caption
    wsh.Cells(4, 4) = lblHF_2.Caption
    wsh.Cells(4, 5) = lblVF_2.Caption
   
    wsh.Cells(5, 1) = lbl_I3.Caption
    wsh.Cells(5, 2) = lblQA_3.Caption
    wsh.Cells(5, 3) = lblQM_3.Caption
    wsh.Cells(5, 4) = lblHF_3.Caption
    wsh.Cells(5, 5) = lblVF_3.Caption
   
    wsh.Cells(6, 1) = lbl_I4.Caption
    wsh.Cells(6, 2) = lblQA_4.Caption
    wsh.Cells(6, 3) = lblQM_4.Caption
    wsh.Cells(6, 4) = lblHF_4.Caption
    wsh.Cells(6, 5) = lblVF_4.Caption
   
    wsh.Cells(7, 1) = lbl_I5.Caption
    wsh.Cells(7, 2) = lblQA_5.Caption
    wsh.Cells(7, 3) = lblQM_5.Caption
    wsh.Cells(7, 4) = lblHF_5.Caption
    wsh.Cells(7, 5) = lblVF_5.Caption
   
    wsh.Cells(8, 1) = lbl_I6.Caption
    wsh.Cells(8, 2) = lblQA_6.Caption
    wsh.Cells(8, 3) = lblQM_6.Caption
    wsh.Cells(8, 4) = lblHF_6.Caption
    wsh.Cells(8, 5) = lblVF_6.Caption
   
    wsh.Cells(9, 1) = lbl_I7.Caption
    wsh.Cells(9, 2) = lblQA_7.Caption
    wsh.Cells(9, 3) = lblQM_7.Caption
    wsh.Cells(9, 4) = lblHF_7.Caption
    wsh.Cells(9, 5) = lblVF_7.Caption
   
    wsh.Cells(10, 1) = lbl_I8.Caption
    wsh.Cells(10, 2) = lblQA_8.Caption
    wsh.Cells(10, 3) = lblQM_8.Caption
    wsh.Cells(10, 4) = lblHF_8.Caption
    wsh.Cells(10, 5) = lblVF_8.Caption
   
    wsh.Cells(11, 1) = lbl_I9.Caption
    wsh.Cells(11, 2) = lblQA_9.Caption
    wsh.Cells(11, 3) = lblQM_9.Caption
    wsh.Cells(11, 4) = lblHF_9.Caption
    wsh.Cells(11, 5) = lblVF_9.Caption
   
    wsh.Cells(12, 1) = lbl_I10.Caption
    wsh.Cells(12, 2) = lblQA_10.Caption
    wsh.Cells(12, 3) = lblQM_10.Caption
    wsh.Cells(12, 4) = lblHF_10.Caption
    wsh.Cells(12, 5) = lblVF_10.Caption
   
    wsh.Cells(13, 1) = lbl_I11.Caption
    wsh.Cells(13, 2) = lblQA_11.Caption
    wsh.Cells(13, 3) = lblQM_11.Caption
    wsh.Cells(13, 4) = lblHF_11.Caption
    wsh.Cells(13, 5) = lblVF_11.Caption
   
        xl.Visible = True

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

and "¡¡¡¡ EUREKA !!!" with this new code I got to transfer the integer data from VB 6 to Excel2000 worksheet; previously I made the following things:
1-Microsoft Excel 9.0 Object Library
2-Open an Excel Worksheet
3-Running the Visual Basic 6.0 application
and the table I got is:

------------------------------------   Excel Table ---------------------------
( 0 - 1 )      131        11,68      379,45      1.337,47
( 1 - 2 )      204        65,46      379,96      1.349,11
( 2 - 3 )      494      201,47      381,02      1.373,68
( 3 - 4 )      951      514,95      382,56      1.410,31
( 4 - 5 )   1115      874,32      383,38      1.430,53
( 5 - 6 )      990        1.004,65      383,33      1.429,29
( 6 - 7 )      699      887,34      382,69      1.413,47
( 7 - 8 )      500      683,95      382,05      1.398,02
( 8 - 9 )      350      509,48      381,49      1.384,62
( 9 - 10 )      244      374,65      381,02      1.373,65
( 10 - 11 )      180      277,27      380,67      1.365,48
( 11 - 12 )      98      200,11      380,30      1.356,90
--------------------------------------------------------------------------------
Now I have another need, and it is to know what expression I must use to get transfer character names like "ORDEN",  "COST", ETC instead integers values
that use "wsh.Cells(1,1) = Array"
Again, Thank you very much for the assistance you are giving me.
I am waiting your reply..
Best regards
SYMOTOM
Moises
Monday, June 5 2006 11 21 hs. (GMT -3:00)
0
 
leonstrykerCommented:
In the procedure you build, you are not using an array, but rather assigning values directly to the cells. To place text into cells, you would need to do it the same way.  Some thing like:

wsh.Cells(1, 1) = "ORDEN"

Leon
0
 
SYMOTOMAuthor Commented:
Dear Leon:
I proved your wshCells(1,1)= "ORDEN" and all ran well. Thanks.
Other, above you told me:"you are not using an array, but rather assigning values directly to the cells", but if I delete my aARRAy, the
program does not run.
It is important to me to know why because I must clean my general code whithout damage my application.
Can you give some comment about it?
Regards
SYMOTOM
Moises
Monday June 5 2006 14 05 hs. (GMT -3:00)
0
 
leonstrykerCommented:
In this code:

Sub TransferData()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wsh As Excel.Worksheet
    Set xl = New Excel.Application
    Set wkb = xl.Workbooks.Add
    Set wsh = wkb.Worksheets(1)
   
    wsh.Cells(2, 1) = lbl_I0.Caption
    wsh.Cells(2, 2) = lblQA_0.Caption
    wsh.Cells(2, 3) = lblQM_0.Caption
    wsh.Cells(2, 4) = lblHF_0.Caption
    wsh.Cells(2, 5) = lblVF_0.Caption
   
    wsh.Cells(3, 1) = lbl_I1.Caption
    wsh.Cells(3, 2) = lblQA_1.Caption
    wsh.Cells(3, 3) = lblQM_1.Caption
    wsh.Cells(3, 4) = lblHF_1.Caption
    wsh.Cells(3, 5) = lblVF_1.Caption
   
    wsh.Cells(4, 1) = lbl_I2.Caption
    wsh.Cells(4, 2) = lblQA_2.Caption
    wsh.Cells(4, 3) = lblQM_2.Caption
    wsh.Cells(4, 4) = lblHF_2.Caption
    wsh.Cells(4, 5) = lblVF_2.Caption
   
    wsh.Cells(5, 1) = lbl_I3.Caption
    wsh.Cells(5, 2) = lblQA_3.Caption
    wsh.Cells(5, 3) = lblQM_3.Caption
    wsh.Cells(5, 4) = lblHF_3.Caption
    wsh.Cells(5, 5) = lblVF_3.Caption
   
    wsh.Cells(6, 1) = lbl_I4.Caption
    wsh.Cells(6, 2) = lblQA_4.Caption
    wsh.Cells(6, 3) = lblQM_4.Caption
    wsh.Cells(6, 4) = lblHF_4.Caption
    wsh.Cells(6, 5) = lblVF_4.Caption
   
    wsh.Cells(7, 1) = lbl_I5.Caption
    wsh.Cells(7, 2) = lblQA_5.Caption
    wsh.Cells(7, 3) = lblQM_5.Caption
    wsh.Cells(7, 4) = lblHF_5.Caption
    wsh.Cells(7, 5) = lblVF_5.Caption
   
    wsh.Cells(8, 1) = lbl_I6.Caption
    wsh.Cells(8, 2) = lblQA_6.Caption
    wsh.Cells(8, 3) = lblQM_6.Caption
    wsh.Cells(8, 4) = lblHF_6.Caption
    wsh.Cells(8, 5) = lblVF_6.Caption
   
    wsh.Cells(9, 1) = lbl_I7.Caption
    wsh.Cells(9, 2) = lblQA_7.Caption
    wsh.Cells(9, 3) = lblQM_7.Caption
    wsh.Cells(9, 4) = lblHF_7.Caption
    wsh.Cells(9, 5) = lblVF_7.Caption
   
    wsh.Cells(10, 1) = lbl_I8.Caption
    wsh.Cells(10, 2) = lblQA_8.Caption
    wsh.Cells(10, 3) = lblQM_8.Caption
    wsh.Cells(10, 4) = lblHF_8.Caption
    wsh.Cells(10, 5) = lblVF_8.Caption
   
    wsh.Cells(11, 1) = lbl_I9.Caption
    wsh.Cells(11, 2) = lblQA_9.Caption
    wsh.Cells(11, 3) = lblQM_9.Caption
    wsh.Cells(11, 4) = lblHF_9.Caption
    wsh.Cells(11, 5) = lblVF_9.Caption
   
    wsh.Cells(12, 1) = lbl_I10.Caption
    wsh.Cells(12, 2) = lblQA_10.Caption
    wsh.Cells(12, 3) = lblQM_10.Caption
    wsh.Cells(12, 4) = lblHF_10.Caption
    wsh.Cells(12, 5) = lblVF_10.Caption
   
    wsh.Cells(13, 1) = lbl_I11.Caption
    wsh.Cells(13, 2) = lblQA_11.Caption
    wsh.Cells(13, 3) = lblQM_11.Caption
    wsh.Cells(13, 4) = lblHF_11.Caption
    wsh.Cells(13, 5) = lblVF_11.Caption
   
   xl.Visible = True

End Sub

There is no array.  If you are actually using something else please post it so I can take a look at it.

Leon
0
 
SYMOTOMAuthor Commented:
The array I am using (if I clean it from the general code, the application no transferr anything to excel), is:
---------------------------------------------------------
Private Sub cmdARRAY_Click()
Dim aArray(11, 4) As String

    aArray(0, 0) = lbl_I0.Caption
    aArray(0, 1) = lblQA_0.Caption
    aArray(0, 2) = lblQM_0.Caption
    aArray(0, 3) = lblHF_0.Caption
    aArray(0, 4) = lblVF_0.Caption
    aArray(1, 0) = lbl_I1.Caption
    aArray(1, 1) = lblQA_1.Caption
    aArray(1, 2) = lblQM_1.Caption
    aArray(1, 3) = lblHF_1.Caption
    aArray(1, 4) = lblVF_1.Caption
    aArray(2, 0) = lbl_I2.Caption
    aArray(2, 1) = lblQA_2.Caption
    aArray(2, 2) = lblQM_2.Caption
    aArray(2, 3) = lblHF_2.Caption
    aArray(2, 4) = lblVF_2.Caption
    aArray(3, 0) = lbl_I3.Caption
    aArray(3, 1) = lblQA_3.Caption
    aArray(3, 2) = lblQM_3.Caption
    aArray(3, 3) = lblHF_3.Caption
    aArray(3, 4) = lblVF_3.Caption
    aArray(4, 0) = lbl_I4.Caption
    aArray(4, 1) = lblQA_4.Caption
    aArray(4, 2) = lblQM_4.Caption
    aArray(4, 3) = lblHF_4.Caption
    aArray(4, 4) = lblVF_4.Caption
    aArray(5, 0) = lbl_I5.Caption
    aArray(5, 1) = lblQA_5.Caption
    aArray(5, 2) = lblQM_5.Caption
    aArray(5, 3) = lblHF_5.Caption
    aArray(5, 4) = lblVF_5.Caption
    aArray(6, 0) = lbl_I6.Caption
    aArray(6, 1) = lblQA_6.Caption
    aArray(6, 2) = lblQM_6.Caption
    aArray(6, 3) = lblHF_6.Caption
    aArray(6, 4) = lblVF_6.Caption
    aArray(7, 0) = lbl_I7.Caption
    aArray(7, 1) = lblQA_7.Caption
    aArray(7, 2) = lblQM_7.Caption
    aArray(7, 3) = lblHF_7.Caption
    aArray(7, 4) = lblVF_7.Caption
    aArray(8, 0) = lbl_I8.Caption
    aArray(8, 1) = lblQA_8.Caption
    aArray(8, 2) = lblQM_8.Caption
    aArray(8, 3) = lblHF_8.Caption
    aArray(8, 4) = lblVF_8.Caption
    aArray(9, 0) = lbl_I9.Caption
    aArray(9, 1) = lblQA_9.Caption
    aArray(9, 2) = lblQM_9.Caption
    aArray(9, 3) = lblHF_9.Caption
    aArray(9, 4) = lblVF_9.Caption
    aArray(10, 0) = lbl_I10.Caption
    aArray(10, 1) = lblQA_10.Caption
    aArray(10, 2) = lblQM_10.Caption
    aArray(10, 3) = lblHF_10.Caption
    aArray(10, 4) = lblVF_10.Caption
    aArray(11, 0) = lbl_I11.Caption
    aArray(11, 1) = lblQA_11.Caption
    aArray(11, 2) = lblQM_11.Caption
    aArray(11, 3) = lblHF_11.Caption
    aArray(11, 4) = lblVF_11.Caption
    '/ This will do the actual transfer
    TransferData
End Sub
---------------------------------------------------------------------------------
I can send you the total code of my application. But unless there is a way in Experts-Exchange , that I do not know, to send attachments, I think I need your previous agree to do it.
SYMOTOM
Moises
Monday, June 5 2006 14 25 hs. (GMT -3:00)

0
 
leonstrykerCommented:
You can find my email in my profile.  Please send the complete code of these procedures. I will then paste the relevent parts to this thread.

Leon
0
 
SYMOTOMAuthor Commented:
Dear Leon:
1 - Is your email: <email removed by leonstryker>?
2 - In your Member Profile page you say: "Please include an EE reference in the subject so I can spot your email since all emails automatically will be sent to the Junk mail folder".
3 - So I ask you if the EE reference I must include in the subject is:
"Title: HOW TO LINK VALUES IN A FORM OF VISUAL BASIC 6 with an EXCEL TABLE"
I am waiting your replay
Thanks
SYMOTOM
Moises
Monday, June 5 2006 17 34 hs. (GMT -3:00)
0
 
SYMOTOMAuthor Commented:
Dear Leon
I had emailed the Program and Code corresponding at this Question.
I wait for notices about it.
Regards
SYMOTOM
Tuesday, June 6 2006 13 11 hs. (GMT -3:00)
0
 
leonstrykerCommented:
Ok, lets go through this.

This part has nothing to do with your question, but is important for you anyway:

>Dim C7_131, C8_131, C9_131, C10_131, C11_131, C12_131 As Double

- In the previous line of code only the last variable "C12_131" is declared as a double.  The rest are of Variant data type.

- If you are declaring a variable on a module level you should declare as either Private or Public. In your cases, the declaration is actually Private although you use Dim keyword.

- You really should use a standard convention for variable. For example a double should look something like: dblC7_131.

A more acceptable declaration string would be as follows:

Private dblC7_131 As Double, dblC8_131 As Double, dblC9_131 As Double
Private dblC10_131 As Double, dblC11_131 As Double, dblC12_131 As Double

In fact then you are using so many variables of the same type, you should consider using Type for your variable declarations. It will make you code more readable.

>D4_131 = txtQA_0_1.Text

This generates an error if you do not have a number entered in to txtQA_0_1 text box. You should at the very least do something like this to take care of errors if nothing is entered in that box:

D4_131 = "0" & txtQA_0_1.Text

But, you should also check to see if the user entered a text value, since this will also generate an error.

In the code, as you have it now, you are not using the array at all, so you may as well take it all out.

To open your file Libro1.xls, I modified it to look like this:

Set wkb = xl.Workbooks.Open(App.Path & "\Libro1.xls")

This opens the Excel fiel you want if it is in the same directory as your application.

Let me know if you have any other questions,

Leon

 
0
 
SYMOTOMAuthor Commented:
Dear Leon:
Thank you very much for your comments about the variables of my application. I
take them as a very valuable thing.

Other theme:
By the moment I have no other question and I consider fully satisfactory the results obtained , because of it, I want to close the case according to the norms of EE, but  I do not know how to do it. Would it be possible for you to help me in closing the case?  
Thanks in advance
SYMOTOM
Moisés
Miércoles Junio 7 2006 19 18 hs. (GMT -3:00)
0
 
leonstrykerCommented:
Thank you fo rthe grade,

Leon
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.

All Courses

From novice to tech pro — start learning today.