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

Ãö©ó§å¦¸³B²zÀx¦s®æ°ÝÃD ¨D¤j®aÀ°¦£¸Ñ´b

Ãö©ó§å¦¸³B²zÀx¦s®æ°ÝÃD ¨D¤j®aÀ°¦£¸Ñ´b

¤j®a¦n
¤p§Ìªì¾Çexcel vba¤£¤[¡A¦³­Ó§å¦¸§ó§ïÀx¦s®æµ{¦¡°ÝÃD·Q½Ð¦U¦ìÀ°¦£¸Ñµª¡C

¤£ª¾¹D¯à§_§Q¥ÎVBA¼g¤@µ{¦¡¡AÅý"¬Y«ü©w¸ê®Æ§¨"¤º©Ò¦³excelÀɮתº¬YÀx¦s®æ¤º®e(EX:"A1")¡A¶ñ¤J©T©w¼Æ­È(EX:"123")¡C

¥H¤Wµ{¦¡¾Þ§@¬O§_¥u¯à¥HAPI¹F¨ì©O?  VBA¤]¦æ?

¦pVBA¥i¦æ¡A½Ð¤j®aÀ°À°¦£«Øij¸Ó¥Î¨º¨Ç¨ç¼Æ¼¶¼g¡C «D±`·PÁÂ~

¦^´_ 1# rbktwi

test.rar (29.5 KB)
  1. Sub ¥¨¶°1()
  2.     Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ
  3.     ' myfloder="±zªºÀɮ׸ô®|¥]§t³Ì«á¤@­Ó­n\"
  4.     w1 = ActiveWorkbook.Name
  5.     '­Y¬O·Q­n§ä¨ì³o­ÓEXCELÀɮתº©Ò¦b¥Ø¿ý´N¨Ï¥Î
  6.     Dim WrdArray() As String
  7.     myfloder = ""
  8.     WrdArray() = Split(ThisWorkbook.FullName, "\")
  9.     For i = 0 To UBound(WrdArray) - 1
  10.         myfloder = myfloder & "\" & WrdArray(i)
  11.     Next i
  12.     myfloder = Mid(myfloder, 2, Len(myfloder) - 1) & "\"
  13.    
  14.     '§ä¥X©Ò¦³ÀɮצWºÙ
  15.     FILE1 = Dir(myfloder)
  16.     Do While FILE1 <> ""
  17.         ar = ar & "," & FILE1 '(¨S«ü©w­þºØÀɮתºEXCEL)
  18.         FILE1 = Dir '¨ú±o¤U¤@­ÓÀɦW
  19.     Loop
  20.     ar = Split(Mid(ar, 2, 100000), ",") '©î¶}²Ä¤@­Ó,
  21.     '¶]¨C­ÓEXCELÀÉ
  22.     For Each e In ar
  23.         If e <> w1 Then '¤£°õ¦æ¦Û¤v¥»ªºÀÉ®×
  24.             Workbooks.Open (myfloder & e)
  25.                 Cells(1, 1) = "123" 'A1¿é¤J­È(¨S«ü©wSHEET)
  26.             ActiveWindow.Close saveChanges:=True 'Ãö³¬¥BÀx¦s
  27.         End If
  28.     Next
  29.     Application.ScreenUpdating = True '«ì´_¿Ã¹õ
  30. End Sub
½Æ»s¥N½X
PKKO

TOP

·PÁÂBobÀ°¦£ ±ßÂI¨Ó½Õ¸Õ

¥i§_¥t¥~¦A½Ð±Ð ¦pªGEXCELÀɮפ@¥´¶}·|¸õ¥XÃþ¦ü ±Ò¥Î¥¨¶°or°Ý­n¤£­n§ó·s³sµ²µ¥µ¥¤§Ãþ¿ï¶µªº¸Ü,­n«ç»òÅývba¥h¿ï¾Ü°Ê§@©O?  ¨Ò¦p¿ï¾Ü±Ò°Ê¥¨¶° or ¿ï¾Ü¤£­n§ó·s ¦A¦¸·PÁÂ

TOP

¦^´_ 3# rbktwi

¤@¡B±Ò°Ê¥¨¶°»Ý­n¤H¬°¾Þ§@,ÁקK¯f¬r¤£»Ý­n¸g¹L±zªº¦P·N,°õ¦æ¯f¬r½X
¥i¦b¦w¥þ©Ê³]©w,³]©w§¹²¦«áÃö³¬,¦A¥´¶}®É´N·|¬O±z³]©wªºµ²ªG,­Y³]©w³Ì§C,«h·|µL±ø¥óª½±µ±Ò°Ê¥¨¶°

¤G¡B³sµ²ªº³¡¤À
¦bThisWorkbookªº¼Ò²Õ¸Ì­±,Workbook_Openªº¨Æ¥ó¤º
³]©w    Application.AskToUpdateLinks=False '¥Nªí¤£·|¸ß°Ý
³]©w   Workbook.UpdateLinks =False ''¥Nªí¤£·|±Ò°Ê³sµ²

¤Ï¤§¥ç¦p~
¸Õ¸Õ¬Ý,¦³¿ù»~½Ð¤£§[ªÈ¥¿!
PKKO

TOP

¦^´_ 4# PKKO

­ì¨Ó¦p¦¹~ «D±`·PÁÂBobÀ°¦£¸Ñ´b ¨ü¯q¨}¦h~

TOP

¤£¦n·N«ä ·Q¦A½Ð°ÝBob
Ãö©ó·j´MÀx¦s®æ ¥H¤Uµ{¦¡½X
  '¶]¨C­ÓEXCELÀÉ
   For Each e In ar
        If e <> w1 Then '¤£°õ¦æ­ì¥ý¶}±ÒªºÀÉ®×
            Workbooks.Open (myfloder & e)
               s = Worksheets.Count
                    For j = 1 To s
                    Find = ActiveWorkbook.sheets(j).cells.Find(What:="Exchange Rate:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate
Next
        End If
    Next
§Ú·Q°µªº¬O°Ñ·Ó§@¥Î¤¤¬¡­¶Ã¯ ±µµÛ½ü¬y·j´M°Ñ·Ó§@¥Î¤¤¬¡­¶Ã¯ªº¦U¤u§@ªí
¦ýexcelÅã¥Üª«¥ó¤£¤ä´©¦¹¥\¯à ½Ð°Ý§Ú¸Ó¥Î­þ¤@ºØÄÝ©Ê©O?

TOP

¤w¸Ñ¨M~
   Dim fr As Range
   Dim sh As Worksheet
    For Each e In ar
    If e <> w1 Then
    Workbooks.Open (myfloder & e)
            For Each sh In ActiveWorkbook.Worksheets
                Set fr = sh.Cells.Find(what:="Exchange Rate:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, SearchFormat:=False)
                    If Not fr Is Nothing Then
                               Msgbox fr.Name
                    End If
            Next sh
    ActiveWindow.Close saveChanges:=True 'Ãö³¬¥BÀx¦s
    End If
    Next e

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD