Excel in VB6 1

[rikeman]

Hi,

I would like to make a visual basic 6 application that works with excel :

There is a textbox and a command button, and when I write something in the textbox and I've clicked on the command button, the application must save the text in the textbox in an excel document (ex. in cell A5, and excel document "c:\new\test.xls"). Can someone help me?
And a little more question: I've got another textbox and another command button, and when I click on that button, the program must open an excel file and must show in the textbox what there is in the cel B5?

Greetz.

 

[mjk9564]

Have you looked into using VBA in Excel to do this? You can do both of these operations in it very easily.

 

[simonkue]

Assuming you wish to do this from within your VB pgm, you could try the following:

Select Project/References and include the Excel object library.
You now have the ability to use Excel objects....

So to populate a newly created worksheet, I've used the following code:
Incidentally the rsExtract is a recordset to an extract table I've already created.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

' We need this line to ensure excel remains visible if we switch
' to the active sheet

Set objTemp = objExcel

excelVersion = Val(objExcel.Application.Version)
If (excelVersion >= 8) Then
Set objExcel = objExcel.ActiveSheet
End If

' Place the names of the fields as the column headers

iRowIndex = 1
iColIndex = 1

For iColIndex = 1 To iFieldCount
With objExcel.Cells(iRowIndex, iColIndex)
.value = rsExtract.Fields(iColIndex - 1).Name
With .Font
.Name = "Arial"
.Bold = True
.Size = 10
End With
End With
Next

' Memory management

rsExtract.Close
Set rsExtract = Nothing

' Just add the data

With objExcel
For iRowIndex = 2 To iRecordCount + 1
For iColIndex = 1 To iFieldCount
.Cells(iRowIndex, iColIndex).value = avRows _
(iColIndex - 1, iRowIndex - 2)
Next
Next
End With

objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit

Hope this helps!

 

[rikeman]

I've tried all what you said, and I'v put the whole code into a command button. When I click on the button, he opens Excel, no problem. But then he gives the error "Sub or function not defined"; and he select "avRows _".

 

[simonkue]

rikeman,

Sorry, I don't understand the error. Can you give some more details please so that I can help further.
I know the code works for me so I suspect that I have a global variable setup that I haven't told you about.

 

[AndyWatt]

I think rikeman is having a problem with

.Cells(iRowIndex, iColIndex).Value = avRows(iColIndex - 1, iRowIndex - 2)

The avRows array is not declared anywhere in the code sample.

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

Thanx! But I use the code:

Option Explicit

Dim xlsApp As Excel.Application
Dim wrdApp As Word.Application

Private Sub Command1_Click()
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
'Put text in to the cell that is selected
.ActiveCell.Value = "Hi"
'Put text into A3 regardless of the selected cell
.Range("A3").Value = "This is an example of connecting to Excel"
End With
End Sub

Private Sub Command2_Click()
'close the workbook
xlsApp.Workbooks.Close
'Close Excel
xlsApp.Quit
End Sub

And this code works perfect; anyway, thanx for your help!
Can I ask one more little question? So, I have an Excel document; and my VB project must search in that document after ex. "test" and must place that in a textbox.

Greetz,
Rikeman.

 

[AndyWatt]

The example of Find function in Excel Visual Basic Help:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

Where must I put what the program must search for and where must I place the code that the program must show the searched text? How can I save de Excel document?

 

[AndyWatt]

The example I culled from the Excel online documentation searches for the value 2 in cells and replaces with 5, so replacing the 2 with whatever it is you want to search for should do the trick.

In the example the value in c.Value would contain the match for the found text:

txtResult.Text = c.Value

for example.

> How can I save de Excel document?

The Save method of the Workbook object. e.g.:

xlsApp.ActiveWorkbook.Save


Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

Thanx! Everythin' works perfect! But when I search for a text in a cell (ex. A3); can I do that the program shows in another textbox what there is in cell A4?

Greetz,
Rikeman.

 

[AndyWatt]

If I've understood correctly...

You can retrieve the value in any cell by:

txtContent.Text = xlsApp.ActiveWorkbook.ActiveSheet.Range("A4").Value

Hope this helps.

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

No, I mean: I search for a text (ex. test) in collum A; my program find this text in cell A11; then the program must show in textbox1 the searched text (test) and in textbox2 the text in cell B11 (so, left from cell A11)?

 

[AndyWatt]

Ah.

Okay. So presumably somewhere in your code you have the value that is being searched for, which is easy enough to write to a textbox.

You can examine the Row and Column properties of a Range Object (returned by the Find Method) to obtain the address of the matching Cell and add 1 to the Column to point to the Cell in Column B:

Put a value in, for example, cell A1 then:

Dim x As Excel.Worksheet
Dim y As Excel.Range

Set x = ActiveWorkbook.ActiveSheet
Set y = x.Range("A1")
MsgBox "Row=" & y.Row & vbCrLf & "Column=" & y.Column

Hope that makes sense.


Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

How can I put something in a cell using row & collum and not cell name? ex.: NOT "A3"; but Column 1, Row 3

 

[AndyWatt]

Dim objXlSheet As Excel.Worksheet

...

objXlSheet.Cells(1, 2).Value = "It Works!"

Andy
--
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[rikeman]

How can I set ONLY a left border to a cell? And how can I put a picture from a picture box in a cell?

 

[johnwm]

Read faq222-2244 to see forum usage guidelines. I suggest a new thread for a new question.

Most of these (fairly easy) VB/Excel questions are solved by recording a Macro in Excel and then dissecting the code for your own use in VB

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts