Snasui Excel Forum

Code: Select all

Sub createPivotTable1()
Dim wb As Variant, Source As Range
On Error Resume Next
'ดึงข้อมูลไฟล์ Text ชื่อ scb-รับ.txt
ThisWorkbook.Activate
Sheets("INKS").Select
Range("A1:H65536").ClearContents
Workbooks.Open Filename:="C:\Users\Cz410036\Desktop\Clash 4\scb.txt"
Application.ScreenUpdating = False
Range("A5:H" & Range("a65536").End(xlUp).Row).Copy
ThisWorkbook.Sheets(3).Activate
Range("a65536").End(xlUp).Offset(0, 0).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
 Windows("scb.txt").Activate
ActiveWindow.Close False
'ดึงข้อมูลไฟล์ Text ชื่อ scb-จ่าย.txt
Workbooks.Open Filename:="C:\Users\Cz410036\Desktop\Clash 4\scb-จ่าย.txt"
Application.ScreenUpdating = False
Range("A5:H" & Range("a65536").End(xlUp).Row).Copy
ThisWorkbook.Sheets(4).Activate
Range("a65536").End(xlUp).Offset(0, 0).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
 Windows("scb-จ่าย.txt").Activate
ActiveWindow.Close False
'-----------------------------------------------------------------
'คำสั่งทำPivotTable scb-รับ
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Set wsData = Worksheets("INSCB")
Set wsPvtTbl = Worksheets("รับSCB")
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
Set rngData = wsData.Range("A1:G400")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
 Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("วันครบกำหนด")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("ลูกค้า")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("มูลค่าเช็ค")
 pvtFld.Orientation = xlValuesField
With PvtTbl.PivotFields("มูลค่าเช็ค")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With
PvtTbl.ManualUpdate = False
'---------------------------------------------------------------------------
'คำสั่งทำPivotTable scb-จ่าย
Set wsData = Worksheets("OUTSCB")
Set wsPvtTbl = Worksheets("จ่ายSCB")
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
Set rngData = wsData.Range("A1:G400")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
 Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("วันครบกำหนด")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("เจ้าหนี้")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("มูลค่าเช็ค")
 pvtFld.Orientation = xlValuesField
With PvtTbl.PivotFields("มูลค่าเช็ค")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With
PvtTbl.ManualUpdate = False

End Sub

Code: Select all

    ActiveWindow.SmallScroll Down:=-9
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\bank9597\Downloads\scb.txt", Destination:=Range("$A$1"))
        .Name = "scb"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 932
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1,2, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Code: Select all

.TextFileColumnDataTypes = Array(1,2, 1, 1, 1, 1, 1, 1, 1)

Code: Select all

Workbooks.Open Filename:="C:\Users\Cz410036\Desktop\Clash 4\scb.txt"

Code: Select all

Public Sub new_import()

        With Sheets("INSCB").QueryTables.Add(Connection:= _
        "TEXT;C:\Users\bank9597\Downloads\scb_in.txt", Destination:=Range("$A$1"))
        .Name = "scb_in"
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        End With
        
        With Sheets("INSCB")
            Rows("1:5").Delete Shift:=xlUp
        End With
End Sub

Code: Select all

C:\Users\bank9597\Downloads\scb_in.txt

Code: Select all

Sub createPivotTable1()
Dim wb As Variant, Source As Range
On Error Resume Next
'´Ö§¢éÍÁÙÅä¿Åì Text ª×èÍ scb-ÃѺ.txt
Call new_import
'´Ö§¢éÍÁÙÅä¿Åì Text ª×èÍ scb-¨èÒÂ.txt
Workbooks.Open Filename:="C:\Users\Cz410036\Desktop\Clash 4\scb-¨èÒÂ.txt"
Application.ScreenUpdating = False
Range("A5:H" & Range("a65536").End(xlUp).Row).Copy
ThisWorkbook.Sheets(4).Activate
Range("a65536").End(xlUp).Offset(0, 0).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
 Windows("scb-¨èÒÂ.txt").Activate
ActiveWindow.Close False
'-----------------------------------------------------------------
'¤ÓÊÑ觷ÓPivotTable scb-ÃѺ
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Set wsData = Worksheets("INSCB")
Set wsPvtTbl = Worksheets("ÃѺSCB")
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
Set rngData = wsData.Range("A1:G400")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
 Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("Çѹ¤Ãº¡Ó˹´")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("ÅÙ¡¤éÒ")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("ÁÙŤèÒàªç¤")
 pvtFld.Orientation = xlValuesField
With PvtTbl.PivotFields("ÁÙŤèÒàªç¤")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With
PvtTbl.ManualUpdate = False
'---------------------------------------------------------------------------
'¤ÓÊÑ觷ÓPivotTable scb-¨èÒÂ
Set wsData = Worksheets("OUTSCB")
Set wsPvtTbl = Worksheets("¨èÒÂSCB")
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
Set rngData = wsData.Range("A1:G400")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
 Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("Çѹ¤Ãº¡Ó˹´")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("à¨éÒ˹Õé")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("ÁÙŤèÒàªç¤")
 pvtFld.Orientation = xlValuesField
With PvtTbl.PivotFields("ÁÙŤèÒàªç¤")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With
PvtTbl.ManualUpdate = False

End Sub

Code: Select all

.TextFilePlatform = 874

Code: Select all

Public Sub new_import()

        With Sheets("INSCB").QueryTables.Add(Connection:= _
        "TEXT;C:\Users\bank9597\Downloads\scb_in.txt", Destination:=Range("$A$1"))
        .Name = "scb_in"
        .TextFilePlatform = 874
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        End With
        
        With Sheets("INSCB")
            Rows("1:5").Delete Shift:=xlUp
        End With
End Sub