How to extract worksheet name in VBA?

Question

I have a script that rips some data for me from a certain tab in multiple excel workbooks.

I am wondering how can I add to that to extract the workbook name for each workbook it goes through

This is what I am using:

Dim fPath As String
Dim iSheet As String
Dim oSheet As String
Dim BMsheet As String

Dim country, bas, play As String
Dim fileNames As Range
Dim file As Range
Dim oWorkbook As Excel.Workbook ' outlook workbook

Dim MyRange As Range

iSheet = "INPUT"
oSheet = "Data"
BMsheet = "Potential Discovery Phasing"

Dim fHandle As New FileSystemObject

ThisWorkbook.Worksheets(iSheet).Activate
Set fileNames = Range("files")
ThisWorkbook.Worksheets(oSheet).Activate
Range("start").Activate

On Error GoTo NotFound:

For Each file In fileNames.Cells
    If fHandle.FileExists(file.Value) Then
        Set oWorkbook = Workbooks.Open(file.Value, False, True)
        'extract columns

        ''''''''''''''''''''''''''''''''''''''''''''''''
        '
        ''''''''''''''''''''''''''''''''''''''''''''''''
        oWorkbook.Worksheets(BMsheet).Select

        If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

        Range("A6").Select

        ActiveCell.Offset(1, 0).Select
        Set MyRange = Range(ActiveCell, ActiveCell.Offset(32, 7))

        MyRange.Select

        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy

        ThisWorkbook.Activate
        ThisWorkbook.Worksheets(oSheet).Select
        Selection.PasteSpecial xlValues

       'While ActiveCell.Value <> ""
             'ActiveCell.Offset(0, -1).Value = file.Offset(0, -2).Value
             ActiveCell.Offset(33, 0).Activate
       'Wend

        Application.CutCopyMode = False
        oWorkbook.Close SaveChanges:=False

        ActiveCell.Select
        file.Offset(0, 1).Value = "Yes"
    Else
        file.Offset(0, 1).Value = "No"
    End If

Skip: Next file

    Exit Sub

NotFound:
    GoTo Skip
End Sub

I am fairly new to VBA so excuse my lack of knowledge

Cheers

I haven't looked at your code, but if you have a range you can find the name of the worksheet it is in by using `Range.Parent.Name` — Tom, Sep 20 '17 at 16:06
Also, I ***highly*** recommend reading through, and applying, [How to Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Sep 20 '17 at 16:15

Doug Coats · Answer 1 · 2017-09-20T20:49:55.807

1

dim sheet as worksheet
dim wb as workbook
set wb = thisworkbook

for each sheet in wb.Sheets
    debug.print; sheet.Name
next sheet

Because we're nerds and want to check things:

Private Sub this()
    For i = 0 To 99
        Debug.Print i;
        'prints to the same line
    Next i

    For i = 0 To 99
        Debug.Print ; i
        prints to next line
    Next i
End Sub

edited Sep 20 '17 at 20:49

answered Sep 20 '17 at 16:17

Doug Coats

6,255
9
27
49

1

What is the significance of the semicolon? I havent ever seen that style of syntax used before. – Brandon Barney Sep 20 '17 at 16:21
1

@BrandonBarney It's standard syntax? I didnt know there was a different way of doing it? I know that strings go before the semi colon in quotes and variables afterwards – Doug Coats Sep 20 '17 at 16:23
1

I guess you could combine variables in the quotes too – Doug Coats Sep 20 '17 at 16:23
1

@BrandonBarney Not sure if there's any point in putting just after `Debug.Print` but it suppresses the new line. Putting it at the end would therefore print all results on one line. – Tom Sep 20 '17 at 16:26
I basically was following the documentation https://msdn.microsoft.com/en-us/library/aa716276(v=vs.60).aspx – Doug Coats Sep 20 '17 at 16:28
@DougCoats That is really strange. I have never seen the semicolon before, and it is odd that it would be the standard in the documentation. I always do something like `Debug.Print Foo` or `Debug.Print "Foo = " & Foo`. They are functionally equivalent to the semicolon as far as I can tell. I wonder if the semicolon is deprecated...strange. – Brandon Barney Sep 20 '17 at 16:34
3

@DougCoats With the help of the RD team I understand it better now. It has different functionality than `Debug.Print Foo` since it, in a way, has a contextual behavior. It behaves like `Console.Write` from C#. Depending on the application it can be quite useful. Interesting to know :). – Brandon Barney Sep 20 '17 at 16:42
Neat. Now I also understand it better :D Thanks – Doug Coats Sep 20 '17 at 16:43
Pretty sure a semicolon is redundant *before* what's being printed. The semicolon basically says "don't print an implicit newline", so the *next* `Debug.Print` call (or whatever follows the semicolon in the same instruction) will be output on the same line. Hard to put in words, but yeah the best I could come up with is `Console.Write` (terminating the statement with a semicolon) vs `Console.WriteLine` (without the semicolon). So `?"foo";"bar"` is the same as `?"foo"&"bar"` - also, using a comma to separate printed values is the same as concatenating with `vbTab` in-between the two values. – Mathieu Guindon Sep 20 '17 at 16:58
@Mat'sMug I coincidentally enough just had an opportunity to test what youre saying. I had to update about 40 records in sql server and I used "debug.print; vbSql" to output the sql statement. Each output was on its own line. So I out of curiousity ran a loop (see edited code above)and tested both options. If variable i is before semi colon its same line, if its after its nect line – Doug Coats Sep 20 '17 at 20:48
So I can see where they have their uses and honestly i think id still prefer after semi colon especially since I would want to mix up my outputs – Doug Coats Sep 20 '17 at 20:52
That's what I'm saying: `Debug.Print i` and `Debug.Print ; i` (with or without the whitespace before it) both produce the same output, i.e. the semicolon is redundant when it's before the output list =) – Mathieu Guindon Sep 20 '17 at 20:55
@Mat'sMug Ah I guess I must be stupid LOL I totally looked over the qualifying part of your statement. Though I do find this to be an interesting distinction. – Doug Coats Sep 20 '17 at 20:58

How to extract worksheet name in VBA?

1 Answers1