Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved OLE excel automation

  1. How-to
0
Votes
Undo
  1. John Vanleeuwe
    John Vanleeuwe
  2. PowerBuilder
  3. Friday, 10 August 2018 12:09 PM UTC

Dear all,

 

We are making some changes to a function we created. This function will take any datawindow , and with the saveasformattedtext function we can convert this datawindow into a temp excel file.

then we are using ole automation to select all the records available in our temp excel file and we paste this on another excel file , more specifically on a certain sheet , lets say sheet 3

At the moment all of this works just fine & flawless...

 

The change we want to make today is the following.

At the moment the data copied from clipboard to the excel file and sheet 3 is being pasted in/at starting cell A1 ( left top corner).

 

We now would like to extend our function and start pasting in our excel file let's say for instance from cell A8.

 

How can we accomplish this please ?

 

I have tried to make it work by setting the range to A5 , but this doesn't do the trick. Is what i am asking feasible or is the excel sheet Paste function always pasting its information starting in cell A1 and we are simply not able to define the "insertion point" of the paste function ?

 

 

 

TIA

John

 

 

 

2017 R3 Questions
[Hidden] Menu shortcuts works with Ctrl, not with ...
Accepted Answer
Bruce Armstrong
Bruce Armstrong Accepted Answer Pending Moderation
  1. Friday, 10 August 2018 17:08 PM UTC
  2. PowerBuilder
  3. # Permalink

You can't paste into a range, but you can change the initial point you are attempting to paste into.

Generally one of the fastest ways to figure out what you need to do is record a macro in Word and then convert that to PowerScript OLE calls.

Try just setting the current cell location to A8 and then doing your paste.  It's pasting into A1 because that's the default current cell position when the sheet is opened.

Comment
There are no comments made yet.
Responses (4)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 11 August 2018 22:34 PM UTC
  2. PowerBuilder
  3. # 1

If have recorded a macro doing the following:

 

1) Position the cursor in the upper left cell by doing a ctrl+home

2) press ctrl+shift+end keys at the same time

3) press ctrl+C

4) go to second sheet and click in cell A8

5) hit Enter key to paste the copied text.

 

This is the Macro it generates:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

The text is pasted starting in the cell A8, I'm not sure if that might do the trick for you?

 

regards,

MiguelLeeuwe

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 11 August 2018 22:43 PM UTC
Just to clarify: the Ctrl+Home resulted in my case in positioning in A3 and not A1, that's because there's an applied filter on my excel sheet (didn't realize that), but it does work.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 11 August 2018 22:44 PM UTC
( I'm using Office 2016)
  1. Helpful
There are no comments made yet.
Luiz Ribeiro
Luiz Ribeiro Accepted Answer Pending Moderation
  1. Friday, 10 August 2018 19:44 PM UTC
  2. PowerBuilder
  3. # 2

Hi John.

 

Try changing the way you are copying the data as below:

aoo_sheet.Range("A1:K20").Copy

 

If the number of rows and columns to copy are unknown, try this:

aoo_sheet.Range("A1").Resize(aoo_sheet.UsedRange.Rows.Count, aoo_sheet.UsedRange.Columns.Count).Copy

 

Regards,

 

Luiz

Comment
There are no comments made yet.
John Vanleeuwe
John Vanleeuwe Accepted Answer Pending Moderation
  1. Friday, 10 August 2018 15:26 PM UTC
  2. PowerBuilder
  3. # 3

Hi guys,

 

I found out that what i am trying to do is not possible , not with VBA code , even not manually.

Try to open an excel file , click on the left upper corner to select all the data of the sheet.

Then press CTRL + C

Open new excel file and try to paste in any other column than A1 :> we receive an excel error message...

 

Guess we have to start looking for an alternative solution.

 

 

Grts

John

 

 

Comment
Brad Mettee
  1. Brad Mettee
  2. Saturday, 11 August 2018 19:52 PM UTC
Does selecting all of the data actually select the whole spreadsheet? If so, that's why you can't paste into another at a different starting column. What happens if you only select the data you need?



As an alternative:

We've been using LibXL.DLL (http://libxl.com) for years. It allows you to open spreadsheets and manipulate them directly. Purchase cost per developer is reasonable, and distribution of the dll is free. Building NVOs to encapsulate all of the calls you'll need should be pretty easy.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.