Pasting in Data from File Path

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,122
Office Version
365
Platform
Windows
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
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,122
Office Version
365
Platform
Windows
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
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,122
Office Version
365
Platform
Windows
It should be
Code:
   If Fname = False Then Exit Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,122
Office Version
365
Platform
Windows
You're welcome
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,122
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,415
Messages
5,450,282
Members
405,601
Latest member
API_newnoob

This Week's Hot Topics

Top