Copy from one excel workbook to another

avikerem

Forums

I have a problem copy selected range of cells from one excel workbook to another using IDEA
The following code sometimes works and sometimes does not. I cannot figure out what is wrong anf how to fix it.
BTW - Using seperate set of source and destination workbook and worksheet objects did not help.
 
any suggestion how to correct the code will be greatly appreciated. THANK YOU
 
Dim ex As Object 'Excel object
Dim wb As Object  'Excel Workbook
Dim ws As Object  'Excel Worksheet
Set ex = CreateObject("Excel.Application")
 Set wb = ex.Workbooks.Open (  "<input excel file> . XLSX")
 lastRow = ex.ActiveSheet.Range("A1").CurrentRegion.Rows.Count
ex.ActiveSheet.Range("$A$1:$C$" & lastRow).Select
ex.Selection.Copy
ex.ActiveWindow.Close
 Set wb = ex.Workbooks.Open ( "<output excel file> . XLSX")
 ex.ActiveSheet.Range("B4").Select
ex.ActiveSheet.Paste
wb.SaveAs "<output excel file> . XLSX", xlsxFileFormat
ex.ActiveWindow.Close
Set ws = Nothing
Set wb = Nothing
ex.quit
Set ex = Nothing

Brian Element Fri, 08/14/2015 - 07:48

Hi Avi, just playing around with it and right now I can't seem to get it to work.  I will need to play some more with it when I have more time.  Did you try the code in Excel and did it work there?  Also I am wondering about the ws and wb variables.  You define them as the active workbook but then use the Excel variable for the sheet.  I am not sure, I will need to test it out, but the sheet comes from the workbook and the workbook comes from the application so I am just thinking out loud if you need another object to hold the sheet instead of using the excel object.

avikerem Fri, 08/14/2015 - 10:21

Hi Brian
No, I could not get it to work although it is an exact copy of the code that does kind of work, which means that probably there are additional environmental considerations such as different way to define the objects involved in the process, I will greatly appreciate if you can find a way to copy from one excel workbook to another and send me a piece of code that does it reliably.
there is no problem to do such a copy of workbook to another workbook from within excel using VB. actually the IDEA code is a port from excel VB code that does work flawlessly. The problem is somewere in the porting of the code or in the IDEA environment.
thanks a lot and have a great weekend.
Avi

Brian Element Fri, 08/14/2015 - 10:41

Hi Avi, thanks for the challenge, I will see if I can figure it out, should be simple but then again ... lol

You also have a great weekend.

Thanks

Brian

Brian Element Sun, 08/16/2015 - 06:24

Hi Avi,

Ok, this seems to be working.  I actually found the base in a script on the IDEA support site for exporting IDEA files to the same excel spreadsheet.  Hopefully it will help you out.

Thanks

Brian

Option Explicit

Sub Main

	Dim oExcel As Object
	Dim oWorkbook1 As Object
	Dim oWorkbook2 As Object
	Set oExcel = CreateObject("Excel.application")
		'copy the workbook
		Set oWorkbook1 = oExcel.Workbooks.Open("C:\Users\Brian\Documents\My IDEA Documents\IDEA Projects\IS Fraud Tool Kit\Exports.ILB\CopyFrom.xlsx")
			oWorkbook1.activesheet.UsedRange.Copy
			oWorkbook1.Close
		Set oWorkbook1 = Nothing
		'save the workbook
		Set oWorkBook2 = oExcel.Workbooks.Open("C:\Users\Brian\Documents\My IDEA Documents\IDEA Projects\IS Fraud Tool Kit\Exports.ILB\CopyTo.xlsx")
			oWorkBook2.ActiveSheet.Paste
			oWorkBook2.Close
		Set oWorkBook2 = Nothing
		oExcel.Quit
	Set oExcel = Nothing
End Sub

 

avikerem Mon, 08/17/2015 - 02:15

Excellent!!    Thanks a lot Brian.
it worked fine.
 
I tried using two different sets of workbook objects few days ago but I probably did not use them correctly and then I switched back to one set, which, as I found out, is not reliable and sometimes does not work. Another probable error in my code is that I used excel.ActiveSheet instead of workbook.ActiveSheet.
again, if this wrong usage was categorically providing wrong answer I may have found this error a long time ago... ( I know, I know. it a terribly lame excuse for sloppy coding)
Cheers,
 
Avi