0

I have 2 sheets, in one Sheet there are 2 buttons Browsefile and Openfile and one textbox TextBox1. I use the Browsefile button to select a file that I want to open and use the Openfile button to open the workbook.

The trouble is, it opens in a new workbook instead of adding it to my active workbook. How could I solve this?

Public fileStr As String
Sub GetOpenFile()

fileStr = Application.GetOpenFilename()
Worksheets("Sheet1").TextBox1.Value = fileStr

End Sub
Sub Paste_Click()
Dim wbk1 As Workbook, wbk2 As Workbook

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Add(fileStr)

wbk2.Sheets(1).Cells.Copy wbk1.Worksheets("Sheet2").Cells(1, 1)

End Sub
Community
  • 1
  • 1
Cwala
  • 29
  • 1
  • 2
  • 12
  • So you want it to be opened in Sheet2 instead of a new workbook? – CustomX Oct 10 '12 at 12:13
  • Yes, that what I really want it to do – Cwala Oct 10 '12 at 12:15
  • You cannot open a `XLS(X(M))` formatted file directly as a tab into another workbook, so the way you have it coded is the the only way, if your file is in the `XLS(X(M))`. If the file is in `CSV` or `TXT` format, then you can use `DataConnections` to "query" the data into the worksheet of `wkb1'. – Scott Holtzman Oct 10 '12 at 12:47
  • If your file is in `XLS(X(M))` format and you could also add `wkb2.Close False` after wbk2.Sheets(1).Cells.Copy ...` line. Then the 2nd workbook will close and you won't have to worry about it being open. – Scott Holtzman Oct 10 '12 at 13:05

1 Answers1

1

Try this :) It will allow you to select the file and copy the first sheet at the end of your opened workbook.

Sub Paste_Click()
Dim wbk1 As Workbook, wbk2 As Workbook

fileStr = Application.GetOpenFilename()

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Add(fileStr)

wbk2.Sheets("Sheet1").Copy After:=Workbooks("WorkbookNameYouCopyCodeInto").Sheets(3)
wbk2.Close
End Sub
CustomX
  • 9,948
  • 30
  • 85
  • 115
  • You can also add your `Worksheets("Sheet1").TextBox1.Value = fileStr` after fileStr = ... and then you only have 1 macro. – CustomX Oct 10 '12 at 12:45
  • Adding this answer to my code will allow you to copy all the sheets from the workbook you select. http://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba – CustomX Oct 10 '12 at 12:55
  • it gives me this error "Subscript out of range" highlight this line with yellow colour "wbk2.Sheets("Sheet1").Copy After:=Workbooks("Book1.xlsm").Sheets(3)" and still paste it into a different woorkbook? – Cwala Oct 10 '12 at 12:58
  • Book1.xlsm is your base file. So the filename you copy your code into. Edited it in my answer. – CustomX Oct 10 '12 at 13:00
  • while this cleans up the OP's code to be a bit more efficient and easy to read, how does this solve the question he is asking, as it still will open the workbook before copying it – Scott Holtzman Oct 10 '12 at 13:02
  • Did not notice this. Solved by adding `wbk2.Close` at the end. – CustomX Oct 10 '12 at 13:06
  • I can be able to copy the file ,my problem is when I paste a values it open a file into a new workbook while I dont want it to open in a new workbook.It also paste into my workbook. – Cwala Oct 10 '12 at 13:08
  • I added `wbk2.close`, this will close the workbook when the macro has finished copying the sheet. I did not notice it opening the workbook at first :) – CustomX Oct 10 '12 at 13:09