×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Change Excel paper size

Change Excel paper size

Change Excel paper size

(OP)
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



CODE --> vba

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,

RE: Change Excel paper size

replace

With AppliExcel.ActiveSheet.PageSetup

with

With CATIA.ActiveDocument.ActiveSheet.PageSetup

Eric N.
indocti discant et ament meminisse periti

RE: Change Excel paper size

(OP)
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,

RE: Change Excel paper size

(OP)
I've tried this :
'******My Main Code

CODE --> VBA

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
   AppliExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
End If
       
If OptionA3 = True Then
   AppliExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA3
End If  

AppliExcel.Application.PrintCommunication = True
AppliExcel.ActiveWindow.View = xlPageLayoutView

End Sub 

'***for the userform

CODE --> VBA

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,

RE: Change Excel paper size

(OP)

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,

RE: Change Excel paper size

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close