Change Excel paper size

[KABEL]

Hello everyone,

I'm working on VBA CATIA project, that need to be connected with Excel to save Data, and I do have to adjust the page size to A4 or A3 depend on the result of OptionButtons (pict bellow ), I tried this code but I got this error message "Run Time error 1004" 'impossible to define the property of PaperSize

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
With AppliExcel.ActiveSheet.PageSetup
       .PaperSize = xlPaperA4
End With
AppliExcel.Application.PrintCommunication = True
AppliExcel.ActiveWindow.View = xlPageLayoutView
End If
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
With AppliExcel.ActiveSheet.PageSetup
       .PaperSize = xlPaperA3
End With
AppliExcel.Application.PrintCommunication = True
AppliExcel.ActiveWindow.View = xlPageLayoutView
End If
End Sub

Public AppliExcel As Object

Private Sub UserForm_Initialize()
Set AppliExcel = CreateObject("Excel.Application")
AppliExcel.Visible = True
AppliExcel.Workbooks.Add
End Sub

Does anyone have any suggestions what wrong it is?
Thanks in advance for any suggestions
regards,

 

[itsmyjob]

replace

With AppliExcel.ActiveSheet.PageSetup

with

With CATIA.ActiveDocument.ActiveSheet.PageSetup

Eric N.
indocti discant et ament meminisse periti ​

 

[KABEL]

Hi, itsmyjob
Thank you for your response,

I tried your suggestion but its still not working, I got this error message " Automation error ",
I'm launching Excel from CATIA, and I need to set the excel page size to A4 or A3 as mentioned above

regards,

 

[ferdo]

https://docs.microsoft.com/en-gb/office/vba/api/Excel.PageSetup.PaperSize

Regards
Fernando

https://picasaweb.google.com/102257836106335725208

- Romania

https://picasaweb.google.com/103462806772634246699/

- EU

 

[KABEL]

I've tried this :
'******My Main Code

Public optionA4 as boolean
Public optionA3 as boolean
'--------------------------------
Sub CATMain()
dim AppliExcel As Object
Set AppliExcel = CreateObject("Excel.Application")
AppliExcel.Visible = True
AppliExcel.Workbooks.Add
'******
UserForm1.Show
If OptionA4 = True Then
   [COLOR=#000000][highlight #FCE94F]AppliExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4[/highlight][/color]
End If
       
If OptionA3 = True Then
   [COLOR=#000000][highlight #FCE94F]AppliExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA3[/highlight][/color]
End If  

AppliExcel.Application.PrintCommunication = True
[highlight #FCAF3E][COLOR=#000000]AppliExcel.ActiveWindow.View = xlPageLayoutView[/color][/highlight]

End Sub

'***for the userform

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
  optionA4 = True
  Unload UserForm1
End If
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
  optionA3 = True
  Unload UserForm1
End If
End Sub

ferdo :
I've already checked the microsoft docs,I used the same in my code but I still get the same error, for the Yellow line :
Unable to set the PaperSize property of the PageSetup class
,
and also I get this error on the orange line : Application-defined or object-defined error

Thanks in advance for any suggestions
regards,

 

[KABEL]

Thanks everyone I resolved the problem by replacing xlPaperA4 with the number 9 and xlPaperA3 with 8, and xlPageLayoutView with 3
For this reply,I just wanna share with you my solution, it works well now

regards,

 

[LWolf]

just a comment, KABEL: xlPaprA4 is just a constant that you have to define yourself... cf xltop
you usually do it at the beginning of your module:

Const xlToRight = -4161
Const xlDown = -4121
Const xlUp = -4162
Const xlValidAlertStop = 1
Const xlValidateList = 3
Const xlListSeparator = 5​

https://docs.microsoft.com/en-us/office/vba/api/excel.xlpapersize

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.constants?view=excel-pia

regards,
LWolf

 

[KABEL]

LWolf
thank you for your kind information,I really appreciate it;
have a good day,
regards,
KABEL