Sign in to follow this  
Followers 0
Syed23

Finding difficulty on _ExcelSaveAs function

18 posts in this topic

Hi All,

I am finding difficulty on using the function _ExcelBookSaveAs. I am trying to save the opened excel as PDF file on my desktop where i am unable to do that. could you please someone help me on this ? example code is below!

$open = _ExcelBookOpen(@Desktopdir&"\Change log.xlsx")

Local $save = _ExcelBookSaveAs($open, "C:\temp\ExcelTest","pdf")

If Not @error Then

_FileWriteLog($Excellog,"INFO"&""&">>"&"Excel Save Function>>"&"Excel has been saved successfully")

Else

_FileWriteLog($Excellog,"ERROR"&""&">>"&"Excel Save Function>>"&"Unable to Save an excel work book and the occured error is "& @error)

EndIf

;end of saving excel sheet


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites



Is export to PDF supported in your version of Excel? You might have to print to a PDF driver like "CutePDF Writer" instead.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

sorry or troubling! can you bit clear? i couldn't understand...


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

i am able to print the excel as PDF successfully in my version of EXcel. What i found is Excle UDF is not having the detail to save as PDF... for _ExcelBookSaveAs has the option to save the file as (txt,xls,html,template,csv) so it doesnt have the information to save as PDF. if you can give const value for PDF or if you can explain me how you have declared the values for the variables... so that i can modify the header file and will give a try...


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

If you have Excel 2007 you can find the exact format of the save method here. The "Save as PDF" add-in has to be installed.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks a lot water...if i am using Office 2010 in that case how can i check the vaue ?


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

Thanks a lot water! you made my work so simple..... :)


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

Thanks a lot water...if i am using Office 2010 in that case how can i check the vaue?

In Excel 2010 you have to use the ExportAsFixedFormat method as well. But as far as I know there is no need for the add-in any more.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

it worked fine Water! Thank you so much... i have one more help..can you help me on this please! I am just trying to insert image to the excel.. for that i am trying to modify the excel UDF by adding new function.. since i don't have much experiance on it again i got strucked.. can u help me on this? below is the link which i am refering..

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pictures.insert(v=office.14).aspx


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Please have a look at the extended Excel UDF

There you'll find function_ExcelPictureInsert which does what you need.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Hi Water,

As per your suggesstion i have downloaded the _ExcelCOM_UDF.AU3 and i have tried. but where the return value shows that picture inserted successfully... but i couldn't see the image on the excel sheet... also i tried whether the function works by assigning the wrong file name of image...even i get the return value as "True". Could you please help me on this?

Local $addsheet = _ExcelSheetAddNew($oExcel, "New Sheet Example")

Local $pict_insert = _ExcelPictureInsert($addsheet, @DesktopDir&"\QA3.jpg", 10, 10, 18, 18, True, True)

If $pict_insert <> @error Then

_FileWriteLog($Excellog,"INFO"&""&">>"&"Excel Add Image Function>>"&"Image has been added to the Sheet successfully")

Else

_FileWriteLog($Excellog,"ERROR"&""&">>"&"Excel Add Image Function>>"&"Function unable to add a Image to the workbook and the occured error is "& @error)

EndIf

;End of add sheet Function

Sleep(5000)

_ExcelBookClose($oExcel)

Sleep(2000)

Edited by Syed23

Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Hi Syed,

I'm on my Ubunto PC right now so I can't check the return value and error codes of the ExcelCOM_UDF.au3. But I think the following line is wrong:

If $pict_insert <> @error Then

Shouldn't it read:

If @error <> 0 Then Msgbox(0,"Error","Error inserting picture: @error: " & @error & ", @extended: " & @extended)

Edit: Missed the 0 after the compare operator.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Shouldn't it read:

If @error <> Then Msgbox(0,"Error","Error inserting picture: @error: " & @error & ", @extended: " & @extended)
Or maybe:
If @error Then Msgbox(0,"Error","Error inserting picture: @error: " & @error & ", @extended: " & @extended)
Without the compare operator "<>".

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

you guys are correct! now i am getting the error message value as "1"


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

Hi water,

Did you get a chance to have a look on the _ExcelPictureInsert issue... i am unable to insert the image on excel sheet...any idea? i am using Office 2007 and 2010 machines.


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites

Hi Sysed,

just checked my script and noticed that I didn't use the function but coded it myself:

; Insert picture
$oExcel.ActiveSheet.Pictures.Insert(@ScriptDir & "\Logo.jpg" ).Select
$oExcel.Selection.Height = 44.57
$oExcel.Selection.Width = 391.71
$oExcel.Selection.Left = 40
$oExcel.Selection.Top = 25

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

This is working perfectly! Awesome code..Thanks a lot Water!


Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Share this post


Link to post
Share on other sites
:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0