VBA : Import Data จาก Excel อื่น
Sub ImportData()
Dim filter As String
Dim captain As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim i, j As Long
Dim iRow As Long
Dim sourceSheet As Worksheet
'Text file and path
filter = "Excel files(*.xlsx),*.xlsx,Excel files(*.xls),*.xls"
captain = "Please Select an input file"
customerFilename = Application.GetOpenFilename(filter, , captain)
If customerFilename = "False" Then
Exit Sub
End If
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
'กำหนด SourceSheet เก็บข้อมูลไฟล์ excel ที่เลือก
Set sourceSheet = customerWorkbook.Worksheets(1) ' ตัวเลขกำหนดว่าจะอ่านข้อมูล Sheet ที่ เท่าไหร่
i = 2 'เริ่มที่ Row 2
j = 6 'เริ่มที่ Row 6
'อ่านข้อมูลที่ละบรรทัด ใส่ข้อมูลที่ละบรรทัด
Do While sourceSheet.Range("A" & i).Value <> ""
DoEvents
Sheet1.Range("A" & j).Value = sourceSheet.Range("A" & i).Value
Sheet1.Range("B" & j).Value = sourceSheet.Range("B" & i).Value
Sheet1.Range("C" & j).Value = sourceSheet.Range("C" & i).Value
Sheet1.Range("D" & j).Value = sourceSheet.Range("D" & i).Value
Sheet1.Range("E" & j).Value = sourceSheet.Range("E" & i).Value
Sheet1.Range("F" & j).Value = sourceSheet.Range("F" & i).Value
Sheet1.Range("G" & j).Value = sourceSheet.Range("G" & i).Value
Sheet1.Range("H" & j).Value = sourceSheet.Range("H" & i).Value
Sheet1.Range("I" & j).Value = sourceSheet.Range("I" & i).Value
Sheet1.Range("J" & j).Value = sourceSheet.Range("J" & i).Value
Sheet1.Range("K" & j).Value = sourceSheet.Range("K" & i).Value
Sheet1.Range("L" & j).Value = sourceSheet.Range("L" & i).Value
Sheet1.Range("M" & j).Value = sourceSheet.Range("M" & i).Value
Sheet1.Range("N" & j).Value = sourceSheet.Range("N" & i).Value
Sheet1.Range("O" & j).Value = sourceSheet.Range("O" & i).Value
Sheet1.Range("P" & j).Value = sourceSheet.Range("P" & i).Value
Sheet1.Range("Q" & j).Value = sourceSheet.Range("Q" & i).Value
Sheet1.Range("R" & j).Value = sourceSheet.Range("R" & i).Value
Sheet1.Range("S" & j).Value = sourceSheet.Range("S" & i).Value
Sheet1.Range("T" & j).Value = sourceSheet.Range("T" & i).Value
Sheet1.Range("U" & j).Value = sourceSheet.Range("U" & i).Value
Sheet1.Range("V" & j).Value = sourceSheet.Range("V" & i).Value
Sheet1.Range("W" & j).Value = sourceSheet.Range("W" & i).Value
i = i + 1
j = j + 1
Loop
customerWorkbook.Close
End Sub
ตัวอย่าง ===> ImportData.xlsm
Good Luck
0 ความคิดเห็น:
แสดงความคิดเห็น