Hi,
I have a vbscript that copies data from existing various excel files and pastes to another excel sheet.
Sometimes (1 in 5 times), I encounter this issue. A message box pops up with the following error message:
“Not Enough System Resources!”
Any ideas, why this is happening.
Here is the script:
====================================
Function cp_wkBook1()
‘Initialize Excel Object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘ Open workbook to which data has to be copied – target Workbook
Set objWorkbook1= objExcel.Workbooks.Open(“D:\FA_Automation\BT_1\BT1.xls”)
‘ Open workbook from which data has to be copied – Source Workbook
Set objWorkbook2= objExcel.Workbooks.Open(“D:\FA_Automation\BT_1\TCA.xls”)
‘Copy data from all the cells used
objWorkbook2.Worksheets(“TCA”).UsedRange.Copy
‘Copy data to B1 location of Sheet 1
objWorkbook1.Worksheets(“Sheet1”).Range(“B1”).PasteSpecial Paste =xlValues
‘ Save workbooks
objWorkbook1.Save
‘ Close Workbooks
objWorkbook1.close
objWorkbook2.Close
‘ Close Excel
objExcel.Quit
‘ Release objExcel
set objExcel=nothing
End Function
Function cp_wkBook2()
‘Initialize Excel Object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘ Open workbook to which data has to be copied – target Workbook
Set objWorkbook1= objExcel.Workbooks.Open(“D:\FA_Automation\BT_1\BT1.xls”)
‘ Open workbook from which data has to be copied – Source Workbook
Set objWorkbook3= objExcel.Workbooks.Open(“D:\FA_Automation\BT_1\UCA.xls”)
‘Copy data from all the cells used
objWorkbook3.Worksheets(“UCA”).UsedRange.Copy
‘Copy data to C1 location of Sheet 1
objWorkbook1.Worksheets(“Sheet1”).Range(“C1″).PasteSpecial Paste =xlValues
‘ Save workbooks
objWorkbook1.Save
‘ Close Workbooks
objWorkbook1.close
objWorkbook3.Close
‘ Close Excel
objExcel.Quit
‘ Release objExcel
set objExcel=nothing
End Function
‘ Call Copy functions
cp_wkBook1()
WScript.Sleep(5000)
cp_wkBook2()
WScript.Sleep(5000)
‘ Function to get data from the final Workbook
Function GetData()
Dim x, strTemp, objExcel, objWB
Dim tableBegin,tableEnd, tableHeader
tableBegin=”
| Division | TCA | UCA |
|---|
”
Set objExcel = Wscript.CreateObject(“Excel.Application”)
Set objWB = objExcel.Workbooks.Open(“D:\FA_Automation\BT_1\BT1.xls”)
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
‘ Make Excel visible while debugging
objExcel.Visible = True
‘ This is the row of our first cell.
x = 1
do while objExcel.Cells(x, 1).Value <> “”
strTemp = strTemp & “
”
strTemp = strTemp & “
”
strTemp = strTemp & “
”
x = x + 1
loop
‘ This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True
‘ Close the workbook and exit the application.
objWB.Close
objExcel.Quit
set objWB = Nothing
set objExcel = Nothing
strTemp = tableBegin & strTemp & tableEnd
GetData = strTemp
End Function
‘ This is our main function.
Set objMessage = CreateObject(“CDO.Message”)
objMessage.Configuration(“mail.agili.com”)
objMessage.Subject = “FA Automation BT1 – ” & Date & Space(2) & Time
objMessage.From = “psss@agili.com”
objMessage.To=”rah@agili.com”
objMessage.AddAttachment “D:\FA_Automation\BT_1\BT1.xls”
objMessage.HTMLBody = GetData & “
“&”
”
‘send email
objMessage.Send
