E2010: get tab name in VBA (1 Viewer)

[bulrush]

I have Excel 2010 (v14.0.6023). I have a macro with VBA code tied to a hotkey. The VBA code is supposed to get the filename, and output the current spreadsheet tab to a tab-delimited text file.

Well, times have changed and now I have spreadsheets with multiple tabs. How do I use VBA to get the tab name so my tab-delimited text file uses the spreadsheet tab name, instead of the SS filename?

Thanks.Here is my current code:

Dim wb As Workbook
Dim s As String

s = Application.ActiveWorkbook.FullName
s = Replace(s, ".xlsx", ".dat")
s = Replace(s, ".xls", ".dat")
ActiveWorkbook.SaveAs Filename:=s, FileFormat:=xlText, CreateBackup:=False
MsgBox "Saved tab-delim file as " & s

p.s. Before you complain, I searched Excel help for "Application object" and only got info on the Format object. So no, Excel help is useless.

 

[boblarson]

You can get the currently selected tab name by using

ActiveSheet.Name

 

[bulrush]

Thanks for your help.