[매크로] 엑셀 시트에 입력된 값을 txt파일로 저장하기

Sub make_sql_basic()

'

' make_sql_basic Macro

' push요청쿼리 text파일로 만들기

'

' 바로 가기 키: Ctrl+e

'

    

    Dim cnt As Integer

    Dim model As String

    Dim cc As String

    Dim current As String

    Dim saveText As String

    Dim i As Long

    Dim checkMsg As String

    Dim savePath As String

    

'  Sheets("sheet1").Range("E2") <---sheet1(시트명). Range("E2")(데이터 필드명)   

    cnt = Sheets("sheet1").Range("E2")

'   현재경로명 

    savePath = ThisWorkbook.Path & "\"

    

    checkMsg = "모델갯수가 " & cnt & "개가 맞습니까?"

'  confirm 체크  

    If MsgBox(checkMsg, vbInformation + vbYesNo, "입력확인") = vbYes Then

        Dim dataModel(100)      As String

        Dim dataCC(100)         As String

        Dim dataCurrent(100)    As String

        

        Dim MyDate 

'  현재일

        MyDate = Date

        

        

        ' 쿼리생성 데이터 추출

        ' 모델명            = dataModel

        ' CC                = dataCC

        ' Current Version   = dataCurrent

        

        For j = 1 To cnt

                dataModel(j) = Sheets("sheet1").Range("A" & j + 1)

                dataCC(j) = Sheets("sheet1").Range("B" & j + 1)

                dataCurrent(j) = Sheets("sheet1").Range("C" & j + 1)

        Next j

          

'  txt파일로 저장하기

        Dim FileName As String

        Dim FileNumber As Integer

        

        FileName = savePath & MyDate & ".txt"

        FileNumber = FreeFile

        

        '파일 열기

        Open FileName For Output As FileNumber

        

        '문자열 쓰기

        For i = 1 To cnt

        Print #FileNumber, "*" & dataModel(i) & "  " & dataCC(i); "  " & dataCurrent(i)

        Print #FileNumber, "select /*+ index (dvce tcdm_mgt_dvce_x04) */"

        Print #FileNumber, "dvce_phsl_addr_txt, 'A:'||nvl(tphon_num_txt, 'No Number')"

        Print #FileNumber, "from sdm.tcdm_mgt_dvce dvce"

        Print #FileNumber, "where dvce_phsl_addr_txt not in (select dvce_phsl_addr_txt from sdm.tcdm_mgt_tst_dvce where del_fg = 'N')"

        Print #FileNumber, "and dvce_phsl_addr_txt not in (select dvce_phsl_addr_txt from sdm.tcdm_mgt_block_dvce)"

        Print #FileNumber, "and dvce_model_nm = '" & dataModel(i) & "'"

        Print #FileNumber, "and dvce_cust_cd = '" & dataCC(i) & "'"

        Print #FileNumber, "and fw_ver = '" & dataCurrent(i) & "'"

        Print #FileNumber, "and push_type_cd is not null"

        Print #FileNumber, "and push_type_cd != 'WAP'"

        Print #FileNumber, "and not exists (select wrk.dvce_phsl_addr_txt"

        Print #FileNumber, "               from sdm.tcdm_mgt_schd_wrk wrk"

        Print #FileNumber, "               where dvce.dvce_model_nm = '" & dataModel(i) & "'"

        Print #FileNumber, "                and dvce.dvce_cust_cd = '" & dataCC(i) & "'"

        Print #FileNumber, "                and wrk.tst_dvce_fg = 'N'"

        Print #FileNumber, "                and wrk.sts_cd in ('P', 'O')"

        Print #FileNumber, "                and wrk.dvce_phsl_addr_txt = dvce.dvce_phsl_addr_txt)"

        Print #FileNumber, "and rownum <= 10000;"

        Print #FileNumber, ""

        Next i

        

        '파일 닫기

        Close FileNumber

    

    MsgBox FileName & "에 SQL문이 저장되었습니다"

   End If

End Sub