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

Resolved Error using Excel VBA

  1. How-to
0
Votes
Undo
  1. Jeong Hwan Jang
    Jeong Hwan Jang
  2. PowerBuilder
  3. Wednesday, 19 July 2023 05:33 AM UTC

Hi, everyone.

I'm using PB 2019.

I made a datawindow with no column head.

I try to save the datawindow to excel file 'empty.xls'.

After saving, I try to copy all rows in sheet1 of 'empty.xls' and paste it to another excel file 'forms.xls'.

I wrote titles of each columns in first row of 'forms.xls'.

So I try to paste it from on second row in sheet1 of 'forms.xls'.

The scripts are as below.

------------------------------------------------------------------------------------------------------------------------
OleObject myoleobject

Int rtn
String ls_file, ls_file_1, ls_title_excel_nm, ls_title_sheet_nm


ls_file = GetCurrentDirectory() + "\empty.xls"

myOleObject = Create OleObject
rtn = myoleobject.ConnectToNewObject("excel.application")

dw_list.SaveAsAscii(ls_file,"~t","")

myOleObject.Application.Visible = False

IF rtn = 0 THEN
    myOleObject.WorkBooks.Open(ls_file)

    ls_file_1 = GetCurrentDirectory() + "\forms.xls"

    myOleObject.WorkBooks.Open(ls_file_1)
ELSE
    Messagebox("!", "Failed!")

    Destroy myoleobject

    Return
End If

Long li_F_Row_Start, li_F_Row_End, li_T_Row_Start, li_T_Row_End
String ls_Copy_Rows, ls_Paste_Rows

li_F_Row_Start = 1
li_F_Row_End = dw_list.rowcount()

li_T_Row_Start = li_F_Row_Start + li_title_rows
li_T_Row_End  = li_F_Row_End + li_title_rows

ls_Copy_Rows = String(li_F_Row_Start) + ':' + String(li_F_Row_End)
ls_Paste_Rows = String(li_T_Row_Start) + ':' + String(li_T_Row_End)

myoleobject.Application.Workbooks('empty.xls').WorkSheets(1).Activate
myoleobject.Application.Workbooks('empty.xls').WorkSheets(1).Rows(ls_Copy_Rows).Select
myoleobject.Application.Selection.Copy

myoleobject.Application.Workbooks('forms.xls').WorkSheets(1).Activate
myoleobject.Application.Workbooks('forms.xls').ActiveSheet.Rows(ls_Paste_Rows).PasteSpecial(-4163, -4142, false, false)
myoleobject.Application.CutCopyMode = False

myoleobject.Application.Workbooks('empty.xls').close

myOleObject.Application.Visible = True
myoleObject.windowstate = 3

DESTROY myOleObject


------------------------------------------------------------------------------------------------------------------------

This is working well on PC with Excel version 2013, OS Win 11.

But on PC with Excel 2019 OS Win 11, error has occured like this.

"Error accessing external object property select at line 00"


Is VBA ".select" not works with Excel 2019 ??



I'm looking forward to your help.

Thanks.

HTTPClient.SendRequest returns +3
Advice on "doubleclicking" a commandbutton
Responses (3)
Mark Goldsmith
Mark Goldsmith Accepted Answer Pending Moderation
  1. Wednesday, 19 July 2023 18:41 PM UTC
  2. PowerBuilder
  3. # 1

Hi Jeong,

The problem is that Rows is neither a property or a method of the Worksheets object so when you use it like this:
myoleobject.Application.Workbooks('empty.xls').WorkSheets(1).Rows(ls_Copy_Rows).Select it's going to fail. Rows, however, is a property of the Worksheet (singular) object and so you can use it that way.

There's a number of ways to accomplish this but what might be the easiest is to assign that worksheet to another ole object variable, something like:

ole_worksheet = myoleobject.Application.Workbooks('empty.xls').WorkSheets(1) and then use the Select and Copy (actually, you don't even need the Select method) on that object.

I know you didn't get to the pasting part of your code because of the previous error but you'll also have an error there as well. ActiveSheet is neither a property or a method of the Workbooks object. It is, however, a property of the WorkBook (singular) object.

HTH...regards,

Mark

Comment
Jeong Hwan Jang
  1. Jeong Hwan Jang
  2. Friday, 21 July 2023 08:02 AM UTC
Hi Mark,



I fixed the problem through changing like this,

------------------------------------------------------------------------------------------------------------------------------

myoleobject.Application.Workbooks('empty.xls').WorkSheets(1).Rows(ls_Copy_Rows).Select

myoleobject.Application.Selection.Copy



=> myoleobject.Application.Workbooks('empty.xls').WorkSheets(1).Rows(ls_Copy_Rows).Copy

------------------------------------------------------------------------------------------------------------------------------

I think ".Select" may not be a method for Excel 2019 VBA.



Thanks to your help.



Regards, Jang

  1. Helpful
Mark Goldsmith
  1. Mark Goldsmith
  2. Friday, 21 July 2023 14:12 PM UTC
Thanks for the update Jang and glad to hear you resolved it.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 21 July 2023 18:48 PM UTC
hmmm, I adviced you to record a macro and edit it to look at the code of it. You said you did. Wouldn't that have shown the correct syntax in that case?

Anyway, glad you solved it.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 19 July 2023 11:16 AM UTC
  2. PowerBuilder
  3. # 2

Hi Jeong;

  I had a support ticket a few months ago on OLE with Microsoft office where the OLE stopped working. I Googled around and found that the object model (DOM) had changed in the newer Office release. After adjusting for that, their code started working again.  HTH

Regards ... Chris 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 19 July 2023 05:46 AM UTC
  2. PowerBuilder
  3. # 3

Hi,

I don't know Excel 2019, but MS keeps changing some visual basic commands for almost every new version.

1. Open Excel 2019 and record a macro that does what you want to do.

2. Then edit the macro to see the VBA and what command you have to use with OLE

regards.

Comment
Jeong Hwan Jang
  1. Jeong Hwan Jang
  2. Wednesday, 19 July 2023 06:53 AM UTC
As you said, I recorded a macro that copy rows on Excel 2019.

and the VBA after edit the macro is as below



-------------------------------------------------------------

Sub Macro_test()

Rows("1:15").Select

Selection.Copy

End Sub

-------------------------------------------------------------

I don't know the difference between this and my scripts.
  1. Helpful
Jeong Hwan Jang
  1. Jeong Hwan Jang
  2. Wednesday, 19 July 2023 07:05 AM UTC
Anyway thanks for your answer, Miguel.



  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.