Pasting in Data from File Path

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
498
Office Version
  1. 365
Platform
  1. Windows
Hi, question on pasting in data from an excel file into another excel file - and I'm not even sure this possible, but wanted to see if it is.

I basically want to have a macro that pops open a File Open Box that allows you to select an excel file. Once you select that file, the macro references the data in that file on Sheet1 and copies cells A1 over to the end and down to the end and pastes it to the WOs tab in cell A2.

Is this possible to do?

Thanks!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How about
Code:
Sub nirvehex()
   Dim Fname As String
   Dim Wbk As Workbook
   
   Fname = Application.GetOpenFilename
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("sheet1").Range("A1").CurrentRegion.Copy ThisWorkbook.Sheets("WO").Range("A2")
   Wbk.Close False
End Sub
 
Upvote 0
Thanks Fluff! That worked great! Way easier than I thought. Any way to make it paste as values? Also if someone hits cancel on the file open box that pops up, is there a way for it to exit the script instead of going into debug mode?
 
Last edited:
Upvote 0
Like
Code:
Sub nirvehex()
   Dim Fname As String
   Dim Wbk As Workbook
   
   Fname = Application.GetOpenFilename
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("sheet1").Range("A1").CurrentRegion.Copy
   ThisWorkbook.Sheets("WO").Range("A2").PasteSpecial xlPasteValues
   Wbk.Close False
End Sub
 
Upvote 0
Fluff,

I also tried to modify the code to include a way for the user to hit cancel without breaking the script:

Code:
Sub WOInput()


Dim Fname As String
   Dim Wbk As Workbook
   
   Fname = Application.GetOpenFilename
   If Application.GetOpenFilename = False Then Exit Sub
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("WorkOrders").Range("A1").CurrentRegion.Copy ThisWorkbook.Sheets("WOs").Range("A2").PasteSpecialxlPasteValues
   Wbk.Close False
   


End Sub

But it makes me hit cancel twice before exiting. Any idea on how to make the code just exit if the user hits cancel once?

Thanks!
 
Upvote 0
It should be
Code:
   If Fname = False Then Exit Sub
 
Upvote 0
Sorry to bug you Fluff,

Something isn't working here:

Code:
Sub WOInput()


Dim Fname As String
   Dim Wbk As Workbook
   
   Fname = Application.GetOpenFilename
   If Fname = False Then Exit Sub
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("WorkOrders").Range("A1").CurrentRegion.Copy ThisWorkbook.Sheets("WOs").Range("A2").PasteSpecialxlPasteValues
   Wbk.Close False
   


End Sub

Something errors out when I select the file and hit ok.
Do I have the exit sub on the wrong line?

Also - the pastespecialxlPasteValues causes a debug error too for some reason.
 
Last edited:
Upvote 0
How about
Code:
Sub nirvehex()
   Dim Fname As Variant
   Dim Wbk As Workbook
   
   Fname = Application.GetOpenFilename
   If Fname = False Then Exit Sub
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets("sheet1").Range("A1").CurrentRegion.Copy
   ThisWorkbook.Sheets("WO").Range("A2").PasteSpecial xlPasteValues
   Wbk.Close False
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top