Open most up to date file on sharedrive and copy/paste into workbook

Question

I have been able to get some code to open the most up to date file located on a share drive. The part of code that i'm really struggling with is the last part which tries to copy and paste the contents of that file into my master - i tried recording this last part and alter that code but have had no luck - i feel like im on the right track but would appreciate any pointers! the specific error is "Run-time error '1004'"

Sub GetLatestFile()

Dim strFolder  As String
Dim strFile    As String
Dim latestFile As String
Dim dtLast     As Date

'   assign variables
    strFolder = "Z:\PRICING1\1Mbs Pricing1\MBSREVAL11\21016111\" 'The end of this path must have a \ on it
    strFile = Dir(strFolder & "\*.*", vbNormal)    ' Any File
'   strFile = Dir(strFolder & "\*.xls*", vbNormal) ' Excel Files
'   strFile = Dir(strFolder & "\*.csv", vbNormal)  ' CSV Files

'   loop through files to find latest modified date
    Do While strFile <> ""
        If FileDateTime(strFolder & strFile) > dtLast Then
            dtLast = FileDateTime(strFolder & strFile)
            latestFile = strFolder & strFile
        End If
        strFile = Dir
    Loop

    MsgBox latestFile

    Workbooks.Open (latestFile)

    Worksheets("Ratesheet").Activate
    Range(A7).Select
    Selection.copy
    Windows("RMBS Pricing_New v5 (version 1) [Autosaved]").Activate
    Range("A7").Select
    ActiveSheet.Paste
    Windows(latestFile).Activate

End Sub

@TimothyKanski thanks for the quick reply, but it still has an error "Run-time error 1004" — Samuel Leak, Apr 27 '16 at 23:35
*I've had no luck* is not a useful problem description. What **specific problem** do you have with the code you've posted? — Ken White, Apr 28 '16 at 01:03
@KenWhite sorry - in short the code opens the file with no worries which is up to the line "Workbooks.open (latestFile)" - the problem occurs when i try and copy the data within Range("A7:V2000") and paste in the current workbook in which im writing the code - if you have any suggestions or more efficient solutions to select the range and copy / paste into the workbook the code is written that would be extremely helpful - cheers — Samuel Leak, Apr 28 '16 at 01:17
You've still not explained what *the problem* is, which is exactly the problem I pointed out in my last comment. What **specific** *problem occurs*??? What is the **specific issue you're having when you reach that line of code**? — Ken White, Apr 28 '16 at 01:25
@kenWhite im not sure - i get a "Run-Time error '1004'" im not sure how else to explain this...after the file opens my code tries to select the range and for some reason it cant...and i get an application-edfined or object-defined error....could it be something too do with the file being in read only ? — Samuel Leak, Apr 28 '16 at 01:35
Where in your question does it mention a *Run-time error "1004"*? That's 'the **specific problem** I've asked you twice before to include in your question. Now please [edit] your question to include that information **there**, where it should have been in the first place. You had that information when you posted your question, and there are zero valid reasons for you not to have included it when you posted. — Ken White, Apr 28 '16 at 01:39
@kenwhite i mentioned this error in the first reply of this problem to TimothyKanski - but i will add to the question — Samuel Leak, Apr 28 '16 at 01:41
Guess I missed it, because it was in the comments and **not in the question where it belonged**. You're asking us for help to solve **your** problem; when you have information that is highly relevant to the question, put it in the question in the first place. We shouldn't have to play 20 questions to get you to provide the important details you should have provided already. — Ken White, Apr 28 '16 at 01:44

score 0 · Answer 1 · answered Apr 28 '16 at 00:27

0

Assuming that RMBS ... is the master file:

Workbooks.Open(latestFile)
Worksheets("Ratesheet").Activate
Range("A7").Select
Selection.copy
' ActiveWorkbook.Close if needed

Workbooks.Open("RMBS Pricing_New v5 (version 1) [Autosaved]")
Range("A7").Select
Selection.Paste
' Save/close as needed

answered Apr 28 '16 at 00:27

yk11

768
4
9

i still seem to be having an issue with this - thanks a lot for you help, the issue arises when i try selecting the data from first workbook that is opened, is there an easier code i can use to select copy and paste the data ? also i want to paste it into the book in which im currently writing the VBA so the file name "RMBS.." is probs irrelevant and i can use ActiveWorkbook.open – Samuel Leak Apr 28 '16 at 01:03
You can use `courceRange.Copy targetRange`. This syntax allows you to copy/paste in a single operation. You should also look into `PasteSpecial` if you need to control what is copied (formats, values, etc.). – yk11 Apr 28 '16 at 14:41

score 0 · Answer 2 · edited May 23 '17 at 12:31

This is the simplest way to achieve what you want. Avoid the use of .Select/.Activate. You may want to see THIS

My Assumptions:

The file that you are trying to open can be opened in Excel
You are running the code from RMBS Pricing_New v5 (version 1). If not then declare another workbook object and assign this workbook to that object.

Is this what you are trying? (Untested)

Sub GetLatestFile()
    Dim strFolder As String, strFile As String, latestFile As String
    Dim dtLast As Date

    Dim wbThis As Workbook, wbThat As Workbook
    Dim wsThis As Worksheet, wsThat As Worksheet

    Set wbThis = ThisWorkbook
    '<~~ Change this to the relevant sheetname where you want to paste
    Set wsThis = wbThis.Sheets("Sheet1")

    '
    '~~> Your code to find the latest file
    '

    Set wbThat = Workbooks.Open(latestFile)
    Set wsThat = wbThat.Sheets("Ratesheet")

    wsThat.Range(A7).Copy wsThis.Range("A7")
End Sub

Open most up to date file on sharedrive and copy/paste into workbook

2 Answers2