Setting workbooks [Programming]

Prev: Access Version being used
Next: Remove Duplicate Numbers in One Cell

From: joel on 16 Jan 2010 11:09

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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Dave Peterson on 16 Jan 2010 11:35

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.

joel wrote:
>
> 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
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]

--

Dave Peterson

From: joel on 16 Jan 2010 11:10

From: joel on 16 Jan 2010 11:50

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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Dave Peterson on 16 Jan 2010 12:54

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.

joel wrote:
>
> 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
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]

--

Dave Peterson

First | Prev | Next | Last
Pages: 1 2 3
Prev: Access Version being used
Next: Remove Duplicate Numbers in One Cell