VBA to pull data from closed excel workbooks to Master within sub folder structures

[JaLoXL]

Hi,

I have been searching for a solution to this issue for quite some time with no luck, some findings get me a portion of the way but not quite what I need. I do not have any VBA experience but I am very competent with formula writing.

I need to pull data ranges from closed, excel sheets stored on a shared drive (F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS), these documents are stored firstly via Month January/February/March etc then Week Commencing 01.01.19/08.01.19/15.01.19. Within each of the Week Commencing folders there are a number of Spreadhseets but I need to look inside only one - 'Labour Test File'. Within this workbook I need to extract the range B3:AA42 from the worksheet named 'Analysis - Costs'.

When this data has been found I would want this to paste into a document stored on the same file path named 'Master', using the next available to row to paste the next week's data.

Your help would be greatly appreciated on this as I have been struggling to find a suitable solution for this for some time.

 

[Yongle]

Hi

To confirm that I have understood everything correctly here is a quick test
- place VBA below in a standard module in NEW workbook and run it
- values will appear in the sheet if the path is correct
- the generated file path is printed (for confirmation) to cell A1 and will be this:
F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS\January\08.01.19\Labour Test File.xlsx

Let me know if anything requires amendment to make this test work and then we'll develop it further to do everything you need
(I will have a few questions for you)

Sub GetValues()
    Dim fMonth As String, fWkComm As String, varPath As String, s As String, fullPath As String
    Dim ws As Worksheet, wb As Workbook
    Const fPath = "F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS"
    Const fName = "Labour Test File.xlsx"
    Const fSheet = "Analysis - Costs"
    Const fRng = "B3:AA42"
[COLOR=#006400][I]'variable elements[/I][/COLOR]
    fMonth = "January"
    fWkComm = "08.01.19"
    s = Application.PathSeparator
[COLOR=#006400][I]'full path string[/I][/COLOR]
    fullPath = fPath & s & fMonth & s & fWkComm & s & fName
[I][COLOR=#006400]'test if file exists[/COLOR][/I]
    If Dir(fullPath) > "" Then
        Set wb = Workbooks.Open(fullPath)
    Else
        MsgBox "File not found"
        Exit Sub
    End If
[I][COLOR=#006400]'get values[/COLOR][/I]
    set ws = ThisWorkbook.Sheets(1)
    ws.Cells(1, 1) = fullPath
    wb.Sheets(1).Range(fRng).Copy ws.Cells(2, 2)
    wb.Close False[I][COLOR=#006400] 'close without saving[/COLOR][/I]
    
End Sub

 

[JaLoXL]

Hi Yongle,

Thanks for helping.

The above code when run managed to get to the correct document but extracted the information from the wrong worksheet?

I have ensured that the spelling of the tab is correct but it extracted information from a worksheet named 'Update Guide' rather than 'Analysis - Costs'

Again I really appreciate the help with this.

 

[Yongle]

My fault (forgot to amend after local test) - amend this line

wb.Sheets([COLOR=#ff0000]fSheet[/COLOR]).Range(fRng).Copy ws.Cells(2, 2)

 

[Yongle]

Looks like I have understood everything

Another test for you
- you may be able to pull the values without opening workbooks

Add a new sheeet in the test workbook
- paste formula below into cell B3 and drag acoss to AA and down to row 42

='F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS\January\08.01.19\[Labour Test File.xlsx]Analysis - Costs'!B3


Formatting may be incorrect, but do the values appear?

 

[Yongle]

paste into a document stored on the same file path named 'Master', using the next available to row to paste the next week's data

.. a few questions

Was test in post#5 successful ?

Does Master contain only ONE sheet ?
- if not, what is the name of the sheet where data is to be pasted ?

Is the pasted data being pasted into the next available cell in column B ?
- if not, where ?

Is the week to be selected via dropdown by user OR are you expecting VBA to auto-update the next available week?
- is week commencing date in any one of columns B:AA ?

Where will VBA reside ?
- in Master ?
- in another workbook ? (its name is not required)

 

[JaLoXL]

.. a few questions

Was test in post#5 successful ?

Does Master contain only ONE sheet ?
- if not, what is the name of the sheet where data is to be pasted ?

There will be 2 sheets - Named 'Sheet 1' & 'Sheet 2', I would like it in sheet 1 if possible.

Is the pasted data being pasted into the next available cell in column B ?
- if not, where ?

If we could paste the next weeks data underneath the previous week, so the next available row.

Is the week to be selected via dropdown by user OR are you expecting VBA to auto-update the next available week?
- is week commencing date in any one of columns B:AA ?

No Week Commencing date is just the name of the folder that contains the 'Labour Test File' workbook

Where will VBA reside ?
- in Master ? Yes
- in another workbook ? (its name is not required)

No, master only.

 

[JaLoXL]

This worked perfectly!!!

 

[JaLoXL]

My fault (forgot to amend after local test) - amend this line

wb.Sheets([COLOR=#ff0000]fSheet[/COLOR]).Range(fRng).Copy ws.Cells(2, 2)

Apologies, to be more specific this worked perfectly.

 

[JaLoXL]

Looks like I have understood everything

Another test for you
- you may be able to pull the values without opening workbooks

Add a new sheeet in the test workbook
- paste formula below into cell B3 and drag acoss to AA and down to row 42

='F:\STRUCTURES\ACCOUNTS\Cost Tracking\BUDGETS\COSTINGS\EMPLOYEE ANALYSIS\January\08.01.19\[Labour Test File.xlsx]Analysis - Costs'!B3


Formatting may be incorrect, but do the values appear?

I have thought about building the sheets with formulas this way but as the will be Month folders not yet created I would like the VBA code to look for all workbooks created with the name 'Labour Test File' and pull down the information contained within 'Analysis - Costs'.