MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VB not VBA?

Posted by Gary Hewitt-Long on January 06, 2002 7:34 AM


I would like to create a stand alone application using Visual Basic 6.0 as opposed to Visual Basic for Applications that edits and controls an excel file.

Is this possible?

All machines that would run the application would have excel 2000 installed on them.

In VBA the following code would put the value of of text1 text box into cell A1 when Command1 was clicked:

Private Sub Command1_Click()

ActiveCell.Formula = Text1.Value

End Sub

How do I get VB to do this?

.Value isn't available after Text1 in the last line.

Also Windows("Book1.xls").Activate doesn't work??


Gary Hewitt-Long

Posted by Dank on January 06, 2002 3:04 PM


You'll have to make sure that VB know it's referencing Excel by either prefixing with a reference to Excel or by using the With keyword. E.g. this could be code to open a workbook from a command button on a VB (not VBA) form. It then puts the value of a textbox on this form into cell A1 of Book1. A textbox's value is its Text property, not Value like a MS Forms textbox.

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'You need to set a reference to the Excel object library

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Workbooks.Open "C:\temp\Book1.xls"
.Visible = True
.Range("A1").Value = Me.Text1.Text
End With

End Sub