VB에서 엑셀파일 읽기

--------------------------------------------------------------------------------

  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  Set xl = CreateObject("Excel.application")
  Set wb = xl.Workbooks.Open("엑셀파일이름.xls")
  Set sht = wb.Worksheets(1)
  row = 1
  Do While 1
    If sht.Cells(row, 1) = "" Then
      Exit Do
    End If
    debug.print sht.Cells(row, 1)
    debug.print sht.Cells(row, 2)
    debug.print sht.Cells(row, 3)
    row = row + 1
  Loop
  Set xl = Nothing
  Set wb = Nothing
  Set sht = Nothing

====================================================
[VB]엑셀파일에 데이터 쓰기

--------------------------------------------------------------------------------

Private Sub 엑셀에쓰기()
    Dim objExcel As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim objWorksheet As Excel.Worksheet
    Dim idx As Integer

    Set objExcel = CreateObject("excel.application")
    objExcel.Visible = True
    Set objWorkbook = objExcel.Workbooks.Add
    objExcel.DisplayAlerts = False

    Do While objWorkbook.Worksheets.count > 1
        Set objWorksheet = objWorkbook.Worksheets.item(objWorkbook.Worksheets.count)
        objWorksheet.Delete
    Loop
    objWorkbook.Worksheets("sheet1").Activate
    Set objWorksheet = objWorkbook.ActiveSheet
    objWorksheet.name = "sheet이름"
    idx = 1
    objWorksheet.Cells(idx, 1) = "자료 정리 중..."
    

    idx = idx + 1
    '제목달기
    objWorksheet.Cells(idx, 1) = "회원번호"
    objWorksheet.Cells(idx, 2) = "계약일"
    objWorksheet.Cells(idx, 3) = "최종입금일"
    objWorksheet.Cells(idx, 4) = "매출액"
    objWorksheet.Cells(idx, 5) = "잔액"
    objWorksheet.Cells(idx, 6) = "비고"

    '칼럼포맷 설정    
    objWorksheet.Columns(4).NumberFormat = "###,###,##0"
    objWorksheet.Columns(5).NumberFormat = "###,###,##0"
    objWorksheet.rows(idx).Font.Bold = True
    objWorksheet.rows(idx).HorizontalAlignment = 3

    idx = idx + 1
    objWorksheet.Cells.ColumnWidth = 12

    '셀에 데이터 쓰기
    Do While objWorksheet.Cells(idx, 1) <> ""
        objWorksheet.Cells(1, 1) = 회원변수 & " 처리 중...."        

        '필요한 자료 얻기

          .......

        '얻은 자료 셀에 넣기
        objWorksheet.Cells(idx, 1) = 회원번호
        objWorksheet.Cells(idx, 2) = 계약일
        objWorksheet.Cells(idx, 3) = 최종입금일
        objWorksheet.Cells(idx, 4) = 매출액
        objWorksheet.Cells(idx, 5) = 잔액
        objWorksheet.Cells(idx, 6) = 비고
    Loop

    '합계 계산
    objWorksheet.Cells(idx + 1, 1) = "합계"
    objWorksheet.Cells(idx + 1, 4) = "=SUM(D3:D" & idx & ")"
    objWorksheet.Cells(idx + 1, 5) = "=SUM(E3:E" & idx & ")"
    objWorksheet.Cells(1, 1) = "작업끝."

    Set objExcel = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing

end sub
[이 게시물은 오원장님에 의해 2013-02-28 16:42:35 오피스팁에서 이동 됨]