none
Copying data from a closed a file using part partial file path & part cell value in current workbook RRS feed

  • Question

  • Hi I've been tasked with streamlining some processes and for this one I thought I could use partial * file name and the * part be taken from a cell value in the current open work book.

    This will be my first VBA exercise other that manipulating macros.

    This is the macro I have and would like to take the underlined values  below from a cell in the open workbook. (or it will always be 1 less than the current workbook)

    (it would also be handy to know where to insert something to stop the pop ups )

    I've bee going threads for days trying not to admit defeat and do it myself, but I get so far until my head is in my hands)

    Would really appreciate any help

    Sheets("Schedule").Select
        Workbooks.Open(Filename:= _
            "S:\UK Farms Accounts\Management Accounts\2014-15\14-15 Pd03 Ma (000 All Co) UK Farm Summary v01.xls" _
            , UpdateLinks:=0, Notify:=False).RunAutoMacros Which:=xlAutoOpen
        Sheets("Schedule").Select
        ActiveWindow.SmallScroll ToRight:=12
        Range("AI58:AJ122").Select
        Selection.Copy
        Windows("14-15 Pd04 Ma (000 All Co) UK Farm Summary v02.xls").Activate
        Range("AA58").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("P&L Summary").Select
        Range("B7").Select
        Windows("14-15 Pd03 Ma (000 All Co) UK Farm Summary v01.xls").Activate
        ActiveWindow.Close
    End Sub

    Wednesday, September 17, 2014 2:09 PM

Answers

  • Fantastic!!

    I changed to 5,5 to the cell I wanted, but had to be in the same tab as the current tab not where it actually is as I don't know how to choose the tab.

    as the original code switched between workbooks which are named I had to close the copy from workbook so the mames became irrelevant and I only had one workbook open.

    This then wouldn't let me use my original paste special values and I had to use Unicode Text, but the result was the same.

    I also got rid of the pop up question from the clipboard by turning off display alerts at the beginning and turning on again at the end.

    All in all very chuffed thanks for your help Jim

    Application.DisplayAlerts = False
        Sheets("Schedule").Select
        Workbooks.Open(Filename:= _
              "S:\UK Farms Accounts\Management Accounts\2014-15\14-15 Pd" & _
               ThisWorkbook.ActiveSheet.Cells(1, 6).Value & _
              " Ma (000 All Co) UK Farm Summary v01.xls", UpdateLinks:=0, _
              Notify:=False).RunAutoMacros Which:=xlAutoOpen
              Application.ScreenUpdating = False
        Sheets("Schedule").Select
        Range("AI58:AJ122").Select
        Selection.Copy
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
        Range("AA58").Select
        ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
            DisplayAsIcon:=False, NoHTMLFormatting:=True
            Sheets("P&L YTD").Select
        Range("c7").Select
        Calculate

    • Marked as answer by Archie Andrews Monday, September 22, 2014 9:14 AM
    Thursday, September 18, 2014 10:03 AM

All replies

  • Re:  streamlining

    There is no underlined code in your message.
    I have had no luck in trying to do that on my messages.
    Same with trying to bold font.
    '---
    Jim Cone
    Wednesday, September 17, 2014 7:27 PM
  • Thanks Jim, looked underlined to me on my page.

    It's the 03 part of the file name if any one else was can't see it.

    S:\UK Farms Accounts\Management Accounts\2014-15\14-15 Pd03 Ma (000 All Co) UK Farm Summary

    Thanks again Jim for letting me know.

    Wednesday, September 17, 2014 7:59 PM
  • Re:  get a value from a worksheet...

    Give this a try, after entering the correct row/column numbers in "Cells(5, 5)"...

    Workbooks.Open(Filename:= _
             "S:\UK Farms Accounts\Management Accounts\2014-15\14-15 Pd" & _
              ThisWorkbook.ActiveSheet.Cells(5, 5).Value & _
             " Ma (000 All Co) UK Farm Summary v01.xls", UpdateLinks:=0, _
             Notify:=False).RunAutoMacros Which:=xlAutoOpen
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2
     (Dropbox)

    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Thursday, October 20, 2016 3:15 PM
    Wednesday, September 17, 2014 10:23 PM
  • Fantastic!!

    I changed to 5,5 to the cell I wanted, but had to be in the same tab as the current tab not where it actually is as I don't know how to choose the tab.

    as the original code switched between workbooks which are named I had to close the copy from workbook so the mames became irrelevant and I only had one workbook open.

    This then wouldn't let me use my original paste special values and I had to use Unicode Text, but the result was the same.

    I also got rid of the pop up question from the clipboard by turning off display alerts at the beginning and turning on again at the end.

    All in all very chuffed thanks for your help Jim

    Application.DisplayAlerts = False
        Sheets("Schedule").Select
        Workbooks.Open(Filename:= _
              "S:\UK Farms Accounts\Management Accounts\2014-15\14-15 Pd" & _
               ThisWorkbook.ActiveSheet.Cells(1, 6).Value & _
              " Ma (000 All Co) UK Farm Summary v01.xls", UpdateLinks:=0, _
              Notify:=False).RunAutoMacros Which:=xlAutoOpen
              Application.ScreenUpdating = False
        Sheets("Schedule").Select
        Range("AI58:AJ122").Select
        Selection.Copy
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
        Range("AA58").Select
        ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
            DisplayAsIcon:=False, NoHTMLFormatting:=True
            Sheets("P&L YTD").Select
        Range("c7").Select
        Calculate

    • Marked as answer by Archie Andrews Monday, September 22, 2014 9:14 AM
    Thursday, September 18, 2014 10:03 AM