SAP CO Risk Vbs Script Program

‘—————————————–

‘SAP CO Risk Vbs Script Program

‘Author: Zhu.Wei

‘Creation Date: 2014.04.23

‘Source Excel File Format is:

‘ -> ZMRNX_COMPANYCODE_DATE_TIME.XLS

‘ e.g:

‘ ZMRN0_0081_20140421_145325.xls

‘ ZMRN1_0080_20140423_070251.xls

‘Change Log:

‘—————————————–

‘2014.04.23 Zhu Wei New Creation

‘—————————————–

On Error Resume Next

 

Const xlWorkbookNormal = -4143

Const xlSaveChanges = 1

 

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

 

Dim wks

 

Dim fso, f, RiskFile

Dim strDate

Dim strFrom

Dim str_temp_1,str_temp_2

dim strFn_ZMRN0

dim strFn_ZMRN1

dim strFn_ZMRN3

dim strFn_ZMRN9

 

strFrom = “Q85”

lstg_from_folder = “C:\temp\” & strFrom

 

lstg_bak_folder = replace(lstg_from_folder,strFrom,strFrom & “_Bak”)

lstg_temp_folder = replace(lstg_from_folder,strFrom,strFrom & “_Temp”)

lstg_output_folder = replace(lstg_from_folder,strFrom,strFrom & “_Output”)

lstg_output_bak_folder = replace(lstg_from_folder,strFrom,strFrom & “_Output_Bak”)

lstg_log_folder = replace(lstg_from_folder,strFrom,strFrom & “_Log”)

 

strDate = Year(Date()) & Right(“0” & Month(Date()), 2) & Right(“0” & Day(Date()), 2)

 

strFn_ZMRN0 = lstg_temp_folder & “\ZMRN0_” & strDate & “.xls”

strFn_ZMRN1 = lstg_temp_folder & “\ZMRN1_” & strDate & “.xls”

strFn_ZMRN3 = lstg_temp_folder & “\ZMRN3_” & strDate & “.xls”

strFn_ZMRN9 = lstg_temp_folder & “\ZMRN9_” & strDate & “.xls”

 

 

set ws = createobject(“wscript.shell”)

set fso = createobject(“scripting.filesystemobject”)

 

InitData

PrepareData

ProcessData lstg_temp_folder,lstg_output_folder

CheckError

 

‘—–Initialization Data—–

Sub InitData

    set folder = fso.getfolder(lstg_temp_folder)

    set files = folder.files

 

    log_msg =”======Initialization Data======”

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg        

 

    log_msg =”—Clear Temp File—“

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg    

 

    For each file in files

     lstg_temp = file.Name

     Set fso = CreateObject(“Scripting.FileSystemObject”)

     fso.DeleteFile(lstg_temp_folder & “\” & file.Name)

 

        log_msg = Date &” “&Time &” delete Temp File [ “& lstg_temp &” ] is done !”

        LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg        

    Next

 

    log_msg =”—Bakup Output File—“

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg

 

    set folder = fso.getfolder(lstg_output_folder)

    set files = folder.files

    For each file in files

        lstg_temp = file.Name

        MoveFile lstg_temp,lstg_output_folder,lstg_output_bak_folder    

    Next    

End Sub

 

‘—–Prepare Data—–

Sub PrepareData

    log_msg =”======Prepare Data======”

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg    

        ‘MsgBox lstg_from_folder

 

    set folder = fso.getfolder(lstg_from_folder)

    set files = folder.files

 

    For each file in files

     Set fso = CreateObject(“Scripting.FileSystemObject”)

 

        str_temp_1 = Left(file.Name,5)

        lstg_temp = file.Name

 

        ‘MsgBox file.Name

 

        Select Case str_temp_1

            Case “ZMRN0”

             Set f = fso.OpenTextFile(lstg_from_folder & “\” & file.Name,ForReading,True)    

                OpenFileData = f.ReadAll

                CollectFile strFn_ZMRN0,OpenFileData

                f.Close

 

                MoveFile lstg_temp,lstg_from_folder,lstg_bak_folder    

         Case “ZMRN1”

             Set f = fso.OpenTextFile(lstg_from_folder & “\” & file.Name,ForReading,True)    

                OpenFileData = f.ReadAll

                CollectFile strFn_ZMRN1,OpenFileData

                f.Close    

 

                MoveFile lstg_temp,lstg_from_folder,lstg_bak_folder    

         Case “ZMRN3”

             Set f = fso.OpenTextFile(lstg_from_folder & “\” & file.Name,ForReading,True)    

                OpenFileData = f.ReadAll

                CollectFile strFn_ZMRN3,OpenFileData

                f.Close    

 

                MoveFile lstg_temp,lstg_from_folder,lstg_bak_folder        

         Case “ZMRN9”

             Set f = fso.OpenTextFile(lstg_from_folder & “\” & file.Name,ForReading,True)    

                OpenFileData = f.ReadAll

                CollectFile strFn_ZMRN9,OpenFileData

                f.Close    

 

                MoveFile lstg_temp,lstg_from_folder,lstg_bak_folder            

        End Select

    Next

 

End Sub

 

 

Sub MoveFile(lstg_file,from_folder,bak_folder)

Dim fso

Set fso = CreateObject(“Scripting.FileSystemObject”)

‘ Set f = fso.GetFolder(lstg_bak_folder)

 

lstg_bak_folder_1 = bak_folder & “\” & strDate & “\”

lstg_from_folder_1 = from_folder & “\”

If Not (fso.FolderExists(lstg_bak_folder_1)) Then

    fso.CreateFolder(lstg_bak_folder_1)

End If

 

If fso.FileExists(lstg_bak_folder_1 & lstg_file) Then

‘msgBox lstg_file&” exists”

        log_msg =Date&” “&Time &” Move File [ “& lstg_file& ” ] is exists !”

        LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg

Else

        ‘MsgBox lstg_from_folder_1 & lstg_file

        fso.MoveFile lstg_from_folder_1 & lstg_file, lstg_bak_folder_1

 

        log_msg =Date&” “&Time &” Move File “& lstg_file& ” From “& lstg_from_folder_1 &” to ” & lstg_bak_folder_1 & “Done!”

        LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg    

end if

 

End Sub

 

‘—–Collection File Data—–

Sub CollectFile(lstg_file,lstg_data)

    Dim fso1,f1,mrnx_file    

    MsgBox lstg_file

‘    MsgBox lstg_data

Set fso1 = CreateObject(“Scripting.FileSystemObject”)

 

    If fso1.FileExists(lstg_file) Then

     Set f1 = fso1.GetFile(lstg_file)

     Set mrnx_file = f1.OpenAsTextStream(ForAppending, TristateUseDefault)

     mrnx_file.WriteLine lstg_data

     mrnx_file.Close

else

        Set mrnx_file = fso1.CreateTextFile(lstg_file, True)

        mrnx_file.WriteLine lstg_data

        mrnx_file.Close

end if

 

    log_msg =Date&” “&Time &” Collect File [ “& lstg_file& ” ] <–!”

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg     

End Sub

 

 

Sub LogFile(lstg_file,log_msg)

    Dim fso,f, LogFile

Set fso = CreateObject(“Scripting.FileSystemObject”)

 

    If fso.FileExists(lstg_file) Then

     Set f = fso.GetFile(lstg_file)

     Set LogFile = f.OpenAsTextStream(ForAppending, TristateUseDefault)

     LogFile.WriteLine log_msg

     LogFile.Close

else

        Set LogFile = fso.CreateTextFile(lstg_file, True)

        LogFile.WriteLine log_msg

        LogFile.Close

end if

 

End Sub

 

Sub ProcessData(temp_frlder,output_flder)

    log_msg =”======Process Data======”

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg    

 

    Set objExcel = CreateObject(“Excel.Application”)

    objExcel.DisplayAlerts = False

 

    ‘Temp Excel File

    set new_workbook=objExcel.Workbooks.open(“c:\temp\zmrx.xlsx”)

 

    Dim StrFile_temp, strFile_output

    Dim index

    set folder = fso.getfolder(temp_frlder)

    set files = folder.Files

 

 

    StrFile_output = output_flder & “\ZMRX_” & strDate &”.xls”

    index = 0

    For each file in files

        index = index + 1

        strFn = left(file.Name,5)

         StrFile_temp = temp_frlder & “\” & file.Name

         set workbook = objExcel.Workbooks.open(StrFile_temp)

         set worksheet = workbook.Worksheets.Item(1)

 

         Set new_worksheet = new_workbook.Worksheets(strFN)

        worksheet.Rows(“1:” & worksheet.UsedRange.Rows.Count).Copy

        new_worksheet.Rows(2).PasteSpecial

 

        log_msg = file.Name & ” Combine done !”

        LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg

 

        workbook.Close

    Next

    new_workbook.SaveAs strFile_output, -4143

    new_workbook.Close

 

    objExcel.Quit

    Set new_worksheet = Nothing

    Set new_workbook = Nothing

    Set workbook = Nothing

    Set objExcel = Nothing     

 

End Sub

 

 

Sub CheckError

If err.Number <> 0 Then

    errNum = Err.Number

    errDesc = Err.Description

    log_msg = “[Error] “& errNum & ” – ” & errDesc

    LogFile lstg_log_folder&”\”&strDate&”.log”,log_msg    

End If

End Sub

Leave a Comment