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

[µo°Ý] ¨D§UÀ°ÀˬdVBA¨º¨à¦³°ÝÃD

[µo°Ý] ¨D§UÀ°ÀˬdVBA¨º¨à¦³°ÝÃD

¤U­±Workbook­ì¥»¦³3­ÓVBA¡A¦Û±q¥[¤J¤F²Ä4­ÓVBA«á¡A²Ä2­ÓVBAªº®ÄªG¶}©l¦³¤FÂI°ÝÃD(EXCEL2000ª©)¡A
¬O¬Æ»ò¦a¤èÅÞ¿è©Î±Æ¦C¦³°ÝÃD¶Ü?
¥i¬O¦pªG§â²Ä4­Ó®³±¼¡A´N¤S¥¿±`¡F
§Ú¥ý§âVBA¶K¤W¨Ó³o¡A³o¦U¦ì¤j¤jÀ°§Ú¬Ý¬Ý­þ¦³°ÝÃD¡A­Y³o¼ËµL§ä¥X°ÝÃD¡A§Ú¦A¥tªþÀɮפW¨Ó¡A¥ýÁÂÁ¦U¦ì¤F¡I


Private Sub Workbook_Open()

Call ÀË´ú¸ê®ÆÀÉ: If xMsg = "" Then GoTo 999 '²Ä1­ÓVBA

MsgBox xMsg & Chr(10) & Chr(10) & _
       "¼u¥Xµøµ¡Åã¥Ü¤å¦r" & Chr(10) & Chr(10) & _
       "¼u¥Xµøµ¡Åã¥Ü¤å¦r2"
      
Application.DisplayAlerts = False
If Workbooks.Count = 1 Then
   Application.Quit
Else
   ThisWorkbook.Close 0
End If
Application.DisplayAlerts = True
Exit Sub
999:
If Info2 <> "" Then MsgBox Info2


              Worksheets("sheet1").Protect Password:="123", userInterFaceonly:=True   '²Ä2­ÓVBA

              Worksheets("sheet1").EnableAutoFilter = True
              
              
Dim Str, GetValue   '²Ä3­ÓVBA
On Error Resume Next
Str = "'" & "C:\Users\Desktop\[TEST.xls]TEST'!R1C1" '¸ô®|ÀɦW¦Û¦æ­×§ï
GetValue = Application.ExecuteExcel4Macro(Str)
If Sheet4.[A1] <> GetValue Then MsgBox "WORD¡I", 48



Dim MyB As Workbook, xB As Workbook, xFile$ '²Ä4­ÓVBA
Set MyB = ThisWorkbook
xFile = "C:\Users\Desktop\TEST1.xls"  '¸ô®|ÀɦW¦Û¦æ­×§ï
Application.ScreenUpdating = False
Set xB = Workbooks.Open(xFile, ReadOnly:=True)

xB.Sheets("TEST1").Protect "123"
With MyB.Sheets("TEST2")
    .Unprotect "123"
    xB.Sheets("TEST3").[B1:BA500].Copy .[B1]
    .Protect "123"
End With



xB.Close 0


         End Sub

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-2-14 22:32 ½s¿è
¤U­±Workbook­ì¥»¦³3­ÓVBA¡A¦Û±q¥[¤J¤F²Ä4­ÓVBA«á¡A²Ä2­ÓVBAªº®ÄªG¶}©l¦³¤FÂI°ÝÃD(EXCEL2000ª©)¡A
¬O¬Æ»ò¦a ...
RCRG µoªí©ó 2016-2-12 14:14

¨SÀÉ®×¥i¥H¸Õ, ¥¼¹ê´ú¯Â±À´ú,§A¥i¥H®M¥Î¬Ý¬Ý (PS. ´ú¸Õ«e½Ð°O±o¥ý³Æ¥÷).

¦³¥i¯à¬O:
Set xB = Workbooks.Open(xFile, ReadOnly:=True)
³o¤@¦æ¤S¥h©I¥s Workbook_Open µ{§Ç,
¦Ó¥¦¤S·|¥h°õ¦æ¨ì ²Ä2­ÓVBA,
¥H­P²£¥Í«D¹w´Áªºµ²ªG.

¤@¯ë­nÁקK¦¹Ãþ±¡§Î§Ú·|¦Ò¼{¥Î¥H¤U¨âºØ¤è¦¡¤§¤@:

1. °õ¦æµ{§Ç«e,¥ý§âIJµo¦æ¬°¶}ÃöÃö±¼,°õ¦æ§¹¦A¥´¶}:
Application.EnableEvents = False
Set xB = Workbooks.Open(xFile, ReadOnly:=True)
Application.EnableEvents = True


2. ³]¸mºX¼Ð±±¨î(ºX¼Ð¤@¯ë¶·¬°¥þ°ìÅܼÆ)¬O§_­nIJµo:
Module-

Public bOpen As Boolean



Workbook_Open-
Private Sub Workbook_Open()
If bOpen Then Exit Sub
Call ÀË´ú¸ê®ÆÀÉ: If xMsg = "" Then GoTo 999 '²Ä1­ÓVBA
.
.
.
bOpen = True
Set xB = Workbooks.Open(xFile, ReadOnly:=True)
bOpen = False
.
.
.

TOP

¦^´_ 2# luhpro


    ¸ÕµÛ¦bSet xB = Workbooks.Open(xFile, ReadOnly:=True) «e«á¥[¤W

Application.EnableEvents = False         bOpen = True
                                                              »P
Application.EnableEvents = True          bOpen = False

¦P¼Ë°ÝÃD¦ü¥G¤]¬O¦s¦b¡IQQ
¨SÃö«Y§Ú¦A§ä¬Ý¦³¨S¥¦ªk¦n¤F¡A¤]ÁÂÁÂuhpro´£¨Ñªº¤èªkÅý§Ú°Ñ¦Ò³á

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-2-19 22:46 ½s¿è

¦^´_ 3# RCRG
§A¸Õ¸Õ§â¨º¦æ:
On Error Resume Next
§ï¦¨:
On Error Goto 0
¶]¶]¬Ý¬O§_·|¦³¿ù»~µo¥Í? ­Y¦³«h¬Ý¬Ýµo¥Í¦b­þ¤@¦æ? ¬Ý¬Ý¬O§_¯à±Æ°£°ÝÃDÂI.
¦]¬° Resume Next «ü¥Oª½±µ©¿²¤¿ù»~, ¦Ó·|°Ê¨ìExcel¥~³¡ªº«ü¥O(¦p : Open ÀÉ®×), ¥Î³o­Ó«ü¥O¦³¥i¯à·|¥X²{µLªk¹w´Áªºµ²ªG.

¥t¤@­Ó¤èªk¬O :
¥ªÁäÂI¤@¤U ©³¤U³o ¨â ¦æ ¥ªÃ䪺¦Ç¦â°Ï°ì - Åý³o¨â¦æÅܦ¨´Ä¦â©³ (§Y³]¦¨°õ¦æ¤¤Â_ÂI)
1)     Call ÀË´ú¸ê®ÆÀÉ   ªº¤U¤@¦æ, ·í xMsg <> ""  ®É±q³o¦æ¶}©l¶] :
MsgBox xMsg & Chr(10) & Chr(10) & _
         "¼u¥Xµøµ¡Åã¥Ü¤å¦r" & Chr(10) & Chr(10) & _
        "¼u¥Xµøµ¡Åã¥Ü¤å¦r2"


2)     999:   ªº¤U¤@¦æ, ·í xMsg = ""  ®É±q³o¦æ¶}©l¶] :
If Info2 <> "" Then MsgBox Info2

°õ¦æ°Ê§@µ¥µ{¦¡¼È°±¦b³o¨â¦æ¤§¤@®É,
³v¦æ «ö F8 «öÁä ±Ò°Ê ³æ¨B°õ¦æ,
°lÂܵ{¦¡¬yµ{¬O§_¥¿½T.

TOP

¦^´_ 4# luhpro

½Ð°Ý¤@¤U¤j®v
Set xB = Workbooks.Open(xFile, ReadOnly:=True)
³o¬q¬O¬Æ»ò¥\¯à©O? ¦³¦s¦b¥²­n¶Ü?

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD