?
Solved

Excel Paste from VBA

Posted on 2003-02-20
4
Medium Priority
?
692 Views
Last Modified: 2008-02-01
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.Application')

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').Paste

but I am not really familiar with the correct VBA syntax required.

Does anyone know how I can do the tasks described programatically?


0
Comment
Question by:mspx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:Howler_Fish
ID: 7990410
I think the paste method belongs to the Worksheet class rather than the workbook class.

Try something like:

IoExcel.WorkBooks('Book1').Activesheet.paste
IoExcel.WorkBooks('Book1').Activesheet.Activecell.offset(1,0).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

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
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7991735
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
0
 

Author Comment

by:mspx
ID: 8021738
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?
0
 
LVL 1

Accepted Solution

by:
Howler_Fish earned 300 total points
ID: 8022086
I think that FoxPro may think that the " Paste:=xlAll" etc. is part of a command to foxpro rather than parameters to be passed to the PasteSpecial method.

The closest I can think of would be to create a method within your Excel source which does the PasteSpecial for you.  You could then call IoSheet.MyPasteFunction.

If you wanted to change the paste location (ie two cells down, one across) you could make them parameters of your method, eg IoSheet.MyPasteFunction(2,1)

Your method could look something like :

WorkBooks('Book1').Activesheet.Activecell.offset(Parameter1,Parameter2).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Cheers,

HF
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question