How do I get the sheet name?

[Jeff Armstrong]

Hello,

I am importing specific information from CSV files and I
want to get the sheet name for each file that I open so I
can use it as a heading in the workbook that I am
compiling the information in. How do I do this?

Thanks,
Jeff

 

[Dave Peterson]

You using workbooks.open to open the .csv file?

If yes, you can get the name with something like:

Option Explicit
Sub testme01()
Dim wksName As String
Workbooks.Open Filename:="C:\My Documents\excel\book1.csv"
wksName = ActiveSheet.Name
msgbox wksName
End Sub

But you may just want to use a variable to represent that worksheet. Then you
can do things like:
wks.range("a1").value = "xxxx"
instead of
worksheets(wksName).range("a1").value = "xxxx"

(and you wouldn't have to worry about qualifying the worksheets, either.
That'll come along with the wks variable:
msgbox wks.parent.name

Sub testme02()
Dim wks As Worksheet
Workbooks.Open Filename:="C:\My Documents\excel\book1.csv"
Set wks = ActiveSheet
MsgBox wks.Name
End Sub