‘—————————————–
‘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