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

[µo°Ý] EXCEL VBA XLSX TO TXT ªÅ¥Õ¦æ

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-11-28 18:20 ½s¿è

¦^´_ 7# dou10801

¤W­±¨â¦ì¤j¤j´£¨Ñªºµ{¦¡ ³£»Ý­n¼g¦b§Aªº "A001.xlsx" Àɮפ¤¡A

¦Ò¶q¨ì§A¥i¯à¦³¦h­ÓÀÉ®× Ex A002,A003 ¥i¯à³£»Ý­nÂনtxt¡A

¨C­ÓÀɮ׳£­n½Æ»s¨º¬qµ{¦¡¡A·|¦³ÂI·Ðº¾

§Ú´£¨Ñ¤@¬qµ{¦¡¡AÅý§A¦Û¤v³]©w¨Ó·½¸ô®|&ÀɦW(xlsx) & ¿é¥X¸ô®|&ÀɦW(txt)¡A·|¤ñ¸û¼u©Ê

­Y­n¤@¦¸°õ¦æ¦h­ÓÀɮסA§A¥i¥H¦Û¤v§ï¦¨°j°é

¤W­±¨â¦ì¤j¤j³£¥Î Open»yªk °µ¥X¯Â¤å¦r¤å¥ó¡A§Ú´N´«­Ó¼gªk§a!

¥ÎExcel¥´¶}ÀÉ®×(°ß¿W)¡A¦A¥t¦s¦¨txtÀÉ

³o¬qµ{¦¡¤£­­¨î§A­n©ñ¦b­þ­ÓExcelÀÉ¡A¿é¤J¿é¥X(I/O)³]©w¦n§Y¥i

µ{¦¡¦p¤U


Sub Test1128()
Dim Arr, R&, C&
myPath$ = ThisWorkbook.Path & "\"
xlsPath$ = myPath & "A001.xlsx"   'xlsÀɮרӷ½¸ô®|¡A½Ð¦Û¦æ³]©w(¹w³]¥»µ{¦¡Àɮ׸ô®|¤U)
txtPath$ = myPath & "A001.txt"     '¿é¥Xtxtªº¸ô®|&ÀɦW¡A½Ð¦Û¦æ³]©w(¹w³]¥»µ{¦¡Àɮ׸ô®|¤U)
With Workbooks.Open(xlsPath, , True).Sheets(1)
  Arr = .[A1].CurrentRegion
  For R = 1 To UBound(Arr)
    For C = 2 To UBound(Arr, 2)
      Arr(R, 1) = Arr(R, 1) & Arr(R, C)
    Next C
    Arr(R, 1) = Arr(R, 1) & String(20, " ")
  Next R
  .[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
  .Columns("B").Resize(, UBound(Arr, 2)).Delete
  .SaveAs txtPath, 42
  .Parent.Close True
End With
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 8# n7822123

®¦....21­ÓªÅ¥Õ¦r¤¸¡A¼g¦¨20­Ó¤F¡A³o¸Ì§ï¤@¤U

Arr(R, 1) = Arr(R, 1) & String(21, " ")
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 10# dou10801

·PÁ n7822123 ´£¨Ñ¥t¤@ºØ«ä¸ô,­«½Æ¦sÀÉ®É,¨t²Î·|¸ß°Ý,[¬O§_­n¨ú¥N],¦p«ö[§_]·|¤¤Â_,¦p¦ó¸Ñ¨M,·P®¦.

¨º´N±j¨îÂл\¡A¤£¸ß°Ý´N¦n¤F¡A²K¥[¤@¦æµ{¦¡§Y¥i

Sub Test1130()
Dim Arr, R&, C&
Application.DisplayAlerts = False   '­Y¤w¦³ÀɮסBª½±µÂл\¤£¸ß°Ý
myPath$ = ThisWorkbook.Path & "\"
xlsPath$ = myPath & "A001.xlsx"   'xlsÀɮרӷ½¸ô®|¡A½Ð¦Û¦æ³]©w(¹w³]¥»µ{¦¡Àɮ׸ô®|¤U)
txtPath$ = myPath & "A001.txt"     '¿é¥Xtxtªº¸ô®|&ÀɦW¡A½Ð¦Û¦æ³]©w(¹w³]¥»µ{¦¡Àɮ׸ô®|¤U)
With Workbooks.Open(xlsPath, , True).Sheets(1)
  Arr = .[A1].CurrentRegion
  For R = 1 To UBound(Arr)
    For C = 2 To UBound(Arr, 2)
      Arr(R, 1) = Arr(R, 1) & Arr(R, C)
    Next C
    Arr(R, 1) = Arr(R, 1) & String(21, " ")
  Next R
  .[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
  .Columns("B").Resize(, UBound(Arr, 2)).Delete
  .SaveAs txtPath, 42
  .Parent.Close True
End With
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-5 03:00 ½s¿è

¦^´_ 12# dou10801


¦]¬°§AªºxlsxÀÉ¡A¥»¨­´N¦³¼ÐÃD¦Cªü=.=

Arr °}¦C¤@¶}©l´N·|¨ú¨ì¼ÐÃD¦C¡A©Ò¥H§AªºFor°j°é¡AR§ï¦¨±q2¶}©l¤]¨S¦³¥Î

³Ì«á§â°}¦Cªº­È¶K¦^¥hÀx¦s®æÁÙ¬O¦³¼ÐÃD¦C~~

¥u­n¥[1¦æµ{¦¡§R±¼²Ä¤@¦C´N¦n¤F



      MES = UBound(Arr) - 1
      .[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
      .Columns("B").Resize(, UBound(Arr, 2)).Delete
      .Rows(1).Delete
      .SaveAs txtPath, 42
      .Parent.Close True
    End With
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-5 14:15 ½s¿è

¦^´_ 14# quickfixer


«á¨Óµ¹ªºÀɮשM¤@¶}©lªº½d¥»,®Ú¥»¤£¤@¼Ë

¨S¿ù!!!

¦h¤F¼ÐÃD¦C,ª÷ÃB¨ºÄæ¦r¼Æ¤£©T©w,¨º­Ó­n¥[21­ÓªÅ¥Õªº±ø¥ó,¬O¿ùªº§a?
ªÅ¥Õ¦r¼Æ¬O¨ä¹êÅܰʪº¤~¹ï?ÁÙ¬Oª÷ÃB¨ºÄæ­n¸É¦r¼Æ?   

³o­Ó§Ú¨S¥J²Ó¬Ý¥L«á­±ªº½d¥»©M¥L¸Éªºµ{¦¡¡A¬Ý¥L»Ý­n¤°»ò¦A¥h½T»{´N¦n

§Ú­Ì¥i¥HÀRÆ[¨äÅÜ
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD