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

[µo°Ý] ¿ï¾ÜÀÉ®×Âন¤å¦rÀÉ°ÝÃD.

¦^´_ 20# ­ã´£³¡ªL
·PÁÂ:­ã´£³¡ªL,«ü¾É.¥N½Xµ¹ªì¾ÇªÌ§ó²M·¡¾Ç²ß,·P®¦.
§ù¤p¥­

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-11-3 07:20 ½s¿è

¦^´_ 20# ­ã´£³¡ªL


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
¥H¤U¬OÃjÀ´ªº¤ß±oµù¸Ñ,½Ð«e½ú¦A«ü¾É

Sub Test_a1()
Dim xFile$, T1$, T2$, BN$, SN$, P$, PP$, Sx%, Cn, Arr, i&
'¡ô«Å§iÅܼÆ:(xFile,T1,T2,BN,SN,P,PP)¬O¦r¦êÅܼÆ,Sx¬Oµu¾ã¼ÆÅܼÆ,
'(Cn,Arr)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼ÆÅܼÆ

ChDir ThisWorkbook.Path
'¡ôChDir "D:\"  '«ü©w¶}±ÒÀɮתº¸ô®|
xFile = Application.GetOpenFilename("ExcelÀÉ,*.XLS*")
'¡ô¥OxFile³o¦r¦êÅܼƬO Åã¥Ü¼Ð·Çªº [¶}±ÒÂÂÀÉ] ¹ï¸Ü¤è¶ô¡A±q¨Ï¥ÎªÌ¨ú±oÀɮתº¦WºÙ
If xFile = "False" Then Exit Sub
'¡ô¦pªGxFileÅܼƬO¦r¦ê"False",¥Nªí¨S¦³¿ï¨úÀÉ®×,µ²§ôµ{§Ç°õ¦æ
T1 = [b1] & Mid([b2], 2, 6) & [b3] & [b4]
'¡ô¥Omcs1³o¦r¦êÅܼƬO [b1]Àx¦s®æ­È,³s±µ[b2]Àx¦s®æ­È¨ú²Ä2¦r¤¸¶}©lªº6¦r¤¸¦r¦ê,
'¦A³s±µ[b3]Àx¦s®æ­È,³Ì«á³s±µ[b4]Àx¦s®æ­È,²Õ¦X¦¨ªº¦r¦ê (¤å¦r¦ê«e½X)

T2 = [b5]
'¡ô¥OT2³o¦r¦êÅܼƬO[b5]Àx¦s®æ­È (¦s´£´Ú¥N¸¹)
Sx = Val([b7])
'¡ô¥OSx³o¦r¦êÅܼƬO [b7]Àx¦s®æ­ÈÂà¤Æªº¾ã¼Æ­È («ü©w²Ä?±i¤u§@ªí(¨Ì¥ª¦Ó¥k¶¶§Ç))
Cn = Split([b6], ",")
'¡ô¥OCn³o³q¥Î«¬ÅܼƬO [b6]Àx¦s®æ¥H ³r¸¹¤À³Î¦¨ªº¤@ºû°}¦C («ü©wÄæ¦ì)
'---------------------------------

On Error Resume Next
'¡ô¥Oµ{§Ç°õ¦æ¤£°»¿ù
With GetObject(xFile)
'¡ô¥H¤U¬OÃö©ó¥HxFileÅܼƥÎGetObject¨ç¦¡ ¦^¶Çª«¥ó(¬¡­¶Ã¯)ªºµ{§Ç
'ÁÂÁ «e½ú«ü¾É³o¨ç¦¡ªº¥Îªk
'°õ¦æ¨ì³o¸Ì¨Ã¨S¦³¬Ý¨ì¶}±Ò¬¡­¶Ã¯,«Ü¯«©_!«á¨Ó¬d¬Ý¨ì¬¡­¶Ã¯µøµ¡³QÁôÂð_¨Ó¤F


'¦pªG¸Ó¬¡­¶Ã¯­ì¥»´N³Q¶}±Ò·|ª½±µ«ü¦V¦¹¬¡­¶Ã¯,¤£·|¦A­«·s¶}±Ò¤@¦¸
https://learn.microsoft.com/zh-t ... /getobject-function
     BN = Split(.Name, ".")(0)
     '¡ô¥OBN³o¦r¦êÅܼƬO ¬¡­¶Ã¯ÀɦW(¥h°£°ÆÀɦW)
     '(¥h°£°ÆÀɦW¤èªk:¥H"."²Å¸¹¤À³Î¬¡­¶Ã¯¦WºÙ,¨ú0¯Á¤Þ¸¹°}¦C­È)

     SN = .Sheets(Sx).Name
     '¡ô¥OSN³o¦r¦êÅܼƬO¬¡­¶Ã¯¸Ì²ÄSxÅܼƯÁ¤Þ¸¹¤u§@ªí¦WºÙ
     Arr = Range(.Sheets(Sx).[g1], .Sheets(Sx).UsedRange) '¸ê®Æ½d³ò(§tGÄæ)
     '¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H¤w¨Ï¥ÎÀx¦s®æ(§tGÄæ)½d³òÀx¦s®æ­È±a¤J
     .Close 0
     '¡ô¥O¬¡­¶Ã¯Ãö³¬(¤£Àx¦s)
End With
On Error GoTo 0
'¡ô¥Oµ{§Ç«ì´_°»¿ù
If SN = "" Then MsgBox "«ü©w¤u§@ªí¤£¦s¦b!  ": Exit Sub
'¡ô¦pªGSNÅܼƬO ªÅªº!´N¸õ¥X´£µøµ¡~~~ ,µ²§ôµ{§Ç°õ¦æ
'----------------------------------

For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q2 ¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
    P = T1 & Arr(i, Cn(0))
    '¡ô¥OP³o¦r¦êÅܼƬO T1ÅܼƳs±µ i°j°é¦C(0¯Á¤Þ¸¹Cn°}¦C­È)ÄæArr°}¦C­È
    P = P & Format(Arr(i, Cn(1)), "00000000000;;#") & "00" & T2
    '¡ô¥OPÅܼƦA³s±µ i°j°é¦C(1¯Á¤Þ¸¹Cn°}¦C­È)ÄæArr°}¦C­È¥HFormat¨ç¦¡¦^¶Ç¦r¦ê,
    '¦A³s±µ "00",Äò³s±µT2ÅܼÆ
    '³o;;#¤À°Ï¬q²{¦bÁÙ¤£¯à²z¸Ñ,»Ý­n§ó¦h¾Ç²ß

    https://learn.microsoft.com/zh-t ... ic-for-applications
    P = P & Left(Arr(i, Cn(2)) & String(29, " "), 29)
    '¡ô¥OPÅܼƦA³s±µ 29­Ó¦r¤¸ªº¦r¦ê:
    'i°j°é¦C(2¯Á¤Þ¸¹Cn°}¦C­È)ÄæArr°}¦C­È ³s±µ29­ÓªÅ¥Õ¦r¤¸,©Ò²Õ¦¨ªº·s¦r¦ê,¨ú¥ª°¼29­Ó¦r¤¸

    If Len(P) = 80 Then PP = PP & IIf(PP = "", "", vbCrLf) & P
    '¡ô¦pªGPÅܼƦr¤¸¼Æ¬O80!´N¥OPP³o¦r¦êÅܼƲּW¥[¦r¦ê(Âk¦ì¦r¤¸´«¦æ²Õ¦X)¶¡¹j
    '¤å¦rÀÉ(.Txt)ÀɻݭnvbCrLf = CHR(13)+CHR(10) ¤~·|´«¦æ

    https://learn.microsoft.com/zh-t ... ellaneous-constants
i01: Next i
If PP = "" Then MsgBox "«ü©w¤u§@ªíµL²Å¦X¸ê®Æ!  ": Exit Sub
'¡ô¦pªGPPÅܼƬOªÅªº!´N¸õ¥X´£µøµ¡~~~ ,µ²§ôµ{§Ç°õ¦æ
'----------------------------------

xFile = ThisWorkbook.Path & "\" & BN & "-Sheets(" & Sx & ").TXT"
Open xFile For Output As #1  'OutputÂл\¸ê®Æ
'¡ô¥H¶¶§Ç¿é¤J¼Ò¦¡¶}±Ò ÀÉ®×
https://learn.microsoft.com/en-u ... help/open-statement
Print #1, PP
'¡ô±NÅã¥Ü®æ¦¡¤Æ¸ê®Æ Âл\¸ê®Æ¼g¤JÀÉ®×
https://learn.microsoft.com/zh-t ... help/printstatement
Close #1
'¡ôÃö³¬ÀÉ®×
MsgBox "¤å¦rÀɤw«Ø¥ß¡G" & xFile
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 20# ­ã´£³¡ªL


    ½Ð±Ð«e½ú
1.Format(Arr(i, Cn(1)), "00000000000;;#")¬O«ü  [¥¿¼Æ;­t¼Æ;0;¥»¤å]
1.1.¦pªGArr(i, Cn(1))­È¬O¥¿¼Æ´N¥H11¦ì¼Æ§e²{(¤£¨¬³¡¤À¥Î0¶ñ¥R¦b¥ª°¼)
1.2.¦pªGArr(i, Cn(1))­È¬O­t¼Æ´N¥HªÅ®æ§e²{
¬O³o¼Ëªº·N¸q¶Ü?

2.½Ð±Ð«e½ú³o³Ì«áªº  ;# ¬O¤°»ò·N«ä?

ÁÂÁ«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 23# Andy2483

¥u¹ï"¥¿¼Æ"¤Î"¹s­È"°µ³W½d, ¹s­È¬°"ªÅ", ¸Õ¸ÕÀx¦s®æ³]¬°"#", ¦A¿é¤J0
­Y¤]­n±N"­t¼Æ"Åܦ¨ªÅ¦r¦ê
MsgBox Format(-1500, "00000000000;"""";#")
¦ý¹ï²Ä¥|°Ñ¼Æ"¤å¦r"ªºÅã¥Ü¤è¦¡µLªkÅܧó,
³Ì²z·QÁÙ¬O¥ÎAPPLICATION.TEXT(¼Æ¦r©Î¤å¦r, "¥¿;­t;0;¤å¦r")

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-11-3 07:27 ½s¿è

¦^´_ 24# ­ã´£³¡ªL


    ÁÂÁ«e½ú,½Ð«e½ú¦A«ü¾É
1. «á¾Ç¾Ç¨ìªº¬O ;; ¨â­Ó¤À¸¹¥Nªíªº¬O¸õ¹L­t¼Æªº§PÂ_,ª½±µ§PÂ_¬O¤£¬O0
2.«á¾Ç¹ï # ²Å¸¹¦bFormat()¸Ìªº·N¸q¤£¤F¸Ñ
¨Ò¦p:Format(Arr(i, Cn(1)), "00000000000;;""""") »P
Format(Arr(i, Cn(1)), "00000000000;;#")
³o¨â­Ó¦³¦ó¤£¦P?

'==========================================
¾Ç²ß¸É¥R:
(#)¼Æ¦r¹w¯d¦ì¸m¡C ·|Åã¥Ü¼Æ¦r©Î¤£Åã¥Ü¥ô¦ó¶µ¥Ø¡C ¦pªG¹Bºâ¦¡¦³¼Æ¦r¦ì¦b®æ¦¡¦r¦ê¤¤¥X²{ # ªº¦ì¸m¡A
«hÅã¥Ü¸Ó¦r¤¸¡F§_«h¦b¸Ó¦ì¸m¤£Åã¥Ü¥ô¦ó¶µ¥Ø¡C ¦¹²Å¸¹ªº§@¥Î´N¹³ 0 ¼Æ¦r¹w¯d¦ì¸m¡A¤£¦P¤§³B¦b©ó¡A
¦pªG¦b®æ¦¡¹Bºâ¦¡¤¤¡A¼Æ¦r¦b¤p¼Æ¤À¹j²Å¸¹¥ô¤@Ã䪺¦ì¼Æ¡A»P # ¦r¤¸ªº¦ì¼Æ¬Û¦P©Î§ó¤Ö¡A
«h¤£·|Åã¥Ü«e¸m¹s©Î¦æ§À¹s¡C
https://learn.microsoft.com/zh-t ... ic-for-applications

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-11-3 09:18 ½s¿è

¦^´_ 24# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É,¥H¤UApplication.Text()¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

Option Explicit
'³Ì²z·QÁÙ¬O¥ÎAPPLICATION.TEXT(¼Æ¦r©Î¤å¦r,"¥¿;­t;0;¤å¦r")
'VBA¤¤©I¥s¤u§@ªí¤¤ªºTEXT¨ç¼Æ

Sub TEST_Text_1°Ï¬q()
MsgBox Application.Text(10, "0000")
MsgBox Application.Text(-1.2, "0000")
MsgBox Application.Text(0, "0000")
End Sub

Sub TEST_Text_2°Ï¬q()
MsgBox Application.Text(0, "000;-0.00")
MsgBox Application.Text(10, "000;-0.00")
MsgBox Application.Text(9999, "000;-0.00")
MsgBox Application.Text(-1, "000;-0.00")
MsgBox Application.Text(-10.1111, "000;-0.00")
End Sub

Sub TEST_Text_3°Ï¬q()
MsgBox Application.Text(0, "000;-0.00;0")
MsgBox Application.Text(10, "000;-0.00;0")
MsgBox Application.Text(9999, "000;-0.00;0")
MsgBox Application.Text(-1, "000;-0.00;0")
MsgBox Application.Text(-10.1111, "000;-0.00;0")
MsgBox Application.Text(-10.1111, "000;­Ë¦©¤F;0")
End Sub
Sub TEST_Text_4°Ï¬q()
MsgBox Application.Text(0, "0000;-0.00;¹s;¿é¤J¿ù»~")
MsgBox Application.Text(10, "0000;-0.00;¹s;¿é¤J¿ù»~")
MsgBox Application.Text(9999, "0000;-0.00;¹s;¿é¤J¿ù»~")
MsgBox Application.Text(-1, "0000;-0.00;¹s;¿é¤J¿ù»~")
MsgBox Application.Text(-10.1111, "0000;-0.00;¹s;¿é¤J¿ù»~")
End Sub

Sub TEST_Text_4°Ï¬q_0()
MsgBox Application.Text(0, "0000;-0.00;;¿é¤J¿ù»~")
MsgBox Application.Text(0, "0000;-0.00;0;¿é¤J¿ù»~")
MsgBox Application.Text(0, "0000;-0.00;#;¿é¤J¿ù»~")
MsgBox Application.Text(0, "0000;-0.00;¨ú®ø;¿é¤J¿ù»~")
MsgBox Application.Text("0o", "0000;-0.00;¹s;¿é¤J¿ù»~")
'¡ôAPPLICATION.TEXT(¼Æ¦r©Î¤å¦r,"¥¿­È®æ¦¡;­t­È®æ¦¡;·Q­nªº¤å¦r®æ¦¡1;·Q­nªº¤å¦r®æ¦¡2")
'¦pªG ¼Æ¦r©Î¤å¦r¬O¥¿¼Æ ,¥O¥H¥¿­È®æ¦¡¦^¶Ç
'§_«h¦pªG ¼Æ¦r©Î¤å¦r¬O­t¼Æ,¥O­t­È®æ¦¡¦^¶Ç
'§_«h¦pªG ¼Æ¦r©Î¤å¦r¬O0,¥O¥H·Q­nªº¤å¦r®æ¦¡1¦^¶Ç
'§_«h¦pªG«e­±3­Ó±ø¥ó³£¤£¦¨¥ß,¥O¥H·Q­nªº¤å¦r®æ¦¡2¦^¶Ç

End Sub

==============================================
¦bÀx¦s®æ®æ¦¡À³¥Îªº¤è¦¡¦p¤U:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD