Pasting in Data from File Path

[nirvehex]

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!

 

[Fluff]

How about

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]

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?

 

[Fluff]

Like

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]

Fluff,

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

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]

It should be

   If Fname = False Then Exit Sub

 

[nirvehex]

Thank you!

 

[Fluff]

You're welcome

 

[nirvehex]

Sorry to bug you Fluff,

Something isn't working here:

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.

 

[Fluff]

How about

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