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

[µo°Ý] VBAµ{¦¡ ¶}±Ò¤@­Ó¬Û¦PÀɦW¤£¦PÀÉ®×

¦^´_ 10# èªÎ¯Ì¦Ï

§ó§ï¦p¤U¬õ¦r¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Dim PH$, FN$, Arr(1 To 65536, 1 To 3)

TOP

TXT¸ê®Æ½Æ»s¨ìCsvªºµ{¦¡½X¹ï¶Ü!
  1. Option Explicit
  2. Dim WB As Workbook, Csv_File As String, Txt_File As String, Txt_Path As String, Csv_Path As String, Save_Path As String
  3. Sub Ex_Main()
  4.     Dim Msg As Variant
  5.     Àˬd¸ê®Æ§¨
  6.     Application.ScreenUpdating = False
  7.     Application.StatusBar = "            µ{¦¡°õ¦æ¤¤...................."
  8.     Csv_File = Dir(Csv_Path & "\*.csv")
  9.     Msg = Csv_File
  10.     Do While Csv_File <> ""
  11.         Txt_File = Txt_Path & "\" & Split(Csv_File, ".")(0) & ".txt"                    '­×§ïcsv °ÆÀɦW ¬°txt
  12.         If CreateObject("Scripting.FileSystemObject").FileExists(Txt_File) = False Then MsgBox "§ä¤£¨ì " & vbLf & Txt_File & vbLf & "Àˬd«á ­«·s°õ¦æµ{¦¡!": End
  13.         Set WB = Workbooks.Open(Csv_Path & Csv_File)
  14.         TXT½Æ»s¦ÜCSV
  15.         Csv_File = Dir()
  16.         Msg = Msg & vbLf & Csv_File
  17.     Loop
  18.     Application.StatusBar = False
  19.     Application.ScreenUpdating = True
  20.     Msg = Split(Msg, vbLf)
  21.     MsgBox Join(Msg, vbLf) & "§¹¦¨TXT½Æ»s¦ÜCSV  " & UBound(Msg) & " ­ÓÀÉ®×"
  22. End Sub
  23. Private Sub Àˬd¸ê®Æ§¨()
  24.     Dim Msg As String
  25.     Save_Path = ThisWorkbook.Path & "\Test\"                     'TXT½Æ»s¦ÜCSV  ¦sÀɪº¸ê®Æ§¨
  26.     Csv_Path = ThisWorkbook.Path & "\csvÀÉ\"                     'csvÀÉÀɪº¸ê®Æ§¨
  27.     Txt_Path = ThisWorkbook.Path & "\TXTÀÉ\"                     'TXTÀɪº¸ê®Æ§¨
  28.     If Dir(Save_Path, vbDirectory) = "" Then
  29.          If MsgBox("«Ø¥ß " & Save_Path & "  ¸ê®Æ§¨", vbYesNo) = vbYes Then
  30.             MkDir (Save_Path)
  31.          End If
  32.     End If
  33.     If Dir(Csv_Path, vbDirectory) = "" Then Msg = "§ä¤£¨ì " & Csv_Path
  34.     If Dir(Txt_Path, vbDirectory) = "" Then Msg = Msg & vbLf & "§ä¤£¨ì " & Txt_Path
  35.     If Msg <> "" Then MsgBox Msg & vbLf & "Àˬd«á ­«·s°õ¦æµ{¦¡!": End
  36. End Sub
  37. Private Sub TXT½Æ»s¦ÜCSV()
  38.     Dim Sh As Worksheet, Rng As Range
  39.     Set Sh = Workbooks.Open(Txt_File).Sheets(1)                              '¶}±ÒTXTªºSheets(1)
  40.     Set Rng = WB.Sheets(1).Range("a1").End(xlDown).Offset(1)   'TXT¸ê®Æ½Æ»s¨ìCsvªº«ü©w¦ì¸m
  41.     If Rng.Row = Rows.Count Then Set Rng = WB.Sheets(1).Range("a1")
  42.     '«ü©w¦ì¸mªºRow= Rows.Count  **«ü©w¦ì¸m¦bÀɮש³³¡®É --AÄ椤¨S¦³¸ê®Æ
  43.     With Sh.Range("A1")                     '¸ê®Æ­åªRAÄæ  -,,,-> Comma:=True
  44.         .CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
  45.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  46.             Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
  47.             :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
  48.         .CurrentRegion.Copy Rng    '**TXT¸ê®Æ½Æ»s¨ìCsv
  49.     End With
  50.     Sh.Parent.Close False                                  'Sh(Worksheet).Parent(¤÷¼h-Workbook)Ãö³¬
  51.     If CreateObject("Scripting.FileSystemObject").FileExists(Save_Path & Csv_File) Then Kill Save_Path & Csv_File
  52.         '**CreateObject("Scripting.FileSystemObject").FileExists=True **ªíÀɮצs¦b,¬G°õ¦æ  Kill
  53.     With WB
  54.         .SaveAs Save_Path & Csv_File               '-Workbook¥t¦s\Test\ ªº CsvÀÉ
  55.         '****   FileExists=True **  .SaveAs ·|´£¥Ü¬O§_¨ú¥N­ìÀÉ®×--   µ{¦¡¼È®É¤¤Â_°õ¦æ
  56.         .Close True
  57.     End With
  58. End Sub
½Æ»s¥N½X
¦^´_ 3# èªÎ¯Ì¦Ï

TOP

  1. Sub Ex_Main()
  2.     Dim Msg As Variant
  3.     Àˬd¸ê®Æ§¨
  4.     Application.ScreenUpdating = False
  5.     Application.StatusBar = "            µ{¦¡°õ¦æ¤¤...................."
  6.     Csv_File = Dir(Csv_Path & "\*.csv")
  7.     Msg = Csv_File
  8.     Do While Csv_File <> ""
  9.         Txt_File = Txt_Path & "\" & Split(Csv_File, ".")(0) & ".txt"                    '­×§ïcsv °ÆÀɦW ¬°txt
  10.         If CreateObject("Scripting.FileSystemObject").FileExists(Txt_File) = False Then MsgBox "§ä¤£¨ì " & vbLf & Txt_File & vbLf & "Àˬd«á ­«·s°õ¦æµ{¦¡!": End
  11.         Set WB = Workbooks.Open(Csv_Path & Csv_File)
  12.         TXT½Æ»s¦ÜCSV
  13.         Csv_File = Dir()
  14.         Msg = Msg & vbLf & Csv_File
  15.     Loop
  16.     Application.StatusBar = False
  17.     Application.ScreenUpdating = True
  18.    '**********¥[¤W³oIf §PÂ_µ{¦¡½X ,µ{¦¡·|§¹¾ã¨Ç*****************
  19. If Msg <> "" Then
  20.         Msg = Split(Msg, vbLf)
  21.         MsgBox Join(Msg, vbLf) & "§¹¦¨TXT½Æ»s¦ÜCSV  " & UBound(Msg) & " ­ÓÀÉ®×"
  22.     Else
  23.         MsgBox "¨S¦³¥ô¦óTXTÀɽƻs¦ÜCSVÀÉ"
  24.     End If
  25. '************************************************
  26. End Sub
½Æ»s¥N½X
¦^´_ 12# lee88

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD