+ Reply to Thread
Results 1 to 9 of 9

Open specific folder, select file to open and copy then paste

Hybrid View

  1. #1
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Open specific folder, select file to open and copy then paste

    Hi Guys

    Been bangin head on wall with this one. I want to open explorer to a specific folder, select a file then copy data from a specific range and paste the data to a specific range in the current work book. Sounds easy

    I've tried heaps of code from the web but no winners. My vba is in its infancy so I might have been able to modify some if I had a bit more experiance - unfortunately I got cranky and dumped all the code I've tried so I can't show you what I've tried.

    any pointers in the right direction greatly appreciated.

    Regards
    Kranky

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Open specific folder, select file to open and copy then paste

    Kranky

    Can you post what you've tried so far?

    Did you try Application.GetOpenFilename?

    Dim wb As Workbook
    Dim strDrive As String
    Dim strFolder As String
    Dim strFilename As String
    
        strDrive = "C:"
    
        strFolder = "C:\ASpecificFolder"
    
        ChDrive strDrive
    
        ChDir strFolder
    
        strFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    
        If strFilename <> "False" Then
            Set wb = Workbooks.Open(strFilename)
    
            ' code to copy and paste to other workbook
    
            wb.Close
    
        End If
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Open specific folder, select file to open and copy then paste

    Thanks Norrie

    No I can't post what I've tried because I dumped it all in frustration. had tried many permutations of code off the net but it seems noone wants to do this specific task as i couldn't get a hit with it. One of them had Application.GetOpenFilename in there somewhere.

    Will give your code a run shortly.

    cheers
    Kranky

  4. #4
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Open specific folder, select file to open and copy then paste

    Bingo - worked a treat

    thank you Norie

  5. #5
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Open specific folder, select file to open and copy then paste

    Works really well Norie

    Sub CopyRC()
    Dim wb As Workbook
    Dim strDrive As String
    Dim strFolder As String
    Dim strFilename As String
    
        strDrive = "F:"
    
        strFolder = "F:\Test\Test"
    
        ChDrive strDrive
    
        ChDir strFolder
    
        strFilename = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    
        If strFilename = "False" Then Exit Sub ' user pressed Cancel
        
        If strFilename <> "False" Then
            
            Set wb = Workbooks.Open(strFilename)
    
            ' code to copy and paste to other workbook
            Range("B6:B27").Copy
            wb.Close SaveChanges:=False
        End If
        Range("D2").Select
        ActiveSheet.Paste
    End Sub
    changed the *.xls to *.xl* otherwise files didnt show. added the use pressed cancel out of the macro option (not sure if I had to).

    this all works very well for what is required.

    For the sake of the exercise would it be possible to select cells from the opened file rather than a specific reference?

    cheers
    Kranky

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Open specific folder, select file to open and copy then paste

    Kranky

    Not sure what you mean exactly.

    Do you want the user to be able to choose the cells, perhaps even the worksheet, to copy from the workbook that's been opened?

    PS If you are exiting the Sub if cancel is pressed, which is what I usually do, you don't need the second If.

  7. #7
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Open specific folder, select file to open and copy then paste

    Quote Originally Posted by Norie View Post
    Kranky

    Do you want the user to be able to choose the cells, perhaps even the worksheet, to copy from the workbook that's been opened?

    .
    Yep - just thought it would be useful to be able to do this. I dont have a specific use for it. Pretty silly really to do it through a macro when the file can just be opened and the cells selected etc

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Open specific folder, select file to open and copy then paste

    Perhaps this:
    Option Explicit
    
    Sub CopyRC()
    Dim wb As Workbook
    Dim strDrive As String
    Dim strFolder As String
    Dim strFilename As String
    Dim rngSrc As Range
    
        strDrive = "C:"
    
        'strFolder = "F:\Test\Test"
    
        ChDrive strDrive
    
        'ChDir strFolder
    
        strFilename = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    
        If strFilename = "False" Then Exit Sub    ' user pressed Cancel
    
        Set wb = Workbooks.Open(strFilename)
    
        ' code to copy and paste to other workbook
    
        Set rngSrc = Application.InputBox("Please select cells to copy:", Type:=8)
    
        If Not rngSrc Is Nothing Then rngSrc.Copy
    
        wb.Close SaveChanges:=False
    
        Range("D2").Select
        
        ActiveSheet.Paste
        
    End Sub

  9. #9
    Registered User
    Join Date
    09-24-2012
    Location
    Perth, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Open specific folder, select file to open and copy then paste

    Ha ha - thats perfect!!!

    Insert superlatives here

    Regards
    Kranky

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1