mspx
asked on
Excel Paste from VBA
I am actually writing a small Visual FoxPro program to automate activities in Excel. Using COM I have created an Excel object in my FoxPro program.
loExcel = createobject('Excel.Applic
The following code then shows Excel and adds a workbook.
loExcel.visible = .t.
loExcel.Workbooks.Add
The loExcel object acts as an Excel Application in that you can programmatically reference properties and execute commands such as show,add or any Excel function.
What I want to now do is to paste the current contents of the clipboard into the Excel WorkBook, select and copy this range, move down one row and PasteSpecial. I have tried things like:
loExcel.WorkBooks('Book1')
but I am not really familiar with the correct VBA syntax required.
Does anyone know how I can do the tasks described programatically?
loExcel = createobject('Excel.Applic
The following code then shows Excel and adds a workbook.
loExcel.visible = .t.
loExcel.Workbooks.Add
The loExcel object acts as an Excel Application in that you can programmatically reference properties and execute commands such as show,add or any Excel function.
What I want to now do is to paste the current contents of the clipboard into the Excel WorkBook, select and copy this range, move down one row and PasteSpecial. I have tried things like:
loExcel.WorkBooks('Book1')
but I am not really familiar with the correct VBA syntax required.
Does anyone know how I can do the tasks described programatically?
Hello mspx,
as HF already mentioned it's the worksheet or range object harbouring the paste/special methods
you can look up anything you need in the object browser in excel
-open excel
-open the vb editor with alt+f11
-next open the object browser with f2
-browse the object/props/meths
HAGD:O)Bruintje
as HF already mentioned it's the worksheet or range object harbouring the paste/special methods
you can look up anything you need in the object browser in excel
-open excel
-open the vb editor with alt+f11
-next open the object browser with f2
-browse the object/props/meths
HAGD:O)Bruintje
ASKER
Thanks for your comments so far. I have been able to paste the clipboard contents to the Worksheet. I then select a Range('A2'). Manually I can then Select Paste Special and Transpose within Excel to get the desired result. However, I need to do this programmatically as mentioned.
I have tried:
Selection.PasteSpecial xlAll,xlNone,False,True
and
loSheet.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Both cause FoxPro to throw a syntax error. Am I phrasing the PasteSpecial command correctly?
I have tried:
Selection.PasteSpecial xlAll,xlNone,False,True
and
loSheet.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Both cause FoxPro to throw a syntax error. Am I phrasing the PasteSpecial command correctly?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try something like:
IoExcel.WorkBooks('Book1')
IoExcel.WorkBooks('Book1')
However, it might be easier if you can instantiate objects that reprsent the child classes (Workbook and WorkSheet) Not too sure how VBA would cope with such a long set of parameters....
HF