CODEWITHSUNDEEP
vbaexcel-2010
user1739280
user1739280

Reputation: 73

How to send textbox value to excel worksheet?

Private Sub Command3_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add


'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Cells(1, 1).Value = Text1.Text
oSheet.Cells(1, 2).Value = "First Name"
oSheet.Cells("A1:B1").Font.Bold = True
oSheet.Cells(2, 1).Value = "Doe"
oSheet.Cells(2, 2).Value = "John"

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
End Sub

First VB coding I've done in 16 years... so go easy on me... Okay, this is a generic code but the idea is the same. Where is says: ' oSheet.Cells(1, 1).Value = Text1.Text ' comes back with an error of "Method or meta member not found". How can I fix this? I've changed the name from Text1 to something else Dim (new name) as string, object and so on,(I got desperate, okay?!) but it continued giving me the same error. I've tried to look online but nothing truly explains why this happens. I know this is a stupid question for MANY of you, but remember, some of us have to start SOMEWHERE, help a little. :) Thanks everyone!

Oh, and what I plan to do is a vb6 form that will record the input data from the textboxes 1-15 to excel, after it finds the next empty row to record its data to. And the part that says, set oBook = oExcel.Workbooks.Add, will say Open("blahblah.xlsx") as you do. so this is not something that will continue to add a new file every time.

Upvotes: 1

Views: 1922

Answers (1)

Jeanno
Jeanno

Reputation: 2859

Use oSheet.Cells(1, 1).Value = Shapes(1).TextFrame.Characters.Text instead. Assuming there is only one shape on your worksheet and it is a textbox. Other wise you will need to loop through all shapes on your sheet using something like

Sub LoopShapes()
    Dim shp As Excel.Shape
    For Each shp In Shapes
        If shp.Type = msoTextBox Then
            Debug.Print shp.TextFrame.Characters.Text
        End If
    Next shp
End Sub

Please let me know if this works for you.

Upvotes: 2

Related Questions