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

[µo°Ý] EXCEL 2007 VBA ¦bEXCEL 2010 °õ¦æ¥X°ÝÃD

[µo°Ý] EXCEL 2007 VBA ¦bEXCEL 2010 °õ¦æ¥X°ÝÃD

½Ð°Ý¦U¦ì¤j¤j

³ÌªñÀ°­Ó«È¤á§Ë¤F¤@­Ó¥¨¶°µ{¦¡

§Ú¥»¨­§@·~¬O¥Î2007ª©EXCEL¼¶¼g¡AÀx¦s¦¨2003¥i°õ¦æªºª©¥»

¦ý¨ì«È¤áºÝ¡Aµo²{¬O2010ª©ªºEXCEL

°ò¥»¤W§Úı±o»yªkÀ³¸Ó¨S¨Ï¥Î¨ì2010¤£¯à¨Ï¥Î¨ìªº³¡¤À(¦]¬°³£ÆZ³æ¯Âªº)

¥D­n¼g¨ìªº¥\¯à
1. ¶}±Ò­n³B²zªº¨Ó·½ÀÉ®×
2. §R°£¨Ó·½Àɤ¤¤£¥²­nªºÀx¦s®æ
3. §ïÅܨӷ½ÀÉ¹Ïªíªº®æ¦¡
(­ì¥»¨Ó·½ÀÉ´N¦³Chart¤F¡A¥u¬O§Ú§R¤F¤£¥²­nªºÀx¦s®æ¡A©Ò¥H¹Ïªí·|¦³ÂIÁY´î¡F¥B¶·­×§ï³¡¤À®æ¦¡¡A¹³¬O½u¼e¡B¼Ð°Oªº¼Ë¦¡µ¥µ¥)
4. ³Ì«á¸õ¥X³B²z§¹¦¨ªº°T®§µøµ¡


«È¤áºÝ¶}±Ò¨Ó·½ÀÉ«á¡A¥u¸õ¥X³Ì«á³B²z§¹ªº°T®§µøµ¡¡C

«e¥b¬qªº§R°£³¡¤À¤Î¹Ïªí³¡¤À³£¨S°õ¦æ¨ì@@

¤]¨S¦³¿ù»~°T®§


«á¨Ó§Ú¹ê»Ú¥Î¤F2010ª©ªºEXCEL°õ¦æ¡A§¹¥þ¸ò¥Î2007°õ¦æ¤@¼Ë¡Aµ²ªG¥¿±`

¤£ª¾¬O¤£¬O«È¤á¤½¥q¨º³]©w¤F¤°»ò???  


·Q½Ð°Ý¦U¦ì¤j¤j¡A³o¤èªk¦³¨S¦³¤°»ò³B²z¤è¦¡? ÁÙ¬O¥i¯à»Ý­n°µ¤°»ò³]©w???

P.S. «È¤á¬O¥Î­^¤åª©ªº2010


·Ð½ÐÀ°¦£  ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

¤p§Ìªþ­ÓÀÉÅý¤j®a¸Õ¸Õ

³Â·ÐÀ°§Ú§ì­Ó°ÝÃD@@

µ{¦¡¬O0424¡A¥t¤@­ÓÀɮ׬O»Ý­n¶×¤JªºÀÉ®×

§Ú¥uªþ¤F²Ä¤@­Ó«ö¶s­n¶}±ÒªºÀÉ®×

²Ä¤G±i¹Ï¬O¶]§¹«áÀ³¸Ó¥X²{ªºµe­±

TKS~
forum.rar (156.42 KB)
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 2# li_hsien
  1. Sub w_report_Click()
  2.     '-----¶}±ÒWªí³æ-----   
  3.     Dim program_file As String, filename As String, w_file As String   
  4.     program_file = Left(ActiveWorkbook.Name, Application.Find(".", ActiveWorkbook.Name) - 1)
  5.     filename = Application.GetOpenFilename   
  6.     On Error GoTo HandleErr  '§A§Æ±æµ{¦¡½X¦³¿ù»~¨ì HandleErr
  7.      A w_file '¦ý°õ¦æ¦¹µ{§Ç,¦p¦³¿ù»~·|¦^¨ìHandleErr,§A¤£©ö°»¿ùªº
½Æ»s¥N½X
  1. Sub A(w_file)
  2.     Dim this_week As Integer, end_col As Integer, end_row As Integer, i As Integer, delete_col As Integer
  3.    'w_file->W1414_GF108_CP_weekly_report
  4.    'w_file ¥¿½T¬° W1414_GF108_CP_weekly_report.xls
  5.     With Workbooks(w_file).Worksheets(1)  'Workbooks(w_file)¤~¤£·|¥X¿ù
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE

ª©¤j §A»¡ªº°»¿ù³B²z

§Ú«á¨Ó¦³·Q¨ì  «¢«¢

©Ò¥H³£§â¥LÃö±¼

ª½±µÅý¿ù»~Åã¥Ü¥X¨Ó

¥H¤U¬O¸g«È¤á¦A¦¸´ú¸Õªºµ²ªG¡A³Â·ÐÀ°§Ú¬Ý¬Ý¡A°ÝÃDÂI³£¤@¼Ë@@
¦n¹³CHARTªº³B²z¦³°ÝÃD



¤£¹L¶}Àɮרº­Ó¦n¹³¨S¦³°ÝÃD ¡A¦]¬°§ÚÀÉ®×ÁÙ¬O¥i¥H¶}±Ò°õ¦æ­C¡A¥u¬O¹Ïªí¸òÀx¦s®æ³B²z¦³°ÝÃD

¦Ó¥B§Ú¤@ª½¤£¸Ñªº¬O¡A§Ú§ä¤F¤T­Ó¤@¼Ë¥Î2010ªºªB¤ÍÀ°§Ú´ú¸Õ
³£¤@¼Ë¥¿½TOK


¤£ª¾¹D¬O¤£¬O­^¤åª©ªº¦³®t²§ ÁÙ¬O»Ý­n°µ¤°»ò³]©w@@


·Ð½ÐÀ°§Ú´ú¸Õ¬Ý¬Ý  ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 4# li_hsien
§Úªº¬O2003ª©,°õ¦æªþÀɵ{¦¡½X¬O2003ª©¥H¤Wª©¥»¼gªº.
¿ù»~ÂI³£¬O "¤£¤ä´©¦¹ÄݩʩΤèªk" ,¥i¥H¼Ð¥X¿ù»~ÂI¬O­þ¸Ì,¬Ý¬Ý¦p¦ó¸Ñ¨M
  1. Sub A_chart(w_file)
  2.     'Dim C As ChartObject
  3.     'Set C = Workbooks(w_file).Worksheets(1).ChartObjects("Chart 3")
  4.     With Workbooks(w_file).Worksheets(1).ChartObjects("Chart 3").Chart
  5.         '.ChartArea.Format.Line.Visible = msoFalse   '2003¿ù»~
  6.         .ChartArea.Border.LineStyle = msoFalse   '§ï¦¨³o¼Ë¹ï¶Ü?
  7.         .SizeWithWindow = True
  8.         '.ChartArea.Width = 362.5 '¼e«× ­ì1449.25      '2003¿ù»~
  9.         '.ChartArea.Height = 124.75  '°ª«× ­ì178.75    '2003¿ù»~
  10.         .Parent.Width = 362.5  ' *** ³o¼Ë¹ï¶Ü? ***
  11.         .Parent.Height = 124.75
  12.         
  13.         With .PlotArea
  14.             .Height = 102.148661417323 'ø¹Ï°Ï°ª«× ­ì120.97031496063
  15.             .Width = 342.872283464567  'ø¹Ï°Ï¼e«× ­ì358.980866141732
  16.             .Left = 10 'ø¹Ï°Ï¦ì²¾
  17.             .Top = 10  'ø¹Ï°Ï¦ì²¾
  18.         End With

  19.         '¹Ïªí¼ÐÃD
  20.         With .ChartTitle
  21.             If IsError(Application.Find(".", Worksheets(2).Range("G2"))) = False Then
  22.                 .Text = Left(ActiveChart.ChartTitle.Text, Application.Find(" ", ActiveChart.ChartTitle.Text) - 1) _
  23.                     & "-" & Mid(Worksheets(2).Range("G2"), Application.Find(".", Worksheets(2).Range("G2"), Application.Find(".", Worksheets(2).Range("G2")) + 1) + 1, 3) _
  24.                     & " " & "wk" & Right(Worksheets(2).Range("A2"), 4) & " " & Right(ActiveChart.ChartTitle.Text, 15)
  25.             ElseIf IsError(Application.Find(".", Worksheets(2).Range("G2"))) = True Then
  26.                 .Text = Left(ActiveChart.ChartTitle.Text, Application.Find(" ", ActiveChart.ChartTitle.Text) - 1) _
  27.                     & "-" & Mid(Worksheets(2).Range("G2"), Application.Find("_", Worksheets(2).Range("G2"), Application.Find("_", Worksheets(2).Range("G2")) + 1) + 1, 3) _
  28.                     & " " & "wk" & Right(Worksheets(2).Range("A2"), 4) & " " & Right(ActiveChart.ChartTitle.Text, 15)
  29.             End If
  30.             .Font.Size = 6.5  '¼ÐÃD¦rÅé¤j¤p
  31.             .Font.Name = "Arial" '¼ÐÃD¦r«¬
  32.             .Left = 123  '¼ÐÃD¦ì²¾
  33.         End With
  34.         With .Legend
  35.             .Position = xlLegendPositionBottom  '¹Ï¨Ò¦C©³³¡
  36.             '.Legend.Top = 300   '¹Ï¨Ò¦ì²¾
  37.             .Top = Range("A21").Top
  38.             .Font.Size = 6.5 '¹Ï¨Ò¦rÅé¤j¤p
  39.             .Font.Name = "Arial" '¹Ï¨Ò¦r«¬
  40.         End With
  41.         With .SeriesCollection("Yield")
  42. '            .SeriesCollection(4).Border.ColorIndex = 18 '½u±øÃC¦â
  43.             .Border.Color = 5066944 '½u±øÃC¦â
  44.             '.SeriesCollection("Yield").Format.Line.Weight = 1 '½u±ø&¼Ð°O¼e¬°1
  45.             '.Format.Line 2003¿ù»~ ***********
  46.             .Border.Weight = 1            '.LineStyle = 0
  47.             .Border.Weight = -4138   '¥u§ïÅܽu±ø
  48.             .MarkerStyle = 2  '¼Ð°OµÙ§Î
  49.             .MarkerBackgroundColorIndex = 2 '¼Ð°OÃC¦â(¥Õ)
  50.             .MarkerSize = 5    '¼Ð°O¤j¤p5
  51.             .MarkerForegroundColor = 5066944    '¼Ð°O¥~®Ø
  52.             .ApplyDataLabels  'Åã¥Ü¸ê®Æ¼ÐÅÒ
  53.             .DataLabels.Font.Size = 5  '¤W¦C¼Æ­È¦rÅé¤j¤p
  54.             .DataLabels.Font.Name = "Arial" '¤W¦C¼Æ­È¦r«¬
  55.             
  56.         End With
  57.         '.SeriesCollection("Yield").Select    '¤£»Ý­n
  58.        ' .SetElement (msoElementDataLabelTop) '¸ê®Æ¼ÐÅÒ¸m¤W '2003¨S³o¿ï¶µ
  59.         .Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 6.5 'Y¶b¼ÐÃD¤j¤p
  60.         .Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Arial" 'Y¶b¼ÐÃD¦r«¬
  61.         .Axes(xlValue, xlPrimary).AxisTitle.Left = -3  'Y¶b¼ÐÃD¦ì²¾

  62.         .Axes(xlCategory).TickLabels.Font.Size = 5  'X¶b¦rÅé¤j¤p
  63.         .Axes(xlCategory).TickLabels.Font.Name = "Arial"    'X¶b¦r«¬
  64.         
  65.         .Axes(xlValue).TickLabels.Font.Size = 6.5  'Y¶b¦rÅé¤j¤p
  66.         .Axes(xlValue).TickLabels.Font.Name = "Arial"  'Y¶b¦r«¬
  67.         
  68.         .SeriesCollection("Yield").DataLabels.Font.Size = 5  '¤W¦C¼Æ­È¦rÅé¤j¤p
  69.         .SeriesCollection("Yield").DataLabels.Font.Name = "Arial" '¤W¦C¼Æ­È¦r«¬
  70.     End With
  71. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE


ÁÂÁª©¤j

Âà´«¦¨³o¼Ëªº¼gªk§Ú¦Û¤v´ú¸Õ¤]OK

¤£¹L´«¨ì¹ï¤è¨ºÃäÁÙ¬O¤@¼Ë¤£¦æ@@

¥B¦pªGWith Workbooksªº¤è¦¡Åܦ¨¦³°ÆÀɦW´N¤£¦æ¤F= =

¥i¬O§Ú¦Û¤v¹q¸£´ú¸Õ¦³°ÆÀɦW ¨S°ÆÀɦW³£¥i¥H

¤]¤Ó©_©Ç¤F
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-4-25 13:25 ½s¿è

¦^´_ 6# li_hsien
¦Û¤v¹q¸£´ú¸Õ¦³°ÆÀɦW ¨S°ÆÀɦW³£¥i¥H (¤]¬O2003ª©¶Ü?,¥i¥H¶Ü?)
  1. 'Workbooks(program_file).Worksheets(1).w_file = ""  '2003³o¸Ì¬O¿ù»~ªº
  2.       w_file = ""  
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ li_hsien ©ó 2014-4-25 14:43 ½s¿è

¦^´_ 7# GBKEE

§ÚÀô¹Ò¬O2007ªº¡A¦³µL°ÆÀɦW³£¥i¥H

¤£¹L§Úµ¹«È¤á(2010­^¤åª©)¡A¦³¥[°ÆÀɦW¦n¹³·|¿ù»~


²{¦b¯u¤£ª¾¬°¤°»ò«È¤á¨ºÃä2010­^¤åª©ªº¬°¤°»ò¤£¯à°õ¦æ@@

©ú©ú§Ú¥Î¦Û®a§O¥x¹q¸£(¤]¬O2010)´N¥i¥H¶]¥X¹Ïªí

µ¹¨ä¥LªB¤Í´ú¸Õ¤]³£¥i¥H

¤£ª¾¹D¬O­þ¬qµ{¦¡©Î¬O³]©w¥X¤F°ÝÃD@@
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-4-25 14:51 ½s¿è

¦^´_ 8# li_hsien
¨º¤£¦n·N«ä§Ú¥u¦³2003ª©À°¤£¤W¦£¤F.»Ý¦³½Ð2010ª©ªºÀ°¦£.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE


ª©¤j ¨SÃö«Y

ÁÂÁ§A!!!

¥­®É¤w¸gÀ°¦£¸Ñ¨M¤F¤£¤Ö°ÝÃD

§Ú¤]¾Ç¨ì¤F¤£¤ÖªF¦è!!!

TKS~
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD