ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦³¨â­Ó¿W¥ßEXCEL¡A¦p¦óª¾¹D¤Á´«¨ì¥t¤@­Ó¿W¥ßEXCEL¡H

¦pªG¤â°Ê·s¼W¤@­Ó¬¡­¶Ã¯®É¡A³o­Ó¬¡­¶Ã¯«o¤£·|³Q­pºâ¨ì¡H

¦U¦ì«e½ú­Ì¤j®a¦n¡A

ºÃ°Ý¬O¡G¡y¦pªG¤â°Ê·s¼W¤@­Ó¬¡­¶Ã¯®É¡A³o­Ó¬¡­¶Ã¯«o¤£·|³Q­pºâ¨ì¡HVBA¸Ó¦p¦ó­×¥¿¤~¯àª¾¹D¥¿½Tªº¬¡­¶Ã¯¼Æ¶q¡H ¡z

ºÃ°Ýµo¥Í¦p¤U¡G
§Ú¦³¤@­ÓÀɮצWºÙ¬°A.xlsm¡A¦b¥N½X¤W¼g¤Wdebug.print workbooks.count¯à­pºâ¥X 1 ¡]¹ê»Ú¤W¥u¦³1­ÓexcelÀÉ®×)¡C
¦pªG³o®É¤â°Ê·s¼W¤@­Óexcel·sÀÉ¡A¦bA.xlsm°õ¦ædebug.print workbooks.countÁÙ¬O¥u¯àÅã¥Ü 1 (¹ê»Ú¤W¦³2­ÓexcelÀÉ¡^

½Ð°Ý¦U¦ì«e½ú­Ì¡A¥N½XÀ³¸Ó¦p¦ó­×¥¿¡H½Ð«ü¾É¡A·PÁ¡ã
  1. debug.print workbooks.count
½Æ»s¥N½X

TOP

¦U¦ì«e½ú­Ì¤j®a¦n¡A

ºÃ°Ý¬O¡G¡y¦pªG¤â°Ê·s¼W¤@­Ó¬¡­¶Ã¯®É¡A³o­Ó¬¡­¶Ã¯«o¤£·|³Q­pºâ¨ì¡HVBA¸Ó¦p¦ó­×¥¿¤~¯à ...
justintoolbox µoªí©ó 2015-7-24 07:25


¦U¦ì«e½ú­Ì¡A¹ï©ó¡y¤â°Ê·s¼W¬¡­¶Ã¯¡z«e­±»¡ªº¤£²M·¡¡A©³¤U¦A¸Ô²Ó»¡©ú¡C

¤â°Ê·s¼W¬¡­¶Ã¯ªº¤è¦¡¬°¡G«ö¦íshiftÁä¡A¨Ã¦ba.xlsmÀɤWÂI¤@¤U¥ªÁä¡]¦p¤U¹Ïa)¡A´N·|¥X²{¤@­Ó¬¡­¶Ã¯¤F(¦p¤U¹Ïb)¡C

¥¼©R¦W_°Æ¥».jpg ----¹Ïa
----¹Ïb
=====================================================
¥u¦³¥H¤W³oºØªº¤â°Ê·s¼W¤è¦¡¡Aworkbooks.count¬° 1
¦ý­Y¬O±Ä¥Î¦b a.xlsmªºÀÉ®×-->·s¼W¬¡­¶Ã¯ªº¤è¦¡¡A³o¥i¥H¥¿½T­pºâ¥X¨Ó¬° 2

¤£ª¾¹D¬°¤°»ò·|³o¼Ë¤l¡H

TOP

³o¤@¼h¨Ã«Dworkbooks³á!
¦]¸Ó¬Oworkbooksªºª¨ª¨ application¤~¹ï¡A©Ò¥H¦]¸Ó¨S¦³¿ìªk¥Îapplication.vba¥h­pºâ¨ìworkbooks¼Æ¶q¡A¦ý¥ÎAPI¥hºâexcel application¦]¸ÓÁÙ¬O¥i¥H¿ìªº¨ì!
­Y¬O§Ú¦^µª¡A¨Ï±zº¡·N¡A½Ð±zÅý§Úª¾¹D¡I                  
­Y¬O§Úªº¦^ÂСA±z¤´¦³¨ä¥L¨£¸Ñ¡A¤]½Ð±z¤£¶Þ«ü±Ð¡I

TOP

³o¤@¼h¨Ã«Dworkbooks³á!
¦]¸Ó¬Oworkbooksªºª¨ª¨ application¤~¹ï¡A©Ò¥H¦]¸Ó¨S¦³¿ìªk¥Îapplication.vba¥h­p ...
Min µoªí©ó 2015-7-25 00:32


­ì¨Ó¬O"¤£¦P¼h"ªºÃö«Y¡ã
«D±`·PÁÂMin¤j¸Ñ´b¡I
¤]´£¨Ñ¤@­Ó¸Ñ¨M°ÝÃDªº¤è¦V¡A«D±`·PÁ¡I

¦ý¬O...¦]¬°¹ï¤W¤@¼hAPI¯uªº¤£ÁA¸Ñ¡C
«e½ú­Ì¯à§_¦A´£¿ô¤@¤U³o³¡¤ÀªºÃöÁä¦rÀ³¸Ó¬d¸ß¤°»ò¡H
Åý§Ú¥i¥H§ó²M·¡ªº¥h§ä´Mµª®×¡C·PÁ¡I

TOP

¦^´_ 4# justintoolbox
¦U¦ì«e½ú­Ì¤j®a¦n¡A
¥Ø«e°ÝÃD¡G¦p¦ó¯à§ì¨ì²Ä¤G­Óexcel¨ÃŪ¨úÀɦW¡H
¥´¶}¤u§@ºÞ²z­û¡Aª¾¹D³o¨â­Óworkbook¬O¤£¦P¤@­Óexcel¡C
¤u§@ºÞ²z­û.jpg

¥Î¥H¤U¤èªk¡A¥u¯àŪ¨ú²Ä¤@­ÓexcelªºÀɦW¡]a.xlsm)¡A¥N½XÀ³¸Ó¦p¦ó­×¥¿¤~¯àŪ¨ú²Ä¤G­ÓEXCELÀɦW¡H
«ô°U¡A¦U¦ì«e½ú­Ì¥X¤â¬Û±Ï¡I
  1. Sub ex()
  2. Dim oXL   As Excel.Application
  3. Dim oWB   As Excel.Workbook

  4. Set oXL = GetObject(, "Excel.Application")
  5. For Each oWB In oXL.Workbooks
  6.         Debug.Print oWB.Name
  7. Next

  8. End Sub
½Æ»s¥N½X

TOP

¦^´_  justintoolbox
¦U¦ì«e½ú­Ì¤j®a¦n¡A
¥Ø«e°ÝÃD¡G¦p¦ó¯à§ì¨ì²Ä¤G­Óexcel¨ÃŪ¨úÀɦW¡H
¥´¶}¤u§@ºÞ²z­û ...
justintoolbox µoªí©ó 2015-7-25 10:02


¦U¦ì«e½ú­Ì¤j®a¦n
¥Ø«e§ä¨ì¤F¤èªk¡]¥N½X¦p¤U)¥i¥Hª¾¹D¦³´X­Ó¿W¥ßEXCEL¡]¤£ª¾¹D¸Ó¦p¦óºÙ©I¡A¼È®É¥ýºÙ¬°¿W¥ßEXCEL)¡A
·QÄ~Äò´M®Ú¨s©³ªºª¾¹D¡A¥N½XÀ³¸Ó¦p¦ó­×¥¿¤~¯àª¾¹D²Ä¤G­Ó¿W¥ßEXCELªºÀɦW¡H
  1. Option Explicit
  2. Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias _
  3. "FindWindowExA" ( _
  4. ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
  5. ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
  6. Private Declare PtrSafe Function GetDesktopWindow Lib "user32.dll" () As Long

  7. Sub GetXLhWnds() '¸ê®Æ¡Ghttp://www.pcreview.co.uk/threads/getobject-when-more-than-1-excel-is-running.3575757/
  8. Dim n As Long
  9. Dim hWndXL As Long

  10. Do
  11.         hWndXL = FindWindowEx(GetDesktopWindow, hWndXL, "XLMAIN", vbNullString)
  12.         If hWndXL Then
  13.         n = n + 1
  14.         End If
  15. Loop Until hWndXL = 0
  16. Debug.Print "¿W¥ßEXCEL¦³¡G" & n &"­Ó"
  17. End Sub
½Æ»s¥N½X

TOP

Sub testxxx()
bookcount = Application.Windows.Count
For i = 1 To bookcount
    Debug.Print Application.Windows(i).Caption
Next
End Sub

TOP

¥»©«³Ì«á¥Ñ no3-taco ©ó 2015-7-25 12:20 ½s¿è

¦^´_ 6# justintoolbox

¤£¦n·N«ä¡A­è­è§Ú¶Kªº¦æ¤£³q
§Ú·Ó§Aªº¤èªk¶}±Òexcel¡A¯uªº¤£¦æ

TOP

¦^´_  justintoolbox

¤£¦n·N«ä¡A­è­è§Ú¶Kªº¦æ¤£³q
§Ú·Ó§Aªº¤èªk¶}±Òexcel¡A¯uªº¤£¦æ
no3-taco µoªí©ó 2015-7-25 12:17


no3-taco«e½ú¡AÁÂÁ§AªºÀ°¦£¡C

§Ú¦³¬d¨ìºô¸ô¤WªºÃþ¦üªº°ÝÃD¡C§Ú§â¥N½X©ñ¦b©³¤U¡]¤W¤@«Êªº¥N½X¬O§Ú¨Ì·Ó¤U­±ªº¥N½X­×¥¿¦Ó¨Ó¡A¥iÅã¥Ü¦h¤Ö­Ó¿W¥ßEXCEL)¡C

´ú¸Õµ²ªG¡G
§Ú¦ba.xlsm°õ¦æ¹L©³¤U¥N½Xªºtestµ{§Ç¡Aµ²ªGdebug.print¥X¨Ó¬O¤@¦êªø¾ã¼Æ¡C
¦pªG³o®É­Ô¡G¡y«ö¤UshitÁä¨Ã¦ba.xlsm«ö¤U¥ªÁä¡z¡A«h·|·s¼W¥X¤@­Ó¿W¥ßªºexcelÀÉ¡C
³o®É­Ô¦ba.xlsm°õ¦ætestµ{§Ç¡A·|¥X²{¨â¦êªø¾ã¼Æ¡C

·P·Q¡G
¥uª¾¹D³o¨â¦ê¦U¥NªíµÛ¨â­Ó¿W¥ßEXCEL¡A¦ý«o¤£ª¾¹D¦p¦ó§Q¥Î¡H
§Ú¦³«ä¦Ò¡G­Yª¾¹D³o¨â¦ê¼Æ¦r¡A´N»¡¤£©w¥i¥H¾Þ±±­Ó§O¿W¥ßEXCEL¤F¡]¤]´N¥i¥Hª¾¹D¥t¤@­Ó¿W¥ßEXCELÀɦW¤F¡^

Àµ½Ð¦U¦ì«e½ú©Î°ª¤â­Ì¡A¥X¤â¬Û±Ï¡ã ·PÁ¡I¡ã
  1. Option Explicit
  2. Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias _
  3. "FindWindowExA" ( _
  4. ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
  5. ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
  6. Private Declare PtrSafe Function GetDesktopWindow Lib "user32.dll" () As Long

  7. Sub test()
  8. Dim i As Long
  9. Dim arrXLhWnd() As Long

  10. If GetXLhWnds(arrXLhWnd) Then
  11. For i = LBound(arrXLhWnd) To UBound(arrXLhWnd)
  12. Debug.Print arrXLhWnd(i)
  13. Next
  14. End If

  15. End Sub

  16. Function GetXLhWnds(arrXLhWnd() As Long) As Long
  17. Dim n As Long
  18. Dim hWndXL As Long, hWndDT As Long

  19. ReDim arrXLhWnd(1 To 100) ' cater for 100 potential Excelinstances
  20. hWndDT = GetDesktopWindow

  21. Do
  22. hWndXL = FindWindowEx(hWndDT, hWndXL, "XLMAIN", vbNullString)
  23. If hWndXL Then
  24. n = n + 1
  25. arrXLhWnd(n) = hWndXL
  26. End If
  27. Loop Until hWndXL = 0
½Æ»s¥N½X

TOP

no3-taco«e½ú¡AÁÂÁ§AªºÀ°¦£¡C

§Ú¦³¬d¨ìºô¸ô¤WªºÃþ¦üªº°ÝÃD¡C§Ú§â¥N½X©ñ¦b©³¤U¡]¤W¤@«Êªº¥N½X¬O§Ú¨Ì ...
justintoolbox µoªí©ó 2015-7-25 17:37


¸Õ¸Õ¬Ý(´ú¸ÕÀô¹Ò : Win7 + Office2013)
  1. Option Explicit
  2. Option Base 1

  3. #If Win64 Then
  4. Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
  5.         (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
  6.          ByVal lpsz2 As String) As LongPtr
  7. Private Declare PtrSafe Function IIDFromString Lib "ole32" _
  8.         (ByVal lpsz As LongPtr, ByRef lpiid As GUID) As LongPtr
  9. Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
  10.         (ByVal hWnd As LongPtr, ByVal dwId As LongPtr, ByRef riid As GUID, _
  11.          ByRef ppvObject As Object) As Long

  12. #Else
  13. Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
  14.         (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
  15.          ByVal lpsz2 As String) As Long
  16. Private Declare Function IIDFromString Lib "ole32" _
  17.         (ByVal lpsz As Long, ByRef lpiid As GUID) As Long
  18. Private Declare Function AccessibleObjectFromWindow Lib "oleacc" _
  19.         (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As GUID, _
  20.          ByRef ppvObject As Object) As Long
  21. #End If
  22.          
  23. Private Type GUID
  24.     Data1 As Long
  25.     Data2 As Integer
  26.     Data3 As Integer
  27.     Data4(7) As Byte
  28. End Type

  29. Private Const S_OK As Long = &H0
  30. Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
  31. Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0

  32. Private Function GetXLapp(hWinXL, xlApp As Object) As Boolean
  33.     Dim hWinDesk, hWin7, obj As Object
  34.     Dim iid As GUID
  35.     Call IIDFromString(StrPtr(IID_IDispatch), iid)
  36.     hWinDesk = FindWindowEx(hWinXL, 0&, "XLDESK", vbNullString)
  37.     hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
  38.     If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iid, obj) = S_OK Then
  39.         Set xlApp = obj.Application
  40.         GetXLapp = True
  41.     End If
  42. End Function

  43. Private Function IsCollectionExists(ByVal oCol As Collection, ByVal vKey As Variant) As Boolean
  44.     On Error Resume Next
  45.     oCol.Item vKey
  46.     IsCollectionExists = (Err.Number = 0)
  47.     Err.Clear
  48.     On Error GoTo 0
  49. End Function

  50. Public Function GetXLInstanceInfo(ByRef col As Object) As Long
  51.     Dim hWndXL, i As Long
  52.     Dim xlApp As Object, wb As Object

  53.     Set col = Nothing
  54.     Set col = New Collection

  55.     hWndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
  56.     While hWndXL > 0
  57.         If GetXLapp(hWndXL, xlApp) Then
  58.             For Each wb In xlApp.Workbooks
  59.                 If Not IsCollectionExists(col, wb.Name) Then
  60.                      col.Add Array(hWndXL, xlApp, wb.Name, wb.Path), wb.Name
  61.                 End If
  62.             Next
  63.         End If
  64.         hWndXL = FindWindowEx(0, hWndXL, "XLMAIN", vbNullString)
  65.     Wend
  66.     GetXLInstanceInfo = col.Count
  67.    
  68. End Function

  69. Sub Ex()
  70.     Dim col As Collection
  71.     Dim i As Long
  72.     Dim xlApp As Excel.Application, AR As Variant
  73.    
  74.     i = GetXLInstanceInfo(col)
  75.     Debug.Print "¿W¥ßEXCEL¦³¡G" & i & "­Ó"
  76.    
  77.     '´ú¸Õ : ¦b©Ò¦³ExcelÀɲÄ1­Ó¤u§@ªíÀx¦s®æA1¤W¼g¤J¦Û¤vªºÀɦW
  78.     For i = 1 To col.Count
  79.         AR = col(i)    ' AR(1): HWND (µøµ¡±±¨î¥N½X)
  80.                        ' AR(2): xlApp (ExcelÀÉ©ÒÄݪº¤÷¶µ Excel.Application)
  81.                        ' AR(3): ÀɦW
  82.                        ' AR(4): Àɮ׸ô®|
  83.         Set xlApp = AR(2)
  84.         xlApp.Workbooks(AR(3)).Sheets(1).Range("A1").Value = "ÀɦW:" & AR(3)
  85.     Next
  86.    
  87.     Set xlApp = Nothing
  88.     Set col = Nothing
  89.    
  90. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD