VBA Excel copy from another worksheet

[kyletreyfield]

I know there are multiple threads on this topic but no matter what I try I cannot get it to work. I am simply trying to copy some excel cells from another worksheet into my current worksheet, go to a different spot, copy another worksheet, etc.

I keep getting the error that says 'Unable to get the Paste property of the Worksheet class'
Here is my code:

oX=CreateObject("EXCEL.APPLICATION")
WITH oX
oX.Visible=.T.
oX.Workbooks.Open("c:\rfportal\combine_project\rfps\&rfpvartext\master.xlsm")
SELECT dirtable
GO TOP
DO WHILE .NOT. EOF()
oX.Run("MergeWorkbooks",TRIM(dirtable.folder))
SKIP
ENDDO
oX.ActiveWorkbook.SaveAs('c:\rfportal\combine_project\rfps\&rfpvartext\final\&final_spreadsheet')
oX.Workbooks.Open("c:\rfportal\combine_project\rfps\&rfpvartext\final\&final_spreadsheet")
oX.WINDOWS("&final_spreadsheet").ACTIVATE

SELECT these_prods
GO top
DO WHILE NOT EOF()
STORE ALLTRIM(these_prods.product) TO prodvar
STORE prodvar+".xls" TO prod_xls

oX.Sheets("&prod_xls").Select
oX.Rows("1:1").Select
oX.Selection.Delete
oX.Columns("A:A").Select
oX.Selection.Delete
oX.Rows("2:9").Select
oX.Selection.Insert
oX.Range("b2").Select

SELECT *;
from mixtable;
WHERE ALLTRIM(mixtable.product)==prodvar;
INTO CURSOR thismix
STORE RECCOUNT() TO thiscount
GO top
DO WHILE NOT EOF()
STORE ALLTRIM(thismix.ratefile) TO thisratefile
oX.Workbooks.Open("c:\rfportal\combine_project\rfps\&rfpvartext\final\rates\&thisratefile")
oX.WINDOWS("&thisratefile").ACTIVATE

loSheet = .activesheet
With loSheet
loSheet.Range("A1:A5").Copy
loSheet.Application.CutCopyMode = False
endwith

oX.Activesheet.Paste
oX.ActiveCell.Offset(0, 1).Select

SELECT thismix
SKIP
ENDDO

oX.Cells.Select
oX.Selection.Columns.AutoFit
oX.Sheets("&prod_xls").Select
oX.Sheets("&prod_xls").Name = "&prodvar"

SELECT these_prods
SKIP
ENDDO

oX.ActiveWorkbook.SaveAs('c:\rfportal\combine_project\rfps\&rfpvartext\final\final_spreadsheet2')
oX.ActiveWorkbook.Close
oX.Quit

ENDWITH

 

[Mike Lewis]

I won't try to answer your question, but I would like to make a couple of suggestions that I think will help you.

First, you are expecting us to read and understand some 70 lines of un-commented code, with no context or explanation.That's quite a lot to expect, especially as we can't run the code because we don't have access to your data. So, my first suggestion is: just post the minimum code we need to understand the problem.

Also, post either the code or the steps we need to take to reproduce the problem. Given that the problem is connected with copying and pasting, it should be possible to do that without reference to any specific tables or worksheets.

Next, when posting code, indicate which line is triggering the error (I can probably guess that it is the line containing the Paste command, but it would have been helpful for you to make that clear).

Finally, for goodness sake learn how to format the code that you post. It's very easy. Just use the "Code" button on the toolbar (just above the edit area). Doing that will preserve indentations and spacing in your code, making it much easier to follow the logic.

I feel sure that if you take on these suggestions, you will get a much better response in the forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kyletreyfield]

My apologies, Mike (and everyone else); thats one (of many) reasons why I listed myself as 'technical user' instead of 'programmer'

I believe this is the code in question:

oX.Workbooks.Open("c:\rfportal\combine_project\rfps\&rfpvartext\final\&final_spreadsheet")
oX.WINDOWS("&final_spreadsheet").ACTIVATE
oX.Workbooks.Open("c:\rfportal\combine_project\rfps\&rfpvartext\final\rates\&thisratefile")
oX.WINDOWS("&thisratefile").ACTIVATE

loSheet = .activesheet
With loSheet
loSheet.Range("A1:A5").Copy
loSheet.Application.CutCopyMode = False
endwith

oX.Activesheet.Paste

I appreciate your advice.

 

[Mike Lewis]

Ah, that's much better. Straight away, I can see some problems:


loSheet = .activesheet
That's not valid syntax. You need to have something before the dot - probably oX.

loSheet.Application.CutCopyMode = False

False is not a valid word in Foxpro. You need to change it to .F.

In any case, I'm not sure that it's correct to set it to .F. My understanding is that doing so will remove the range that you are copying from, which is probably not what you want (but I'm not sure about that).

oX.Activesheet.Paste

That will paste into the current range, which you have not set. You need to specify the target - the place to which you are pasting, like so:

oX.Activesheet.Paste("B1:B5")

None of the above directly solves the problem, but at least it eliminates some other issues and enables the code to run properly.I suggest you fix these issues and report back.

Also, what is contained in &final_spreadsheet and &thisratefile ?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You might try something like this:

oXL= CREATEOBJECT("excel.application")
oWork1 = oXL.workbooks.open("c:\test.xls")   && <= insert your source filename here
oSheet1 = oWork1.ActiveSheet
oWork2 = oXL.workbooks.open("c:\test1.xls")  && <= insert your destination filename here
oSheet2 = oWork2.ActiveSheet
oXL.Visible = .t.
oSheet1.Activate
oSheet1.Range("A1:A5").Select
oXL.Selection.Copy
oSheet2.Activate
oSheet2.Range("C1").Select
oSheet2.Paste

If it works, you should see the first five cells from Col. A from the source sheet copied to the first five cells in Col. C in the destination sheet.

Let me know what happens.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kyletreyfield]

Mike, works like a charm! Can't thank you enough, as usual.

Again, I apologize for the bad coding etiquette.

 

[Mike Lewis]

Glad to hear it is working - and no need to apologise. We all have to start from somewhere.

By the way, the code I posted didn't include anything to save the workbook and quit from Excel. You will need something like the following at the end of the code:

oWork2.SaveAs("c:\new.xls")  && <= change filename as appopriate
oWork2.Close
oXL.Quit
RELEASE oXL

The Quit is important, otherwise you could end up with an invisible Excel staying in memory until your next reboot.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads