Setting workbooks

G

Gordon Rainsford

I have some code that includes the line:

Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")

The problem is that the file I need to set as shRanks may have an
integer in its name - eg "Ranks17.csv"

How best can I do this?
 
J

joel

A CSV file is only one sheet since it is really just a text file. The
sheet name is always the same as the workbookname. the best solution is
just to use the first tab.

from
Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")


to

Set shRanks = Workbooks("Ranks.csv").Sheets(1)



I don't think this is going to work since the workbook name is also
wrong. I would need to see the statement you use to read the CSV file.
If you are using workbook open then try this


fileToOpen = Application _
..GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox("Cannot Open file - Exiting Macro")
exit sub
End If

Set bk = workbooks.open(filename:=fileToOpen)

Set shRanks = bk.Sheets(1)
 
G

Gary''s Student

Sub demo()
Dim shRanks As Worksheet
Dim n As Integer
n = 17
Set shRanks = Workbooks("Ranks" & n & ".csv").Sheets("Ranks")
End Sub
 
G

Gordon Rainsford

I must have been unclear.

There will be a worksheet open, whose name is "Ranks**.csv" where ** are
wildcards. I want to set that worksheet as shRanks, whatever the value
of **,

Thanks,

Gordon
 
D

Dave Peterson

Maybe you can set the variable right after you open the .csv file.

Dim scvwks as worksheet
dim CSVWkbk as workbook

set csvwkbk = workbooks.open(filename:="c:\ranks.csv")
set csvwks = activesheet
 
J

joel

You can search the window names. The search is case sensitive so mak
sure your search string matches the file name.


Sub test()

Dim FName As String
Dim hWndStart As Long
Dim WindowText As String

hWndStart = 0
level = 0
WindowText = "*Ranks*.csv"


FName = FindWindowLike(hWndStart, _
WindowText, level)

set bk = Workbooks(FName)
Set shRanks = bk.Sheets(1)

End Sub





function to get name
------------------------------------------------------


Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, _
ByVal wCmd As Long) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long

Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long
_
As Long

Public Const GWL_ID = (-12)
Public Const GW_HWNDNEXT = 2
Public Const GW_CHILD = 5
'FindWindowLike
' - Finds the window handles of the windows matching the specified
' parameters
'
'hwndArray()
' - An integer array used to return the window handles
'
'hWndStart
' - The handle of the window to search under.
' - The routine searches through all of this window's children an
their
' children recursively.
' - If hWndStart = 0 then the routine searches through all windows.
'
'WindowText
' - The pattern used with the Like operator to compare window'
text.
'
'ClassName
' - The pattern used with the Like operator to compare window'
class
' name.
'
'ID
' - A child ID number used to identify a window.
' - Can be a decimal number or a hex string.
' - Prefix hex strings with "&H" or an error will occur.
' - To ignore the ID pass the Visual Basic Null function.
'
'Returns
' - The number of windows that matched the parameters.
' - Also returns the window handles in hWndArray()
'

'----------------------------------------------------------------------



Function FindWindowLike(ByVal hWndStart As Long, _
WindowText As String, ByVal level As Integer) As String
Dim r As String
' Hold the level of recursion:
' Hold the level of recursion:
'Hold the number of matching windows:

Dim sWindowText As String
Dim sClassname As String
Dim sID

'return nothing if not found
FindWindowLike = ""

' Initialize if necessary:
If level = 0 Then
hWndStart = GetDesktopWindow()
End If
' Increase recursion counter:
level = level + 1
' Get first child window:
hWnd = GetWindow(hWndStart, GW_CHILD)
Do Until hWnd = 0
DoEvents ' Not necessary
' Search children by recursion:
r = FindWindowLike(hWnd, WindowText, level)

If r <> "" Then
FindWindowLike = r
Exit Function
Else

' Get the window text and class name:
sWindowText = Space(255)

r = GetWindowText(hWnd, sWindowText, 255)
sWindowText = Left(sWindowText, r)
If InStr(sWindowText, "Excel") Then
a = 1
End If
' Check that window matches the search parameters:
If sWindowText Like WindowText Then

FindWindowLike = sWindowText

Debug.Print "Window Found: "
Debug.Print " Window Text : " & sWindowText
Debug.Print " Window Handle: " & CStr(hWnd)

Exit Function
Else

' Get next child window:
hWnd = GetWindow(hWnd, GW_HWNDNEXT)
End If

End If
Loop
End Functio
 
J

joel

You can search the window names. The search is case sensitive so mak
sure your search string matches the file name.


Sub test()

Dim FName As String
Dim hWndStart As Long
Dim WindowText As String

hWndStart = 0
level = 0
WindowText = "*Ranks*.csv"


FName = FindWindowLike(hWndStart, _
WindowText, level)

set bk = Workbooks(FName)
Set shRanks = bk.Sheets(1)

End Sub





function to get name
------------------------------------------------------


Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, _
ByVal wCmd As Long) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long

Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long
_
As Long

Public Const GWL_ID = (-12)
Public Const GW_HWNDNEXT = 2
Public Const GW_CHILD = 5
'FindWindowLike
' - Finds the window handles of the windows matching the specified
' parameters
'
'hwndArray()
' - An integer array used to return the window handles
'
'hWndStart
' - The handle of the window to search under.
' - The routine searches through all of this window's children an
their
' children recursively.
' - If hWndStart = 0 then the routine searches through all windows.
'
'WindowText
' - The pattern used with the Like operator to compare window'
text.
'
'ClassName
' - The pattern used with the Like operator to compare window'
class
' name.
'
'ID
' - A child ID number used to identify a window.
' - Can be a decimal number or a hex string.
' - Prefix hex strings with "&H" or an error will occur.
' - To ignore the ID pass the Visual Basic Null function.
'
'Returns
' - The number of windows that matched the parameters.
' - Also returns the window handles in hWndArray()
'

'----------------------------------------------------------------------



Function FindWindowLike(ByVal hWndStart As Long, _
WindowText As String, ByVal level As Integer) As String
Dim r As String
' Hold the level of recursion:
' Hold the level of recursion:
'Hold the number of matching windows:

Dim sWindowText As String
Dim sClassname As String
Dim sID

'return nothing if not found
FindWindowLike = ""

' Initialize if necessary:
If level = 0 Then
hWndStart = GetDesktopWindow()
End If
' Increase recursion counter:
level = level + 1
' Get first child window:
hWnd = GetWindow(hWndStart, GW_CHILD)
Do Until hWnd = 0
DoEvents ' Not necessary
' Search children by recursion:
r = FindWindowLike(hWnd, WindowText, level)

If r <> "" Then
FindWindowLike = r
Exit Function
Else

' Get the window text and class name:
sWindowText = Space(255)

r = GetWindowText(hWnd, sWindowText, 255)
sWindowText = Left(sWindowText, r)
If InStr(sWindowText, "Excel") Then
a = 1
End If
' Check that window matches the search parameters:
If sWindowText Like WindowText Then

FindWindowLike = sWindowText

Debug.Print "Window Found: "
Debug.Print " Window Text : " & sWindowText
Debug.Print " Window Handle: " & CStr(hWnd)

Exit Function
Else

' Get next child window:
hWnd = GetWindow(hWnd, GW_HWNDNEXT)
End If

End If
Loop
End Functio
 
D

Dave Peterson

Wouldn't it be easier to just loop through the workbooks collection matching on
the names?

dim wkbk as workbook
dim csvWks as worksheet

set csvwks = nothing
for each wkbk in application.workbooks
if lcase(wkkb.name) like lcase("ranks*.csv") then
on error resume next
set csvwks = wkbk.worksheets("ranks")
on error goto 0
if csvwks is nothing then
msgbox "Found " & wkbk.name & vblf & "no ranks sheet!"
end if
exit for
end if
next wkbk

if csvwks is nothing then
msgbox "No sheet found by that name in any open workbook"
exit sub '???
end if


========
I wouldn't use, either. I'd still set a variable when open that csv file.
You can search the window names. The search is case sensitive so make
sure your search string matches the file name.

Sub test()

Dim FName As String
Dim hWndStart As Long
Dim WindowText As String

hWndStart = 0
level = 0
WindowText = "*Ranks*.csv"

FName = FindWindowLike(hWndStart, _
WindowText, level)

set bk = Workbooks(FName)
Set shRanks = bk.Sheets(1)

End Sub

function to get name
------------------------------------------------------

Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, _
ByVal wCmd As Long) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long

Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long)
_
As Long

Public Const GWL_ID = (-12)
Public Const GW_HWNDNEXT = 2
Public Const GW_CHILD = 5
'FindWindowLike
' - Finds the window handles of the windows matching the specified
' parameters
'
'hwndArray()
' - An integer array used to return the window handles
'
'hWndStart
' - The handle of the window to search under.
' - The routine searches through all of this window's children and
their
' children recursively.
' - If hWndStart = 0 then the routine searches through all windows.
'
'WindowText
' - The pattern used with the Like operator to compare window's
text.
'
'ClassName
' - The pattern used with the Like operator to compare window's
class
' name.
'
'ID
' - A child ID number used to identify a window.
' - Can be a decimal number or a hex string.
' - Prefix hex strings with "&H" or an error will occur.
' - To ignore the ID pass the Visual Basic Null function.
'
'Returns
' - The number of windows that matched the parameters.
' - Also returns the window handles in hWndArray()
'

'----------------------------------------------------------------------

Function FindWindowLike(ByVal hWndStart As Long, _
WindowText As String, ByVal level As Integer) As String
Dim r As String
' Hold the level of recursion:
' Hold the level of recursion:
'Hold the number of matching windows:

Dim sWindowText As String
Dim sClassname As String
Dim sID

'return nothing if not found
FindWindowLike = ""

' Initialize if necessary:
If level = 0 Then
hWndStart = GetDesktopWindow()
End If
' Increase recursion counter:
level = level + 1
' Get first child window:
hWnd = GetWindow(hWndStart, GW_CHILD)
Do Until hWnd = 0
DoEvents ' Not necessary
' Search children by recursion:
r = FindWindowLike(hWnd, WindowText, level)

If r <> "" Then
FindWindowLike = r
Exit Function
Else

' Get the window text and class name:
sWindowText = Space(255)

r = GetWindowText(hWnd, sWindowText, 255)
sWindowText = Left(sWindowText, r)
If InStr(sWindowText, "Excel") Then
a = 1
End If
' Check that window matches the search parameters:
If sWindowText Like WindowText Then

FindWindowLike = sWindowText

Debug.Print "Window Found: "
Debug.Print " Window Text : " & sWindowText
Debug.Print " Window Handle: " & CStr(hWnd)

Exit Function
Else

' Get next child window:
hWnd = GetWindow(hWnd, GW_HWNDNEXT)
End If

End If
Loop
End Function

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170681

Microsoft Office Help
Click to expand...
 
J

joel

Dave: Your solution won't work if it was opened with a different Exce
application. I thought about your solution first. But if I double
clicked on the file from a window explorer and 2nd excel applicatio
came up and the CSV file was not in the list of files.

The right solution is to assign an object to the CSV file when it wa
opened but when haven't seen the code that opens the book. I assume
that the CSV file was not opened by the VBA code
 
D

Dave Peterson

This line:
Set bk = Workbooks(FName)
assumes that the workbook named FName is open in the same instance of excel that
is running the code.



Dave: Your solution won't work if it was opened with a different Excel
application. I thought about your solution first. But if I doubled
clicked on the file from a window explorer and 2nd excel application
came up and the CSV file was not in the list of files.

The right solution is to assign an object to the CSV file when it was
opened but when haven't seen the code that opens the book. I assumed
that the CSV file was not opened by the VBA code.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170681

Microsoft Office Help
Click to expand...
 
G

Gordon Rainsford

joel said:
Dave: Your solution won't work if it was opened with a different Excel
application. I thought about your solution first. But if I doubled
clicked on the file from a window explorer and 2nd excel application
came up and the CSV file was not in the list of files.

The right solution is to assign an object to the CSV file when it was
opened but when haven't seen the code that opens the book. I assumed
that the CSV file was not opened by the VBA code.
Click to expand...

That's correct - the csv file is opened by another program.
 
Top