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

[µo°Ý] ½Ð°Ý¦p¦óÅý¥X¨Óªº¤å¦r¤£¦PÃC¦â¤ÎÂà²Åé¦r©ÎªÌÁcÅé¦r

[µo°Ý] ½Ð°Ý¦p¦óÅý¥X¨Óªº¤å¦r¤£¦PÃC¦â¤ÎÂà²Åé¦r©ÎªÌÁcÅé¦r

Worksheets(customer).Cells(cnt, 10).Value =  "µo²¼ª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 18).Value & "          ½c¼Æ:" & Worksheets("Oracle").Cells(I, 41).Value & "               Âd¸¹:" & Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0) & "               ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value ¡¨

½Ð°Ý¡G
¦p¤WªºVBA, ¦pªG·Q¥X¨Óªº®ÄªG¬Y¨Ç¦r¦³ÃC¦â¡A¥i¥H¶Ü¡H
¤ñ¦p ¡G
"µo²¼ª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 18).Value & "  ³o¬qªº¤å¦r­n¬õ¦â
"               ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value ¡¨³o¬qªº¤å¦r­nÂŦâ

ÁÙ¦³¥i§_¥X¨Ó«á§â©Ò¦³¤å¥óÂà²Åé¦r©ÎªÌÁcÅé¦r¡H

¦^´_ 1# 198188
´«¦â¥i§Q¥Î Range.Characters ; Ác²Âà´«§Ú¤£·|
  1. Sub Test()
  2.   Dim s1 As String, s2 As String
  3.   
  4.   s1 = "µo²¼ª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(i, 18).Value
  5.   s2 = "               ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(i, 26).Value
  6.   s = s1 & "          ½c¼Æ:" & Worksheets("Oracle").Cells(i, 41).Value & "               Âd¸¹:" & Split(Worksheets("Oracle").Cells(i, 42).Value, "/")(0) & s2
  7.   
  8.   With Worksheets(customer).Cells(cnt, 10)
  9.     .Value = s
  10.     .Characters(InStr(s, s1), Len(s1)).Font.Color = vbRed
  11.     .Characters(InStr(s, s2), Len(s2)).Font.Color = vbBlue
  12.   End With
  13. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# 198188
¦^´_ 2# stillfish00
®M¥Î stillfish00 ¤j¤jªºµ{¦¡½X¡G
  1. Dim wrdApp As Object

  2. Sub Test()
  3.     Dim s1 As String, s2 As String
  4.   
  5.     Set wrdApp = CreateObject("Word.Document")
  6.     Application.ScreenUpdating = False
  7.    
  8.     s1 = "µo²¼ª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(i, 18).Value
  9.     s2 = "               ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(i, 26).Value
  10.     s = s1 & "          ½c¼Æ:" & Worksheets("Oracle").Cells(i, 41).Value & "               Âd¸¹:" & Split(Worksheets("Oracle").Cells(i, 42).Value, "/")(0) & s2
  11.   
  12.     With Worksheets(customer).Cells(cnt, 10)
  13.         .Value = s
  14.         .Characters(InStr(s, s1), Len(s1)).Font.Color = vbRed
  15.         .Characters(InStr(s, s2), Len(s2)).Font.Color = vbBlue
  16.    
  17.         ' 1 ÁcÂಠ 0 ²ÂàÁc
  18.         .Value = T_S_Cvt(.Value, 1)
  19.     End With
  20.    
  21.     Application.ScreenUpdating = True
  22.     wrdApp.Close False
  23. End Sub

  24. Public Function T_S_Cvt(strData, bytOption) As String
  25.     With wrdApp
  26.         .Content = strData
  27.         ' ½Õ¥Î Word TCSCConverter ¤èªk¨ÓÂà´«Ác²Åé
  28.         .Range.TCSCConverter bytOption, True, True
  29.         T_S_Cvt = .Content
  30.     End With
  31. End Function
½Æ»s¥N½X

TOP

  1. Sub SCHPN()
  2.   Dim I As Double
  3.   Dim cnt As Double
  4.   Dim customer As String
  5.   Dim A As String
  6.   Dim LastRec As Integer
  7.   Dim k As Integer
  8.   Dim j As Integer
  9.   Dim l As Integer
  10.   Dim m As Double
  11.   Dim z As Integer
  12.   Dim y As Integer
  13.   Dim e As Integer
  14.   Dim d As Integer
  15.   Dim f As Integer
  16.   Dim FRng As Range
  17.   Dim s1 As String, s2 As String, s3 As String
  18.   Dim wrdApp As Object

  19.   j = Worksheets("Oracle").Range("A" & Worksheets("Oracle").Rows.Count).End(xlUp).Row
  20.   l = Worksheets("Follower").Range("A" & Worksheets("Follower").Rows.Count).End(xlUp).Row
  21.   z = Worksheets("Rule").Range("B" & Worksheets("Rule").Rows.Count).End(xlUp).Row
  22.   y = 39
  23.   Do
  24.    Set wrdApp = CreateObject("Word.Document")

  25.     Application.ScreenUpdating = False
  26.   f = 0
  27.   d = 0
  28.   e = 0
  29.   cnt = 74  

  30.   A = Worksheets("Rule").Cells(y, 2).Value
  31.   customer = A
  32.   Worksheets("SchPN").Copy After:=Worksheets(Worksheets.Count)
  33.   Worksheets(Worksheets.Count).Name = customer
  34.   Worksheets(customer).Range("H5").Value = Date
  35. For I = 2 To j
  36.   If Worksheets("Oracle").Cells(I, 5).Value = customer And Worksheets("Oracle").Cells(I, 19).Value > 0 And Trim(Worksheets("Oracle").Cells(I, 19).Value) <> "" And Trim(Worksheets("Oracle").Cells(I, 15).Value) = "" And (Trim(Worksheets("Oracle").Cells(I, 20).Value) = Trim(Worksheets("Oracle").Cells(I, 18).Value) Or Trim(Worksheets("Oracle").Cells(I, 20).Value) = "") Then
  37.   If Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 1 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 2 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 3 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 4 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 5 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 6 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 7 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 8 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 9 Then
  38.   Set FRng = Worksheets(customer).Range("A:A").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
  39.   If InStr(UCase(Worksheets("Oracle").Cells(I, 2).MergeArea(1)), "SPOT") = 0 And InStr(UCase(Worksheets("Oracle").Cells(I, 2).MergeArea(1)), "³q内´µ现货") = 0 Then
  40.   If FRng Is Nothing Then
  41.   Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 1).Value
  42.   Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 24).Value
  43.   Worksheets(customer).Cells(cnt, 3).Value = Worksheets("Oracle").Cells(I, 14).Value
  44.   If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 8).Value = "«Ý³qª¾" Else If Trim(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) = "" Then Worksheets(customer).Cells(cnt, 8).Value = "«Ý³qª¾" Else Worksheets(customer).Cells(cnt, 8).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)
  45.   Worksheets(customer).Cells(cnt, 19).Value = Left((Worksheets("Oracle").Cells(I, 22).Value), 3)
  46.   Worksheets(customer).Cells(cnt, 9).Value = "USD" & Format(Worksheets("Oracle").Cells(I, 19).Value * Worksheets(customer).Cells(cnt, 19).Value, "#.00")
  47.   Worksheets(customer).Cells(cnt, 19).Value = ""
  48.   
  49.   If Left((Worksheets("Oracle").Cells(I, 1).Value), 1) = 8 Then
  50.     s1 = Left((Worksheets("Oracle").Cells(I, 22).Value), 3) & "©wª÷"
  51.     s2 = "          ¥N²z:" & Worksheets("Oracle").Cells(I, 10).Value
  52.     s3 = " ¬ü°ê "
  53.     S = s1 & "               ¦X¦Pª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 19).Value & "          ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value & s2 & s3
  54.     With Worksheets(customer).Cells(cnt, 10)
  55.     .Value = S
  56.     .Characters(InStr(S, s1), Len(s1)).Font.Color = vbRed
  57.     .Characters(InStr(S, s2), Len(s2)).Font.Color = -16777024
  58.     .Characters(InStr(S, s3), Len(s3)).Font.Color = vbBlue
  59.     .Value = T_S_Cvt(.Value, 1)
  60.     End With
  61.   End If
  62.   
  63.   If Left((Worksheets("Oracle").Cells(I, 1).Value), 1) = 2 Then
  64.     s1 = Left((Worksheets("Oracle").Cells(I, 22).Value), 3) & "©wª÷"
  65.     s2 = "          ¥N²z:" & Worksheets("Oracle").Cells(I, 10).Value
  66.     s3 = " ·ç¤h"
  67.     S = s1 & "               ¦X¦Pª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 19).Value & "          ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value & s2 & s3
  68.    With Worksheets(customer).Cells(cnt, 10)
  69.    .Value = S
  70.    .Characters(InStr(S, s1), Len(s1)).Font.Color = vbRed
  71.    .Characters(InStr(S, s2), Len(s2)).Font.Color = -16777024
  72.    .Characters(InStr(S, s3), Len(s3)).Font.Color = vbBlue
  73.    .Value = T_S_Cvt(.Value, 1)
  74.    End With
  75.   End If
  76.   
  77.   Worksheets(customer).Cells(cnt, 12).Value = Worksheets("Oracle").Cells(I, 19).Value
  78.   Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 22).Value
  79.   Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 15).Value
  80.   If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 28).Value Else Worksheets(customer).Cells(cnt, 13).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:C"), 2, False)
  81.   Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 27).Value
  82.   If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 16).Value = "" Else Worksheets(customer).Cells(cnt, 16).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:Q"), 17, False)
  83.   If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 17).Value = "" Else Worksheets(customer).Cells(cnt, 17).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:E"), 5, False)
  84.   If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 18).Value = Worksheets("Oracle").Cells(I, 25).Value Else Worksheets(customer).Cells(cnt, 18).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:X"), 24, False)
  85.   Worksheets(customer).Cells(cnt, 19).Value = Worksheets("Oracle").Cells(I, 18).Value
  86.   If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then Worksheets(customer).Cells(cnt, 5).Value = "¨S¦³" Else Worksheets(customer).Cells(cnt, 5).Value = "¦³"
  87.   If Worksheets(customer).Cells(cnt, 5).Value = "¨S¦³" Then Worksheets(customer).Cells(cnt, 5).Font.Color = vbRed
  88.   Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 7).Value
  89.   Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
  90.   If Trim(Worksheets("Oracle").Cells(I, 27).Value) = "" Then Worksheets(customer).Cells(cnt, 7).Value = "«Ý³qª¾" Else Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 27).Value
  91.   With Worksheets(customer).Range(Worksheets(customer).Cells(cnt, 1), Worksheets(customer).Cells(cnt, 10))

  92.         .Borders.LineStyle = 1

  93.         .Borders.LineStyle = 1

  94.         .Borders.ColorIndex = 0

  95.         .BorderAround , 2, 0

  96.     End With
  97.   cnt = cnt + 1
  98.   d = d + 1
  99.   e = e + 1
  100.     End If
  101.    End If
  102.     End If
  103.    End If
  104.     Next I
  105.   Loop Until y > z
  106. Application.ScreenUpdating = True

  107.     wrdApp.Close False
  108. End Sub
  109. Public Function T_S_Cvt(strData, bytOption) As String

  110.     With wrdApp

  111.         .Content = strData

  112.         ' ½Õ¥Î Word TCSCConverter ¤èªk¨ÓÂà´«Ác²Åé

  113.         .Range.TCSCConverter bytOption, True, True

  114.         T_S_Cvt = .Content

  115.     End With

  116. End Function
½Æ»s¥N½X
¦^´_ 3# c_c_lai

.content = strData ¥X²{RUN-TIME ERROR'424': Object required

TOP

¦^´_ 4# 198188
¦b§A¥Ø«eªºµ{¦¡½X¬Ý¨Ó¡A¤£¥X¿ù¤~©Ç¡I
½Ð¥J²Ó¬Ý¬Ý½d¨Ò¡A°ÝÃD¥X¦b­þ¸Ì¡H
§A©I¥sªºª«¥ó®Ú¥»¤£¦s¦b°Ú¡C

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2014-7-7 20:26 ½s¿è

¦^´_ 4# 198188
·Q·QÁÙ¬Oª½±µµ¹§Aµª®×§a¡I
¬°¤F­n³B²z¡yÂà²Åé¦r©ÎªÌÁcÅé¦r¡z¡A§Ú­Ì¤Þ¥Î¤F
Word ªºª«¥ó¡G
  1. Set wrdApp = CreateObject("Word.Document")
½Æ»s¥N½X
¦ý¦b³]©w¤§«e¡A¥²¶·­n¹w¥ý«Å§i¤@­Ó wrdApp ª«¥ó (Object)¡A
¦b§A¥Ø«eªºµ{¦¡½XÁöµM¦³«Å§i¦¹ÅܼơG
  1.     Dim wrdApp As Object
½Æ»s¥N½X
¦ý«o©¿²¤¤F .Value = T_S_Cvt(.Value, 1) ªº Function Call ¤ºªº¤º®e¤Þ¥Î¡A
  1. Public Function T_S_Cvt(strData, bytOption) As String
  2.     With wrdApp
  3.         .Content = strData
  4.         ' ½Õ¥Î Word TCSCConverter ¤èªk¨ÓÂà´«Ác²Åé
  5.         .Range.TCSCConverter bytOption, True, True
  6.         T_S_Cvt = .Content
  7.     End With
  8. End Function
½Æ»s¥N½X
µ²ªG°£¤F SCHPN() ¥~«o§ä¤£µÛ wrdApp ªºª«¥ó¡A
¨ì¦¹§A¤w¸g¤F¸Ñ°ÝÃD¤§©Ò¦b¤F¶Ü¡H

¦pªGÁÙ¤£©úÁA¡A½Ðª`·N§A¬O¦b¦ó³B«Å§i wrdApp ªº¡C
¥Ø«e wrdApp ¥u¯à¦b  SCHPN() ùØÀY¥i³QÃѧO¡A¥X¤F SCHPN()
¥~«K·|²£¥Í¡G
  1. RUN-TIME ERROR '424': Object required
½Æ»s¥N½X
¦]¬° T_S_Cvt() ¤ºµLªkÃѧO wrdApp¡A ©Ò¥H¥¦¤~§i¶D§A
¡y¦¹³B»Ý­nª«¥ó¡z¡C
Á`µ²¡A§A¥²¶·¦b SCHPN() ¤WÀY»Ý§i¡G
  1. Dim wrdApp As Object
  2. Sub SCHPN()
½Æ»s¥N½X
¦Ó«D¦b SCHPN() ùØÀY«Å§i¡F¦p¦¹ wrdApp ¤~¯à¦¨¬°¡y¥~°ìÅܼơz¡C
T_S_Cvt() ¤~±o¥H¥¿½T°õ¦æ¡C

TOP

¦^´_ 4# 198188
¦A¸É¥R»¡©ú¡G

TOP

¦^´_ 3# c_c_lai


    ©ú¥Õ¡A¤w¸g¸Ñ¨M¡AÁÂÁÂ

TOP

¦^´_ 8# 198188
¦pªG§Aı±o wrdApp µL¥²­n«Å§i¬°¡y¥~°ìÅܼơz(Global Variable)
¨º¥ç¥i­×¥¿¦¨¬°¡y°Ï°ìÅܼơz(Local Variable)¡A¦ý¬O¤Þ¥Î¤§¥\¯à¨ç¦¡
«h¥²¶·¥[¥H­×§ï¦¨¡A§Y¥²¶·¦P®É±a¤J wrdApp ª«¥ó (¤Þ¤J°Ñ¼Æ)¡G
  1. Public Function T_S_Cvt(wrdApp, strData, bytOption) As String
  2.     With wrdApp
  3.         .Content = strData
  4.         ' ½Õ¥Î Word TCSCConverter ¤èªk¨ÓÂà´«Ác²Åé
  5.         .Range.TCSCConverter bytOption, True, True
  6.         T_S_Cvt = .Content
  7.     End With
  8. End Function
½Æ»s¥N½X
¦p¦¹¡A«K¯àª½±µ¦b©I¥s¥¦ªº¨ç¦¡¤º¥hª½±µ¨Ï¥Î¥¦¤F¡C
  1. Sub Test()
  2.     Dim wrdApp As Object
  3.     Dim s1 As String, s2 As String
  4.   
  5.     Set wrdApp = CreateObject("Word.Document")
  6.     Application.ScreenUpdating = False
  7.    
  8.     s1 = "µo²¼ª÷ÃB¡GUSD350.00"
  9.     s2 = "               ¥Øªº´ä¡G¥xÆW°ò¶©´ä"
  10.     s = s1 & "          ½c¼Æ:120½c" & "               Âd¸¹:LSK122345P78" & s2
  11.   
  12.     With Worksheets("¤u§@ªí1").Cells(1, 1)
  13.         .Value = s
  14.         ' 1 ÁcÂಠ 0 ²ÂàÁc
  15.         .Value = T_S_Cvt(wrdApp, .Value, 1)
  16.         ' .Value = T_S_Cvt(wrdApp, .Value, 0)
  17.         
  18.         .Characters(InStr(s, s1), Len(s1)).Font.Color = vbRed
  19.         .Characters(InStr(s, s2), Len(s2)).Font.Color = vbBlue
  20.     End With
  21.    
  22.     Application.ScreenUpdating = True
  23.     wrdApp.Close False
  24. End Sub
½Æ»s¥N½X
¯¬§A¶¶·N¡I

TOP

¦^´_ 9# c_c_lai


    §Úµo²{¥Î³o­Ó¤èªk¡A·|¦³­Ó°ÝÃD¥X¨Ó¡A´N¬O¤£Â_»s³y¤@¨ÇÁôÂêºword¡A¨C¦¸Ãö¾÷³£·|Åã¥Ü­n¤£­nÀx¦sword.

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD