[RESOLVED] Visual Basic 6 Through Excel

[Joye]

hi everyone

I have a program made by VB6 contains some textboxes and some commandbuttuns,On my desktop there is an existing Excel file (my2k.xls).

NOW: I want the user to write some numbers and letters in the textboxes and then clicks the command button.
The letters and numbers should be transferred in single cells on that Excel file (my2k.xls).For example :

user writes : ORDER 123

The program transfers that to the excel sheet on specific cells like : O R D E R 1 2 3 each letter or number in single cell.

Witch means : first cell (S8 = "O") second cell (T8 = "R") etc..


The Q's now:

1- How to make VB6 (and not VBA) connects with that existing Excel file and communicate with it?

2-How to transfer texts from VB6 to certain cells on that Excel sheet?

I really need this and I wish someone could help.

If not too much, attached open source example would be helpful.

Thank you very much for your respond

[westconn1]

check out the tutorial, for automating office applications including excel, in the second thread in the office development forum

[Joye]

Thank you very much westconn1

First problem solved so far

In process to solve the second one and get the job done.

I used this thread :

http://www.vbforums.com/showthread.php?t=391665

Was so helpful and rich of infos

<--------<< This guy will get promoted so soon

[Joye]

Guess What ??
Not solved yet !!
The second question I mean !!
And I'm 100% sure it's so easy..But maybe I'm not making this clear for many.
so plz let me know..

[westconn1]

post the code you have so far,

basically
workbookobject.sheets("sheet1").range("b99").value = "my test"
use whatever workbook object you have already created
or if you have created a sheet object, you can omit the workbook object

[Joye]

Thanks for your respond

my codes are :

General:

Dim oXLApp As Excel.Application         'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

FormLoad:

Private Sub Form_Load()
Set oXLApp = New Excel.Application    'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open("C:\Program Files\2K Writer\2Kilo Form.xls") 'Open an existing workbook
Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
End Sub

CommandButton:

oXLSheet.Range("S8").Formula = Text1.Text     'here is my problem (It works but not like I want it)

I want (text1.text) to be Distributed in S8 , T8 , U8 and V8

let's say text1.text = "NICE"

After Command click it should be :

S8 = N
T8 = I
U8 = C
V8 = E


I hope it's clear now and thank you again.

[si_the_geek]

To work with individual characters from a string use the Mid function, eg:

MsgBox Mid("hello", 5, 1) 'displays just the 5th character

To determine how long a string is, use the Len function, eg:

MsgBox Len("hello") 'displays 5

So to work with each character in a string, use a loop:

Dim intLoop as Integer
  For intLoop = 1 to Len(Text1.Text)
    MsgBox Mid(Text1.Text, intLoop, 1)  'displays each character in turn
  Next intLoop

All you need to do now is change MsgBox to something that puts the data into Excel. Note that it will be easier to use .Cells rather than .Range

[westconn1]

if the letters are spaced or anything they could be split into an array and the array assigned to the cells in a range

[Joye]

Sorry didn't work !!

The project has many textboxes to be pasted or transferred to the sheet..

I need a small change to this code:

oXLSheet.Range("S8").Formula = Text1.Text

Or maybe it's something to do with the Excel sheet to make what is pasted in S8 distributed into the individual cells.

If not. How to pick litters from one textbox? like I only want to copy the 5th letter from text1.

Thank you again.

[westconn1]

like I only want to copy the 5th letter from text1.

si gave you the code to do that before
letter = mid(text1, 5 ,1)

you can make a loop to put your string into different cells
this will put into row 2

for i = 1 to len(text1)
     oxlsheet.cells(2, i).value = mid(text1, i, 1)
next

[Joye]

Thank you very much

my problem got solved finally

I used this code and it worked very well:

Dim intLoop As Integer
For intLoop = 1 To Len(Text1)
     oXLSheet.Cells(7, 19 + intLoop).Value = Mid(Text1, intLoop, 1)
Next

Best Regards
And thank you again.