[RESOLVED] Excel VBA: access sheets through variable name

[pgag45]

I am currently trying to open and save individual sheets in a workbook with the following code:

Dim sFileName As String
'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
     
Workbooks.Open fileName:=sFileName
Sheets("EVENTS").Copy After:=Workbooks("NLeapGIS10.xls").Sheets(ThisWorkbook.Sheets.Count)
Workbooks("EVENTS.xls").Close

However, this function would only work for a sheet named EVENTS. I want to use something like

Sheets(sFileName) , but this is not legal. Any help, or am I just stuck?

[VBAhack]

However, this function would only work for a sheet named EVENTS. I want to use something like

Sheets(sFileName) , but this is not legal. Any help, or am I just stuck?

You need to distinguish between workbook (file) names and sheet names. Looks like you are trying to use a workbook name (with .xls extension) as a sheet name, which, as you found out, is invalid. It should be OK to have sheet names as variables:

Sub test()
    Dim s1 As String, s2 As String
    s1 = "Sheet1"
    s2 = "Sheet2"
    Sheets(s1).Range("A1") = Sheets(s2).Range("A1")
End Sub

[pgag45]

Ok thanks man. Guess I wasn't getting the difference between sheets, worksheets, and workbooks. (Still don't know the difference b/w sheets and worksheets)

But is it possible to use

Workbooks(variableName & ".xls").Close

or something similar to that ... b/c the above line is causing me problems.

thanks again

[Lord Orwell]

i have had problems with excel closing sheets like that. i found out it is inconsistent unless you have close code in a module. I dont know if this applies in your case though...

[pgag45]

Yeah I'm unable to figure this out. I can open workbooks with a variable name, but can't close them with that same variable name????

and which module would the code go in? My workbook that is always open or the one I am opening momentarily.

[VBAhack]

Try Workbooks(variableName).Close without the .xls extension. I did a little experiment by opening 2 new workbooks (Book1.xls and Book2.xls). The following code worked fine:

Workbooks("Book2").Close

Re your other question about the difference between Sheets and Worksheets, just use VBA help. It seems Sheets include charts sheets as well as worksheets, whereas worksheets are just worksheets. In other words, worksheets are a subset of sheets.

"The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets)"

[pgag45]

Workbooks(sFileName).Close

and

Workbooks.close Filename:sFileName

doesn't work either... =(

[VBAhack]

What is the content of sFileName? Use debug.print to find out. If it contains .xls, try stripping it out. If not, then I'm stumped. My test example worked fine.

[pgag45]

Yeah it does contain .xls, I'll splice it and test it... thanks

[pgag45]

Nope... ughhhhhhhhhh

splicing the .xls didn't work either... still crashing on close with subscript out of range error

Dim sFileName As String
'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
MsgBox (sFileName)
'They have cancelled.
If sFileName = "False" Then Exit Sub
Workbooks.Open fileName:=sFileName
Sheets("EVENTS").Copy After:=Workbooks("NLeapGIS10.xls").Sheets(ThisWorkbook.Sheets.Count)
Dim stringLength As Integer
stringLength = Len(sFileName)
sFileName = Mid(sFileName, 1, stringLength-4)
MsgBox (sFileName)
Workbooks(sFileName).Close

[VBAhack]

You need to strip off the path info that is in sFileName.

[pgag45]

haha yeah just figured that out, almost done doing it. I'll post it after it (hopefully) works

[pgag45]

ahh finally, thanks for the help hack. appreciate it much.

Dim sFileName As String
'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
MsgBox (sFileName)
'They have cancelled.
If sFileName = "False" Then Exit Sub
Workbooks.Open fileName:=sFileName
Sheets("EVENTS").Copy After:=Workbooks("NLeapGIS10.xls").Sheets(ThisWorkbook.Sheets.Count)
Dim temp As String
temp = spliceFileNameEnd2(sFileName)
MsgBox (temp)
Workbooks(temp).Close SaveChanges:=False

Function spliceFileNameEnd2(fileName As String) As String

Dim x As Integer
Dim stringLength As Integer
Dim lastSlash As Integer
Dim temp As String

stringLength = Len(fileName)

For x = 1 To stringLength
    temp = Mid(fileName, x, 1)
    If temp = "\" Then
        lastSlash = x
    End If
Next x
        
spliceFileNameEnd2 = Mid(fileName, lastSlash + 1, stringLength)

    
End Function

[Lord Orwell]

just a small side note. depending on your excel version, try looking up help on the Instrrev command. it could save you extra coding in the future

[kewakl]

just a small side note. depending on your excel version, try looking up help on the Instrrev command. it could save you extra coding in the future

That would be fine unless you are running on a hand-me-down-corporate-doorstop-pentiumIII like I am!

See Post 7


just my $0.42!

[Ellis Dee]

That would be fine unless you are running on a hand-me-down-corporate-doorstop-pentiumIII like I am!

See Post 7

Careful with that. While it is true that InStrRev() is very slow, it'll still execute way faster than this:

stringLength = Len(fileName)
 
For x = 1 To stringLength
    temp = Mid(fileName, x, 1)
    If temp = "\" Then
        lastSlash = x
    End If
Next x

The big bottleneck in this implementation is the Mid() call. If you change it to Mid$(), it'll be dramatically faster, but still slower than InStrRev(). The other issue is that since you're searching for the last instance, you should start at the end, not the beginning. (Example later.) To get faster than InStrRev() you need to use InStr():

temp = InStr(filename, "\")
Do While temp <> 0
    lastSlash = temp
    temp = InStr(temp + 1, filename, "\")
Loop

This solution will end up twice as fast as InStrRev() on average, several times faster than iterating each character with Mid$(), and up to ten times faster than iterating each character with Mid().

When it comes to iterating characters, Mid() is not a good way to go. Instead, convert the string to a byte array. This example would rival the speed of InStrRev(), if not be outright faster: (You'll never beat the lightning-fast InStr() in the general case, but because this starts looking at the end, it may actually be faster in this specific case):

Dim bytArray() As Byte
 
bytArray = StrConv(filename, vbFromUnicode)
For x = Ubound(bytArray) To 0 Step -1
    If bytArray(x) = 92 Then ' Asc("\") = 92
        temp = x + 1
        Exit For
    End If
Next
Erase bytArray

Note that if you end up using byte arrays to speed up string manipulation that modifies the string, you can easily convert the modified byte array back to a string with another StrConv() call like this:

strString = StrConv(bytArray, vbUnicode)

[Ellis Dee]

I threw together a benchmark program to see exactly how fast each approach I described above executes. To simulate a real-world test, it searches for the last "\" in the string:

"C:\Documents & Settings\User\Application Data\Company\Application\FileName.ext"

It executes each search method on this string 50,000 times. Surprisingly, InStrRev() is the fastest method. The results in order of fastest to slowest:

0.06055 seconds: InStrRev()
0.08984 seconds: InStr()
0.33105 seconds: Byte array
0.42090 seconds: Mid$() in reverse order
2.90332 seconds: Mid$()
3.22461 seconds: Mid() (the original code)

Amazing how much faster the Mid$() approach is when starting from the end instead of the beginning.

Here's the complete benchmark program; copy this into a bas module and compile to run from exe for the most accurate results. (If you do, you'll see just how slow my computer is when you compare your results to mine.)

Option Explicit

Sub Main()
    Const iMax = 50000
    Dim strFileName As String
    Dim strChar As String
    Dim lngLastSlash As Long
    Dim strMessage As String
    Dim bytArray() As Byte
    Dim sngStart As Single
    Dim i As Long
    Dim ii As Long
    
    strFileName = "C:\Documents & Settings\User\Application Data\Company\Application\FileName.ext"
    ' Mid()
    sngStart = Timer
    For i = 1 To iMax
        For ii = 1 To Len(strFileName)
            strChar = Mid(strFileName, ii, 1)
            If strChar = "\" Then
                lngLastSlash = ii
            End If
        Next
    Next
    strMessage = strMessage & "Mid(): " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    ' Mid$()
    sngStart = Timer
    For i = 1 To iMax
        For ii = 1 To Len(strFileName)
            strChar = Mid$(strFileName, ii, 1)
            If strChar = "\" Then
                lngLastSlash = ii
            End If
        Next
    Next
    strMessage = strMessage & "Mid$(): " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    ' Mid$() in reverse order
    sngStart = Timer
    For i = 1 To iMax
        For ii = Len(strFileName) To 1 Step -1
            strChar = Mid$(strFileName, ii, 1)
            If strChar = "\" Then
                lngLastSlash = ii
                Exit For
            End If
        Next
    Next
    strMessage = strMessage & "Mid$() in reverse order: " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    ' InStrRev()
    sngStart = Timer
    For i = 1 To iMax
        lngLastSlash = InStrRev(strFileName, "\")
    Next
    strMessage = strMessage & "InStrRev(): " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    ' InStr()
    sngStart = Timer
    For i = 1 To iMax
        ii = InStr(strFileName, "\")
        Do While ii <> 0
            lngLastSlash = ii
            ii = InStr(ii + 1, strFileName, "\")
        Loop
    Next
    strMessage = strMessage & "InStr(): " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    ' Byte array
    sngStart = Timer
    For i = 1 To iMax
        bytArray = StrConv(strFileName, vbFromUnicode)
        For ii = UBound(bytArray) To 0 Step -1
            If bytArray(ii) = 92 Then ' Asc("\") = 92
                lngLastSlash = ii + 1
                Exit For
            End If
        Next
        Erase bytArray
    Next
    strMessage = strMessage & "Byte Array: " & Format(Timer - sngStart, "0.00000") & " seconds" & vbCrLf
    MsgBox strMessage
End Sub

I would point out that a couple minor optimizations could still be made in several of the methods, but big picture is that if you need to know where the last slash in a full path & filename is, InStrRev() is both the easiest and fastest way to go.

[Lord Orwell]

not the point of the post. office 97 doesn't support instrrev for example. Maybe that is what he meant. But i had a question on the window closing.
did you ever try activeworkbook.close or workbook(2).close?
It would negate the need of all the path stripping in the first place

[pgag45]

No Orwell I never tried making the other workbook active and calling activeworkbook.close ... although I will try that when I get back to work.

Anyway, thanks for all the help guys. I ended up going with mid$ in reverse to make sure this runs on 97.

thanks again