change worksheet name, a difficult one

Question

When I use excel to open a .txt file (a notepad file), the worksheet name is the file name of the notepad file that was opened by default. Therefore, the sheet name will be different when open a different notepad file. Downstream code need this worksheet name be a fixed one. Is there anyway to make change the sheet name to a fixed name such as "sheet1". By the way, codename can not be used, since the macro to use the data in the open file is not another workbook.

Thanks!

Answer 1

You don't need the codename not the worksheet name when you are opening .txt files from Excel. There will always be 1 sheet. So in your code you can always address that sheet as

wb.Sheets(1)

Where wb is the workbook object.

For your reference every .txt file that you open with VBA cannot have a common name unless you set it via code. And if you do that, you will have to still use wb.Sheets(1)

For example

wb.Sheets(1).Name = "Blah Blah"

Answer 2

Could you call your text file sheet1.txt? Would that solve your problem?