GBKEE

¦^´_ 8# luffyzoro

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim hubopf As String, AR
   
4.     hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
   
5.     If hubopf = "False" Then MsgBox "½Ð¿ï¨úÀÉ®×": Exit Sub
   
6.     AR = Split(hubopf, "\")
   
7.     hubopf = AR(UBound(AR))   'hubopf = "B.xls"ÀHµÛ¶}±ÒÀɦW¤£¦P¤]¥i¯à¬O"C.xls"
   
8.     Workbooks.Open hubopf     'Workbooks( hubopf )
   
9. End Sub

½Æ»s¥N½X

luffyzoro

¦^´_ 7# Hsieh


    ª©¤j¦A¦¸·PÁ±zªº¼ö¤ß

©êºp,§Ú¨S¦³§â°ÝÃD»¡²M·¡
°²³]§Úªº¥¨¶°¬O¼g¦bA.xls , ¥ÎA.xls¶}±ÒB.xls , ·Óª©¤jªº¤è¦¡§Úªºhubopf·|±o¨ì
"D:\C¸ê®Æ§¨\F¸ê®Æ§¨\B.xls"
¹ê»Ú¤W§Ú·Q±o¨ìªº¦r¦ê¥u¦³ "B.xls" ,¦]¬°¨C¦¸¶}ªºÀɦW¥i¯à¤£¤@¼Ë
©Ò¥H§Ú·Q±Nhubopf = "B.xls"
¦p¦¹«áÄòªºWorkbooks("hubopf")À³¸Ó´N·|·ÓµÛ¶}±Ò¤£¦PªºÀÉ®×°µÅÜ°Ê

½Ð°Ý
1.§Ú¸Ó¦p¦ó¨ú±ohubopf = "B.xls"ÀHµÛ¶}±ÒÀɦW¤£¦P¤]¥i¯à¬O"C.xls"
2.«áÄò­n®M¥ÎWorkbooks¬O­n¼gWorkbooks(hubopf)ÁÙ¬O­n¼gWorkbooks("hubopf")?

·PÁÂ

Hsieh

¦^´_ 6# luffyzoro

Dim hubopf As String
hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If hubopf = "False" Then MsgBox "½Ð¿ï¨úÀÉ®×": Exit Sub
Workbooks.Open hubopf

luffyzoro

¦^´_ 5# Hsieh


    ¦A¦¸·PÁ¤j¤j¨³³tªº¦^ÂÐ,§Ú¤S¦³·s°ÝÃD¤F

Application.Dialogs(xlDialogOpen).Show
  Dim hubopf As String
  hubopf = ActiveWorkbook.Name¡ö§Úªº¥Î·N¬O­n¨ú±o¥H¹ï¸Ü®Ø³Í±ÒÀɮתº¦WºÙ,¬O§_³o¸Ì¦³°ÝÃD?
  Worksheets("AC-8E").Cells.Select
  Worksheets("AC-8E").Range("A:BY").Sort Key1:=Worksheets("AC-8E").Range("D2"), _
    Order1:=xlAscending, Key2:=Worksheets("AC-8E").Range("I2") _
    , Order2:=xlAscending, Header:=xlYes
  ThisWorkbook.Activate
  Dim calopf As String
  calopf = ThisWorkbook.Name
  ActiveSheet.Cells.Select
  Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2"), _
   Order2:=xlAscending, Key3:=Range("E2"), _
   Order3:=xlAscending, Header:=xlYes
  Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
  Selection.Offset(1, 0).Select
  Workbooks(hubopf).Worksheets("AC-8E").Activate¡ö³v¦æ°õ¦æ¨ì³o¸Ì¥X²{¿ù»~"«¬ºA¤£²Å¦X"

½Ð°Ý¬O¤£¬O§Ú¦b«Å§ihubopf³o­ÓÅܼƮɦ³°ÝÃD?

·PÁ¤j¤j

Hsieh

¦^´_ 4# luffyzoro


    hubcn = 2
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[hub.xls]AA!R" & hubcn & "C2,[hub.xls]AA!R" & hubcn & "C24>0,[hub.xls]AA!R" & hubcn & "C24-SUM(R2C11:RC[5])>0),[hub.xls]AA!R" & hubcn & "C9)"

luffyzoro

¦^´_ 2# Hsieh

hubcn = 2
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[hub.xls]AA!"R"&hubcn&"C2",[hub.xls]AA!"R"&hubcn&"C24">0,[hub.xls]AA!"R"&hubcn&"C24"-SUM(R2C11:RC[5])>0),[hub.xls]AA!"R"&hubcn&"C9")"

½Ð°Ýª©¤j­Y·Ó±zÁ|¨Ò¬O§_¬O¼g¦¨¦p¤W?³o¦¸§Ú­nÅܰʪº¬Orows
¦ý¬O«o¥X²{»yªk¿ù»~?

½Ð°Ý­þ¸Ì»Ý­n­×¥¿?

·PÁÂ

luffyzoro

·q½Ð«üÂI.rar (34.38 KB)

ªþÀÉ®×
¤U¸ü¦¸¼Æ: 26

2011-11-22 02:09

¦^´_ 2# Hsieh

ª©¤j±z¦n
·PÁ±z¼ö¤ß¦^ÂÐ
¤p§Ì¦]¤u§@»Ý­n,¦]¦¹À|¸Õ¼gVBA
¼g¤F¤@°ï¦Û¤v¬Ý¤F³£«Ü½ÆÂøªºµ{¦¡½X,¼g¨ì¥d¦í¤F
Àµ½Ðª©¤j¶}ÄÀ
¸Ó¥Î¤°»ò¼gªk¤~¯à§¹¦¨¤p§Ì·Q­n¹F¦¨ªº§¹¦¨ÀÉ
¨ä¤¤¦³ªþ¤W¤p§Ì¼gªº
Àµ½Ðª©¤j«üÂI

Hsieh

¦^´_ 1# luffyzoro

¿ý»s¥¨¶°ªº¤½¦¡¬O¥HR1C1°Ñ·Ó§Î¦¡
R«á­±ªº¼Æ­È¬Oªí¥Ü¦C¦ì¡A·íR«á­±¨S¦³¼Æ­Èªí¥Ü»P¤½¦¡©Ò¦bÀx¦s®æ¦P¦C¡A¼Æ­È¥H¤¤¬A¸¹¼Ð¥Üªí¥Ü¬Û¹ï°Ñ·Ó©ó¤½¦¡©Ò¦bÀx¦s®æªº¦C¼Æ¡C
C«á­±ªº¼Æ­È¬Oªí¥ÜÄæ¦ì¡A·íC«á­±¨S¦³¼Æ­Èªí¥Ü»P¤½¦¡©Ò¦bÀx¦s®æ¦PÄæ¡A¼Æ­È¥H¤¤¬A¸¹¼Ð¥Üªí¥Ü¬Û¹ï°Ñ·Ó©ó¤½¦¡©Ò¦bÀx¦s®æªºÄæ¼Æ¡C   
¦Ó¤½¦¡¦bVBA¤¤¬O¤@­Ó¦r¦ê«¬ºAªº¸ê®Æ
¨Ò¦p:R2C24³o¬O«ü¨ìX2Àx¦s®æ
­Y¬OÄæ¦ì­nÅÜ°Ê¡A¥ý¥HÅܼƫü©wÄæ¦ìµM«á±a¤J¦r¦ê
k=26
"R2C" & k
³o¼Ë±o¨ì¦r¦ê´N¬OR2C26