CODEWITHSUNDEEP
excelvba
Jared-Schrec
Jared-Schrec

Reputation: 1

Using VBA to open another excel file, copy data, and paste data into a textbox

The end goal of this project is for users to use their formatted excel sheet that have their equations built in. Then go to my excel sheet click "Import" and the data from their excel sheet will be imported into mine. I want the VBA code to browse files, open a file, copy a certain cell(s), and paste it into a textbox in my excel sheet. In the code below I was able to browse an excel file, open the file, copy data, but I got a run time error on the paste line (ThisWorkbook.Worksheets("SelectFile").TextBox1.Value.PasteSpecial xlPasteValues). Any feedback helps, thank you!

Private Sub CommandButton1_Click()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File and Import Range", FileFilter:="Excel Files (*.xls*,**xls*")
    If FileToOpen <> False Then
       Set OpenBook = Application.Workbooks.Open(FileToOpen)
       OpenBook.Sheets(1).Range("A2").Copy
       ThisWorkbook.Worksheets("SelectFile").TextBox1.Value.PasteSpecial xlPasteValues
       OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 2839

Answers (1)

hennep
hennep

Reputation: 660

' you cannot paste to the value property
ThisWorkbook.Worksheets("SelectFile").TextBox1.Value.PasteSpecial xlPasteValues

' and textbox1 does not support the pastspecial method:
ThisWorkbook.Worksheets("SelectFile").TextBox1.PasteSpecial xlPasteValues

what about:
ThisWorkbook.Worksheets("SelectFile").TextBox1.Value = OpenBook.Sheets(1).Range("A2").Value

Upvotes: 0

Related Questions