Extracting data from all files in the same folder

[Corleone]

Hi
Im trying to put together some code that will open up all of the other files in the same folder as the one im in
and then select all the data from cell A7 down to wherever the data finishes in column H in all of the files one by one and paste it into my blank spreadsheet from which the macro will be running from.


Im currently getting the following message "File not found" on the following line
SourceFile = Dir(SourcePath & "*.xlsm")

I can confirm that all of the file extensions in the directory are .xlsm

Below is the code

Sub CopyDataFromOtherFiles()
Dim SourcePath As String
Dim SourceFile As String
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim LastRow As Long
Dim TargetRow As Long
Dim TargetSheet As Worksheet

' Set the source path to the current directory
SourcePath = ThisWorkbook.Path & "C:\Users\shegarty\OneDrive - Network Rail\Profile\Desktop\Latest Analytical Tools - Copy\Period 05 (P04 Contractor Programmes)"

' Set the target worksheet in your current workbook
Set TargetSheet = ThisWorkbook.Sheets(2)

' Initialize the target row to the next available row after existing data
TargetRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Row + 1

' Loop through all files in the source directory
SourceFile = Dir(SourcePath & "*.xlsm")

Do While SourceFile <> ""
If SourceFile <> ThisWorkbook.Name Then ' Exclude the current workbook
' Open the source workbook
Set SourceWorkbook = Workbooks.Open(SourcePath & SourceFile)

' Set the source sheet (tab)
Set SourceSheet = SourceWorkbook.Sheets(2)

' Find the last row with data in columns A to H on the source sheet
LastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "A").End(xlUp).Row

' Copy data from source to target
SourceSheet.Range("A2:H" & LastRow).Copy TargetSheet.Cells(TargetRow, 1)

' Close the source workbook without saving changes
SourceWorkbook.Close False

' Update the target row for the next file
TargetRow = TargetRow + (LastRow - 1)
End If

' Move to the next file
SourceFile = Dir
Loop
End Sub




thanks

 

[Corleone]

Apologies - im having a mare - i have now copied in that slash now

This is the directory that is coming up - I manually typed it out not realising i could do a screen shot

These are the files in the folder as you can see they are all .xlsm
im just doing it in a test area at the moment - when its set up properly there will be around 30 different files

Im logging off for a bit now -thanks for all your help

 

[Joe4]

Are you getting to that folder using the URL being returned in the thread, or are you manually browsing to it?

So, if you open up a browser window, and put the following in the URL line at the top:

https://networkrail-my.sharepoint.com/personal/shegarty_networkrail_co_uk/Documents/Profile/Desktop/Latest Analytical Tools - Copy/ Period 05 (P04 Contractor Programmes)/

does it open the folder and show all those files?

If not, then you may not be able to navigate to the folder that way.

If you can see the files when you do it that way, I have no idea why it would not be working.