Sign in to follow this  
Followers 0
Andreik

Insert large data in excel cell

11 posts in this topic

I'm using excel udf to write some data in specific cells. Unfortunately I have to put there images also and here the problems starts. I'm not necessarily interested to see the images in excel so I encode them base64 but data leght is far too much for an excel cell, whici is limited to 32,767 characters. What alternatives I have?


When the words fail... music speaks

Share this post


Link to post
Share on other sites



Write them as shapes to a sheet and then hide this sheet.


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

Will this work well if I have 20000 rows and for each row I have 3 images? Maybe the most normal method would be to split data in chunks of 32,767 characters and write them in multiple cells. Of course it's not pretty but may work. I'm still open to any suggestion.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

Shapes aren't related to cells. I understand it as they are placed on another layer. You can place them wherever you like on the sheet.

I have never done this myself but I would try the following:

Place the image on another worksheet. To make it invisible you can either set the image to visible=False or the whole worksheet

Set the name of the image like "row-column" so you have a connection between a cell and an image.

You can access each image by using this name.

If you like I can post an example.


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

Sounds good, I'll try this method.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

If you like I can provide an example.


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

Example:

$sFile = @ScriptDir & "\test.jpg"
Global $oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = True
$oWorkbook = $oExcel.Workbooks.Add()
$oExcel.Sheets.Add()
$oWorksheet = $oWorkbook.Sheets(2)
$oPicture = $oWorksheet.Shapes.AddPicture($sFile, -1, -1, 1, 1, 0, 0)
$oPicture.Scalewidth(1, -1, 0)
$oPicture.Scaleheight(1, -1, 0)
$oPicture.Visible = False
$oPicture.Name = "001-003" ; Example for: Row 1, column 3
MsgBox(0, "", "Press enter to set visibility to true")
$oWorksheet.Shapes.Item("001-003").Visible = True
$oExcel.Close
Exit

Creates an Excel Object

Adds a workbook

Adds a worksheet

Adds a picture to worksheet 2

Sets the size of the picture

Makes the picture invisible

Adds a name ("Row-Column") to the picture

Accesses the picture by name ("Row-Column") and makes it visible.


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

Thank you! That's pretty cool, just one more question. Could I insert the image from binary data? If this is not possible it's not a big deal to write in temporary files the images before to insert them in excel.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

I don't think so because the Add method requires a filename.


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

I already saw some options using clipboard and Paste method. Thank you very much!


When the words fail... music speaks

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

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • genius257
      By genius257
      I've made a library, based on AutoItObject UDF with the goal of implementing getter and setter functionality and make it possible to define new object properties in as few steps as possible.
      Thank you to @trancexx for getting me on the right track, and all users in Hooking into the IDispatch interface for the code to get me going.
      If I've forgotten to add credit, please let me know
      Example:
      #include "AutoItObject_Internal.au3" $myCar = IDispatch() $myCar.make = 'Ford' $myCar.model = 'Mustang' $myCar.year = 1969 $myCar.__defineGetter('DisplayCar', DisplayCar) Func DisplayCar($oThis) Return 'A Beautiful ' & $oThis.parent.year & ' ' & $oThis.parent.make & ' ' & $oThis.parent.model EndFunc MsgBox(0, "", $myCar.DisplayCar) More examples: https://github.com/genius257/AutoIt-projects/tree/master/AutoItObject Internal/Examples
      Version: 1.0.3
      AutoItObject_Internal.au3
      Documentation:
       

      Edit2 (19th March 2017):
      First of all, sorry about the lack of updates on this project. I always start too many projects and end up ignoring old projects, if I run into problems ^^'.
      So I've started moving my AutoIt scripts to GitHub. I will still post the most recent script version here.
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.