Filename = Dir(Path) is not giving me the full name

[ds2020]

this is part of my code to open multiple workbooks and put them in sheets in one workbook. However, the name (path) is not the full path (it's missing the important part of the names). What can I do to get the full name of every sheet from this code?



Path = "D:\uni\Test Data\197\New folder\"
Filename = Dir(Path)
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop

Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Worksheets
xsheet.Select

 

[Fluff]

How about

 Filename = Dir(Path & "*.xls*")

 

[ds2020]

How about

 Filename = Dir(Path & "*.xls*")

no, doesn't work

 

[Fluff]

In what way?
Do you get any errors, do the files open, does nothing open?

 

[ds2020]

In what way?
Do you get any errors, do the files open, does nothing open?

No it is still the same problem.

 

[Fluff]

What problem?

 

[ds2020]

What problem?

The name of each workbook is not the full name

It's missing a part the name after Unknown road

 

[Fluff]

What has a formula in a cell, got to do with your original question?
As yet you have not said what exactly is wrong.
Do you get any errors?
Do the workbooks get opened?
Do all the sheets in each workbook get copied?
WHAT? Please be specific.

 

[ds2020]

Everything Works. So this code is supposed to open multiple workbooks and copy each of them in a separate sheet in another workbook. But when it does that, the name of each workbook (now worksheet) is shorter. So the problem is the filename, it doesn't appear in full and I don't.

 

[Fluff]

Ok, I got you. There is nothing wrong with the code, or what it's doing, it's just that sheet names are limited to 31 characters. So you will need to get rid of part of the name.
How do you want it named?
Also are those csv files your opening?