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

 

[ds2020]

Thanks I didn't know it's limited to 31 characters. the part after unknown road is actually what I need if i can't do anything with the code I may need to change the file names but there are more than.

 

[Fluff]

Do you just want the last 15 characters of the workbook & are they all csv files?

 

[ds2020]

Yes and yes.

 

[Fluff]

Ok how about

Do While Filename <> ""
   Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
   Sheets(1).Copy after:=ThisWorkbook.Sheets(1)
   ThisWorkbook.Sheets(2).Name = Left(Right(Filename, 19), 15)
   Workbooks(Filename).Close
   Filename = Dir()
Loop

 

[ds2020]

Ok how about

Do While Filename <> ""
   Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
   Sheets(1).Copy after:=ThisWorkbook.Sheets(1)
   ThisWorkbook.Sheets(2).Name = Left(Right(Filename, 19), 15)
   Workbooks(Filename).Close
   Filename = Dir()
Loop

Yes, it works perfectly. Thank you so much for your help. I've been playing with it for two days to figure something out.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ds2020]

You're welcome & thanks for the feedback.

Can I ask another question? I'm trying to refer a cell from a sheet (sheet1) to another cell in the first sheet but without mentioning the first sheets name. whatever I can't get the cell value from first sheet to the new one.

    Sheets("Sheet1").Select
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=sheets(1)!RC"
    Range("H3").Select

I tried this, sheet1, and some other values for ActiveCell.FormulaR1C1 = but it doesn't give me the value

 

[Fluff]

How about

Range("H2").FormulaR1C1 = "='" & Sheets(1).Name & "'!rc"

 

[ds2020]

How about

Range("H2").FormulaR1C1 = "='" & Sheets(1).Name & "'!rc"

Thanks, it works

 

[Fluff]

Glad to help & thanks for the feedback.