CODEWITHSUNDEEP
vbaexcel
shanxiang shen
shanxiang shen

Reputation: 31

change worksheet name, a difficult one

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!

Upvotes: 2

Views: 820

Answers (3)

Robert Co
Robert Co

Reputation: 1715

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

Upvotes: 0

Toby Allen
Toby Allen

Reputation: 11221

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

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149335

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"

Upvotes: 8

Related Questions