change worksheet name, a difficult one

By | February 27, 2018
Questions:

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!

Answers:

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"

Questions:
Answers:

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

Questions:
Answers:

I am picturing that your macro opens the text file dynamically because you want to use excel’s built in csv parsing. Perhaps sorting and filtering the data afterwards.

Siddarth gave you a good lead, but you shouldn’t worry about the name or the sheet because as he said you have the worksheet object to use for your downstream code.

wb.Sheets(1)

Now, if you want to reference this sheet outside of the subroutine that you opened the file. Use a global variable for your

wb

Leave a Reply

Your email address will not be published. Required fields are marked *