Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Pasting in Data from File Path
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2011
    Posts
    428
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pasting in Data from File Path

    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!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Pasting in Data from File Path

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular
    Join Date
    Jul 2011
    Posts
    428
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting in Data from File Path

    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 by nirvehex; May 17th, 2019 at 09:33 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Pasting in Data from File Path

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Jul 2011
    Posts
    428
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting in Data from File Path

    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!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Pasting in Data from File Path

    It should be
    Code:
       If Fname = False Then Exit Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    Board Regular
    Join Date
    Jul 2011
    Posts
    428
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting in Data from File Path

    Thank you!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Pasting in Data from File Path

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Jul 2011
    Posts
    428
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting in Data from File Path

    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 by nirvehex; May 17th, 2019 at 10:17 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Pasting in Data from File Path

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •