How to shorten file name which is used to name the sheet name in a master file using VBA?

[Sheila003]

Hi,

I am not used to Macro, however I would like to automate a process which I found a formula but need it be tweaked so I need some help please.

I am basically trying to copy a specific excel sheet in files that are kept in a folder and name the sheet in the master workbook the name of the file. Below is the the VBA code

It is unable to copy due to the length of the file name being more than 31. How do I shortened the file name on ActiveSheet name please?

Sub MergeWorkbooks()

Dim FolderPath As String
Dim File As String


FolderPath = "C:\Users\x\yyyy\Documents\Testing\Test\"

File = Dir(FolderPath)

Do While File <> ""

Workbooks.Open FolderPath & File
ActiveWorkbook.Worksheets(2).Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = Replace(File, ".xlsx", "")
Workbooks(File).Close

File = Dir()

Loop


End Sub

 

[mumps]

Try:

ActiveSheet.Name = Left(Replace(File, ".xlsx", ""), 31)

 

[Sheila003]

Try:

ActiveSheet.Name = Left(Replace(File, ".xlsx", ""), 31)

Thank you for your quick response, however it came with Compile error : Syntax error.
Much appreciated

 

[Sheila003]

Thank you for your quick response, however it came with Compile error : Syntax error.
Much appreciated

Actually, please ignore my previous message. I missed a comma.

It works perfectly. Thank you so much

Sheila

 

[mumps]

You are very welcome.