Tuesday, March 27, 2012

Copy Excel sheet content into Another Excel sheet

''*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
''* Function : Copy_ExcelContent(Source_File, Source_Sheet,Destination_File, Destination_Sheet)
''* Description : Copy the Excel file content from sourcesheet into Desired File and destination sheet
''* Developed By : Baba Fakruddin D
''* Developed on : 20-Mar-2012
''* Input : SourceFile, SourceSheet, DestinationFile,DestinationSheet
''* Output : DestinationFile Path
''* Syntax : CallCopy_ExcelContent(InputDataPath&"Department_csv.xls","Department_csv", "", "Department_csv")
''*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Function Copy_ExcelContent(Source_File, Source_Sheet,Destination_File, Destination_Sheet)
'KillProcess "EXCEL.EXE"
Dim xl, fso, bln_Flag, xl1, xl2, Paste, xlValues
Set fso = createobject("scripting.filesystemobject")
Set xl = createobject("Excel.application")
xl.Visible= true
xl.DisplayAlerts= False

If Destination_File <> "" Then
If (fso.FileExists(Destination_File)) Then
Set xl1 = xl.Workbooks.Open(Cstr(Destination_File))
bln_Flag= True
Else
Setxl1 = xl.Workbooks.Add
bln_Flag= False
End If
Else
Set xl1 = xl.Workbooks.Add
bln_Flag= False
End If

Set xl2 = xl.Workbooks.Open(Cstr(Source_File))
xl2.Worksheets(Cstr(Source_Sheet)).usedrange.Copy

If Destination_Sheet <> "" Then
xl1.ActiveSheet.Name= Destination_Sheet
xl1.WorkSheets(Cstr(Destination_Sheet)).Range("A1").PasteSpecial Paste = xlValues
Else
xl1.ActiveSheet.Range("A1").PasteSpecial Paste = xlValues
End If

If Not bln_Flag Then
If Destination_File <> "" Then
xl1.SaveAsDestination_File
Copy_ExcelContent= Destination_File
Else
If fso.FileExists(InputDataPath&"TempFile.xls") Then
fso.DeleteFile(InputDataPath&"TempFile.xls")
Wait1
End If
xl1.SaveAsInputDataPath&"TempFile.xls"
Copy_ExcelContent= InputDataPath&"TempFile.xls"
End If
Else
xl1.Save
Copy_ExcelContent= Destination_File
End If

xl1.Close
xl2.Close
xl.Quit

Set xl1 = nothing
Set xl2 = nothing
Set xl = nothing
Set fso = nothing
End Function

''*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

No comments:

There was an error in this gadget