DIM "export to CSV" - thumbnails question?

jakiblue

I just recently discovered the "export to CSV" function in DIM (yeah yeah I know, I'm slow...LOL)

Works well, but is there any way the thumbnail pictures can be exported as well? So the images show up in the spreadsheet also?

Miss B

There was a similar question here on the forum I noticed the other day, and even though you can "insert" the thumbnails easily enough, I spent some time yesterday checking out all of Excel's functions, and none of them deal with locating and inserting images. I really thought the Lookup function would work, or at least it seemed to in my mind, but it really only looks up "data" within a workbook (perhaps between sheets in the same workbook, but nothing that would check the contents of the column containing the SKU numbers, and then match it with a JPG image in a folder with that same value as the image's name.

Would be nice, that's for sure, but I can't think of anyway to do it with a spreadsheet.

Edited to Add: OK, here's that other thread about the thumbnails in a spreadsheet  --> http://www.daz3d.com/forums/discussion/44068/

jakiblue

Thanks Miss B! I checked out that thread - doesn't look like there's any way to do it other than manually tho. :sigh:

M F M

(hmmm... is it just me, or is there some magic state the DIM needs to be in to be able to enable those various tickboxes in the "CSV Export" dialog... none of the tickboxes will tick for me, when I click them... o.0). DIM v1.1.0.21...

I wrote an Excel script to do it (although I ended up crashing Excel by trying to load ALL the product images from both here and Rendo)... do you use OpenOffice or Excel Jaki?

jakiblue

I use LibreOffice mainly, but I can redownload OpenOffice to use the excel script. :D :D

jakiblue

M F M said:

(hmmm... is it just me, or is there some magic state the DIM needs to be in to be able to enable those various tickboxes in the "CSV Export" dialog... none of the tickboxes will tick for me, when I click them... o.0). DIM v1.1.0.21...

I wrote an Excel script to do it (although I ended up crashing Excel by trying to load ALL the product images from both here and Rendo)... do you use OpenOffice or Excel Jaki?

I'vegot 1.1.0.21 DIM and the tickboxes definitely tick for me. Weird?

M F M

Oh, oops, yes, LibreOffice they're called now, aren't they? <(^_^).</p>

Okay, poking around LibreCalc macrology for a bit...

ETA: definitely weird. Do you have your DIM online or offline Jaki? ... DS running or stopped? ... can you tick boxes on both Installed, as well as Ready to Install panes? ... do you have 8000+ products listed? ... something must be different in our setup >_<...</p>

jakiblue

M F M said:

Oh, oops, yes, LibreOffice they're called now, aren't they? <(^_^).</p>

Okay, poking around LibreCalc macrology for a bit...

ETA: definitely weird. Do you have your DIM online or offline Jaki? ... DS running or stopped? ... can you tick boxes on both Installed, as well as Ready to Install panes? ... do you have 8000+ products listed? ... something must be different in our setup >_<...</div>

Oh, are Libre and Open the same thing? LOL I had no idea. :D

DIM online
DS closed
Both ready to download and installed
No idea how many products I had selected...HEAPS AND HEAPS LOL

M F M

(the little green numbers on the left of the tab names show you the number of products you have ;-).

And hmmmm... I try and do what you just did, and no ticks are registered >_<. Oh well, I'll fudge up some numbers in a spreadsheet to get this macro on the way anyway... now I wonder what the LibreBasic manual is like...</p>

(looks quite similar to VBA at first glance - so this shouldn't present too much issue).

jakiblue

M F M said:

(the little green numbers on the left of the tab names show you the number of products you have ;-).

And hmmmm... I try and do what you just did, and no ticks are registered >_<. Oh well, I'll fudge up some numbers in a spreadsheet to get this macro on the way anyway... now I wonder what the LibreBasic manual is like...</p>

(looks quite similar to VBA at first glance - so this shouldn't present too much issue).

Oh, oh yeah the numbers are there :blush: LOL talk about head in the clouds today!

M F M

Okay Jaki, I've got a LibreOffice-4.0 Calc script here which seems to do what you want (automatic embedding of thumbnails given the ProductId). How familiar are you with macros in LibreOffice? (do you need step-by-step instructions on how to tailor and install and run it?).

Currently it works only on the first sheet of your workbook, and assumes that column A are ProductIDs and column B are where thumbnails should go (this can be easily changed).

(( Be warned though, it seems to slow down as it runs - the first thousand thumbnails are processed in the order of 30secs or so, but then after than it just starts getting slower and slower. On the positive side, unlike VBA I haven't managed to crash Calc yet... but I haven't let it complete an entire run though. Will try timing it now. ))

[removed]

jakiblue

M F M said:

Okay Jaki, I've got a LibreOffice-4.0 Calc script here which seems to do what you want (automatic embedding of thumbnails given the ProductId). How familiar are you with macros in LibreOffice? (can I just paste the code here and you'll know what to do with it, or do you need step-by-step instructions on how to tailor and install and run it?).

(( Be warned though, it seems to slow down as it runs - the first thousand thumbnails are processed in the order of 30secs or so, but then after than it just starts getting slower and slower. On the positive side, unlike Excel, I haven't managed to crash Calc yet... but I haven't let it complete an entire run though. Will try timing it now. ))

I've never used macros before - I think I may be a complete dumbkopf with them. :D If you could do a dummies guide, it would be HUGELY appreciated.

Does it take the thumbnails from the store, or my DIM, or from a folder on my computer? And if it slows down can you do part of it, then do another part later? Or does it have to be run all at once?

M F M

Okay this might take a bit longer to clean up... whats posted above is very much a "concept demo" (showing that it IS possible to do)... not really even beta quality yet ;-).

It takes the thumbnails from your machine, from where DIM downloads them to (your C:\Users\Public\blahblahblah\... as was posted in a thread referenced up above)... and currently the script has to process the whole lot at once (but that should be able to be adjusted).

(( I've just checked - it does indeed take ~30secs for 1000 thumbnails... and ... oh that's interesting... it stopped spacing out the thumbnails around row 165... .... more debugging required it seems <(^_^)............. nope, it worked - just have to force a repaginate by adjusting the thumbnail column width slightly ^_^)).</p>

EDIT: hmmm... even with only 4000 thumbnails, it's enough to crash Calc just trying to open the saved spreadsheet... I'm starting to think this might not be the best way (unfortunately) to visualise your thumbnails...

EDITx2: hmmm... upgrading to the current LibreOffice (4.2.5.2) seems to have changed the crash behaviour... not for the better unfortunately >_<... now it dies with a 'osl::Threat::create failed" message... I don't really think this is a feasible solution Jaki (we might have to wait for Taozen to knock out the thumbnail browse feature in his utility) - how many products do you have, more than 3000? (because Calc definitely dies at the 3000 mark trying to reload the spreadsheet... trying again with 2000... ).</p>

EDITx3: correction - now up to 4600 thumbnails without crashing Calc yet (and still able to reopen the spreadsheet :D). The "trick" is to (a) resize the thumbnail column after every batch of installs to fix the visibility, and (b) to Edit -> Links -> BreakLinks on all the images (so a copy gets stored in the spreadsheet, not just a reference to your thumbnail directory)... for 4600 thumbnails, the spreadsheet is 49MB in size, but still alive :D... doesn't speed up the insertion of thumbnails though...

jakiblue

you're an absolute saint MFM to be doing this!! A saint with the patience of.....of.....um, a really patient person. LOL. (couldn't think of a proper analogy)

Miss B

OHHHHHHHHHHHHH write a marco!!!!! ~sheesh~

I used to write macros in Lotus123 years ago on my DOS computers. Haven't written a macro in Excel in recent years, but now that you reminded me, I should try that as well. Is there some way to adjust the Excel script you wrote to accept a certain amount of products at a time. That way you could do, for example, 100 at a time. Would take a while to get them all done, but it certainly would be less likely to crash.

I wouldn't mind seeing your Excel script.

Yes, LibreOffice is the "new" name for OpenOffice. I had OpenOffice on my last laptop, and Excel on my desktop.

Miss B

M F M said:

EDITx3: correction - now up to 4600 thumbnails without crashing Calc yet (and still able to reopen the spreadsheet :D). The "trick" is to (a) resize the thumbnail column after every batch of installs to fix the visibility, and (b) to Edit -> Links -> BreakLinks on all the images (so a copy gets stored in the spreadsheet, not just a reference to your thumbnail directory)... for 4600 thumbnails, the spreadsheet is 49MB in size, but still alive :D... doesn't speed up the insertion of thumbnails though...

Can't you set the height for the column where the thumbnails will display, and the width for all the rows (except the column headers at the top) before you even start?

For instance, I had my trial Excel spreadsheet set to 4 columns, Product Name, Product ID, Package ID and Thumbnails, and I had the 4th column (D) width at 16.5, and the height of all rows at 135. The thumbnails fit perfectly. I don't know how those measurements would translate to LibreOffice measurements, but they can't be that far off.

M F M

Here's the next version Jaki - major features are:

. this version processes (inserts) only 2000 thumbnails at a time (you'll find running it a second time will start at around the same speed as it was inserting - and you'll also find that you can't Save a file that has 6600 thumbnails - I'm not sure what the maximum number is);
. it can be restarted multiple times (and will skip the "existing" thumbnail rows);
. it will automatically change the height of the row it has processed to match the thumbnail height - you don't need to do that manually;
. you'll crash LibreCalc eventually with it (although with multi-gig render machines we have nowadays, its really rather surprising the application would crash - maybe running out of handles for all the graphic images or something...);
. it looks on the FIRST sheet of the workbook, but will accept any column ordering;
. the FIRST row of the FIRST sheet of the workbook is checked for titles "ProductID" and "Thumbnail", and these columns when found are used for processing (you can modify this if your columns are labelled differently - see ProductTitle$ and ThumbTitle$ just below);
. the thumbnails are embedded with size 3.07cm x 3.93cm (which is what the image seems to show as "Original Size" when I inserted some manually). If you want to make them bigger/smaller, adjust the thumbSizeWidth and thumbSizeHeight below;
. by default, the script attempts to pull thumbnail JPGs from C:\Users\Public\Documents\DAZ 3D\InstallManager\Thumbnails\ (which is where my DIM downloads them to). If you have your thumbnails elsewhere, then you'll need to modify thumbDir below;

Further instructions in next post.

REM  *****  BASIC  *****
Option Explicit

REM the following constant determines which sheet to look for data on.  The
REM first sheet is index 0, the second sheet is index 1, etc.
Private Const dataSheetIndex% = 0

REM the following two constants are the headings to search for in the first
REM row of the dataSheetIndex-th sheet in the current workbook.
Private Const ProductTitle$ = "ProductID"
Private Const ThumbTitle$ = "Thumbnail"

REM the following two constants are the directory to find the associated
REM thumbnails in (matching the contents of the ProductID field above),
REM and the suffix.
Private Const thumbDir$ = "C:\Users\Public\Documents\DAZ 3D\InstallManager\Thumbnails\"
Private Const thumbExt$ = ".jpg"

REM the following two constants determine the size to make the thumbnail
REM in the sheet.  These are expressed in cm.
Private Const thumbSizeWidth = 3.07
Private Const thumbSizeHeight = 3.93

REM ----------------------------------------------------------------------
REM shouldnt need to modify anything below here...

Sub Main
 Dim curDoc as Object
 Dim curSheet as Object
 Dim curRow as Object
 Dim thumbCell as Object
 Dim oGraphic as Object
 
 Dim r&, rCnt&, c&, fileName$, out$
 Dim columnID&, columnThumb&
 
 REM used for debugging output
 Dim CrLf$
 CrLf = Chr$(13) & Chr$(10)
 
 REM sizes are expressed in 100ths of a cm
 Dim sSize as New com.sun.star.awt.Size
 sSize.Width = thumbSizeWidth * 1000
 sSize.Height = thumbSizeHeight * 1000
 
 Dim sPoint as New com.sun.star.awt.Point
 
 REM retrieve the document and specified sheet, and
 REM determine the columns containing the ID and thumbnails.
 curDoc = ThisComponent
 curSheet = curDoc.Sheets( dataSheetIndex )
 curRow = curSheet.GetCellByPosition( 0, 0 )
 columnID = -1
 columnThumb = -1
 do while ((curRow.String <> "") and ((columnID = -1) or (columnThumb = -1)))
  c = curRow.CellAddress.Column
  if (curRow.String = ProductTitle) then
   columnID = c
  end if
  
  if (curRow.String = ThumbTitle) then
   columnThumb = c
  end if
  
  curRow = curSheet.getCellByPosition( c+1, 0 )
 loop
 
 REM check that we found both columns
 if (columnID = -1) or (columnThumb = -1) then
  MsgBox( "Could not locate column titles '" + ProductTitle + "' and '" + _
   ThumbTitle + "' on first row of sheet '" + curSheet.Name + "'." )
  exit sub
 end if
 
 REM now start the processing...
 curRow = curSheet.getCellByPosition( columnID, 1 )
 rCnt = 0
 
 do while ((curRow.String <> "") and (rCnt < 2000))
  r = curRow.CellAddress.Row
  
  REM retrieve the thumbnail cell
  thumbCell = curSheet.getCellByPosition( columnThumb, r )
  
  REM if theres nothing in there, then its a cell to process
  if (thumbCell.String = "") then
   REM modify the height of each row in preparation for the thumbnail
   curSheet.Rows(r).Height = sSize.Height
  
   REM compute the thumbnail filename and check for existence
   fileName = thumbDir + curRow.String + thumbExt
  
   if FileExists(fileName) Then
    REM out = out + "Row " + str( r ) + ": " + curRow.String + ".jpg file exists!" + CrLf
    thumbCell.String = "Found"
   
    sPoint = thumbCell.Position
    REM it appears that the visual offset of the image is removed
    REM by adjusting the size of the column slightly.
    REM sPoint.y = sPoint.y + (thumbCell.CellAddress.Row * 70)
   
    REM with thanks to a post from "F3K Total"...
    oGraphic = curDoc.createInstance("com.sun.star.drawing.GraphicObjectShape")
    oGraphic.GraphicURL = ConvertToUrl(fileName)
    oGraphic.Name = curRow.String & ".jpg"
    curSheet.DrawPage.add( oGraphic )
    oGraphic.Anchor = thumbCell
    oGraphic.setSize(sSize)
   else
    REM out = out + "Row " + str( r ) + ": " + curRow.String + ".jpg file NOT FOUND!" + CrLf
    thumbCell.String = "Not Found"
   end if
   
   rCnt = rCnt + 1
  end if
  
  curDoc.CurrentController.StatusIndicator.Start( "Row " + str(r) + "...", 0 )
  curRow = curSheet.getCellByPosition( columnID, r + 1 )
 loop
 
 curDoc.CurrentController.StatusIndicator.Reset
End Sub

M F M

Ok - here's what I know (/have learnt X) about LibreOffice Calc (v4.2.5.2).

In DIM,
(d1) download all the thumbnails for all your products;
(d2) "extract" the CSV for your products;

In LibreOffice Calc,
(1) import your products CSV into a LibreOffice Calc spreadsheet (I'm assuming you know how to do this);

(2) open the (.ODS) spreadsheet that contains your products listing;
(2.a) confirm that your first sheet (sheet names are listed in the bottom-left corner of the Calc window) contains your products;
(2.b) also confirm that the first sheet contains headings on the first row, and that one of those headings is "ProductID", containing the product number that corresponds to the thumbnail number in your downloaded thumbnails directory;
(2.c) insert a column (or at the end of the used columns), and in the first row put heading "Thumbnail" on it;

(3) open the Tools -> Macros -> Organise Macros -> LibreOffice Basic... dialog;

(4) in the "Macro from" section, select your ODS file, and the listing on the right "Existing macros in: Module1" should be empty. Press New to show the "New Module" dialog;

(5) enter name 'modJaki' (or whatever - doesn't matter), and press OK. The "LibreOffice Basic" window should then open, showing an "Object Catalog" on the left side, and a default script on the right, that reads "REM ***** BASIC *****" followed by "Sub Main" followed by "End Sub". This is the LibreOffice macro editor (very similar to VBA);

(6) highlight the text on the right (as shown in screenshot below), and press Delete to clear it out. The window on the right should be empty;

(7) Copy and paste the code from my previous post above (everything from "REM **** BASIC *****" down to "End Sub"), into the window. If you pasted it correctly, you should see parts of it change colour (that's the LibreOffice editor auto-syntax highlighting happening);

(8) [optional] to check that you pasted it okay, press the button on the far left of the row that contains the "Insert button" (highlighted below) - it looks like a filing cabinet with an arrow on top, just to the right of the "[Untitled 1].Standard" dropdown. If you hover over it, the tooltip reads "Compile". This will check to see that the code looks okay;

(9) close the LibreOffice Basic window and return to your main spreadsheet window;

(10) having made all the checks in step (2) above, open the Tools -> Macros -> Organise Macros -> LibreOffice Basic... dialog again, and this time you should see that your file (at the bottom of the list on the "Macro from" section on the left, now has an 'Existing macros in: modJaki', on the right, called "Main". This is the thing we just pasted in. Select it, and click the "Run" button in the top-right of the window, and wait;

(11) you SHOULD see your spreadsheet start to flicker and jump, as the first few visible rows are stretched in height, and the thumbnails start to appear (assuming you have the column visible). There will also be a small counter on the statusbar at the bottom of the window, showing you which row it has processed. This will count up to 2000 and stop (or the number of products you have, whichever is smaller). The number starts out incrementing quickly, but will slow down after awhile as more and more images are inserted;

(12) when it finishes, adjust the width of your thumbnail column outward by a small margin (say 0.3-0.5cm) - I've found this helps the LibreOffice Calc display (and will often bring invisible images out into the open). You can make the column larger or smaller - just the change is what's important;

M F M

Limitations and notes:

. I don't seem to be able to get Calc to Save a sheet with 6600 thumbnails, although as I said previous page, it seemed to be okay at 4500 thumbnails. That's a LibreOffice limitation - the only thing we can do is split our Product Lists into smaller sized chunks and use separate files (sheets maybe? I haven't tested this...);

. if you scroll down your list and see the thumbnail column empty (but contains the text "Found"), then that means the refresh didn't work. Try resizing the column and your thumbnails should pop into view;

. if the text in the thumbnail column reads "Not found", that means you haven't downloaded the thumbnail for that product (or the macro couldn't find it in your C:\Users\Public\Documents\DAZ 3D\InstallManager\Thumbnails\ directory. Check manually that the file with that product Id DOES actually exist in there;

. one trick I've noticed, which may help if you want to take your spreadsheet away from the machine with the thumbnail directory, is to "embed" the images directly into the spreadsheet. To do this with a spreadsheet full of thumbnails, go to Edit -> Links..., all the entries should be listed with the filename and Status "Manual" - select all of those entries and click "Break Link". This will remove the link to your hard-drive version of the thumbnail, and store it within the spreadsheet file itself. Say "OK" to the 'Are you sure' dialog, then Close the Links window, and Save the spreadsheet again. This version of the file should be portable to another machine without "losing" the images;

. good luck! (^_^)n

M F M

Hello Miss B (^_^)n

Miss B said:

OHHHHHHHHHHHHH write a marco!!!!! ~sheesh~

I used to write macros in Lotus123 years ago on my DOS computers. Haven't written a macro in Excel in recent years, but now that you reminded me, I should try that as well. Is there some way to adjust the Excel script you wrote to accept a certain amount of products at a time.

In Excel, the problem was similar to the one in LibreOffice Calc - it really doesn't handle huuuuge numbers of embedded images well (like, crashes, can't save, etc X). They obviously didn't expect anyone would _ever_ embed more than a 1000 (say) images in a file... X).

Miss B said:

That way you could do, for example, 100 at a time. Would take a while to get them all done, but it certainly would be less likely to crash.
The crashing didn't occur on the insertion (usually) - for me it generally happened after I had inserted, and was attempting to save (or even just to scroll down to see whether they were all there X).

Miss B said:

I wouldn't mind seeing your Excel script.
It wasn't all that smart - very similar to the LibreOffice one above actually - process each row, find the ID, locate file, insert image (and here the VBA version was FAR less esoteric than the LibreOffice one, because of the auto-completion ^_^), move to next row.

EDITx3: correction - now up to 4600 thumbnails without crashing Calc yet (and still able to reopen the spreadsheet :D). The "trick" is to (a) resize the thumbnail column after every batch of installs to fix the visibility, and (b) to Edit -> Links -> BreakLinks on all the images (so a copy gets stored in the spreadsheet, not just a reference to your thumbnail directory)... for 4600 thumbnails, the spreadsheet is 49MB in size, but still alive :D... doesn't speed up the insertion of thumbnails though...

Can't you set the height for the column where the thumbnails will display, and the width for all the rows (except the column headers at the top) before you even start?

Yep, certainly ^_^.

For instance, I had my trial Excel spreadsheet set to 4 columns, Product Name, Product ID, Package ID and Thumbnails, and I had the 4th column (D) width at 16.5, and the height of all rows at 135. The thumbnails fit perfectly. I don't know how those measurements would translate to LibreOffice measurements, but they can't be that far off.
LibreOffice macros use a scale of 100ths of a mm, so a 30mm wide cell is represented in macros as 3000 - from memory Excel uses something like points, or twips, or differing scales for height vs width? *can't remember* <(^_^).</q>

Miss B

M F M said:

Hello Miss B (^_^)n

Miss B said:

OHHHHHHHHHHHHH write a marco!!!!! ~sheesh~

I used to write macros in Lotus123 years ago on my DOS computers. Haven't written a macro in Excel in recent years, but now that you reminded me, I should try that as well. Is there some way to adjust the Excel script you wrote to accept a certain amount of products at a time.

In Excel, the problem was similar to the one in LibreOffice Calc - it really doesn't handle huuuuge numbers of embedded images well (like, crashes, can't save, etc X). They obviously didn't expect anyone would _ever_ embed more than a 1000 (say) images in a file... X).

Miss B said:

That way you could do, for example, 100 at a time. Would take a while to get them all done, but it certainly would be less likely to crash.
The crashing didn't occur on the insertion (usually) - for me it generally happened after I had inserted, and was attempting to save (or even just to scroll down to see whether they were all there X).

Miss B said:

I wouldn't mind seeing your Excel script.
It wasn't all that smart - very similar to the LibreOffice one above actually - process each row, find the ID, locate file, insert image (and here the VBA version was FAR less esoteric than the LibreOffice one, because of the auto-completion ^_^), move to next row.

EDITx3: correction - now up to 4600 thumbnails without crashing Calc yet (and still able to reopen the spreadsheet :D). The "trick" is to (a) resize the thumbnail column after every batch of installs to fix the visibility, and (b) to Edit -> Links -> BreakLinks on all the images (so a copy gets stored in the spreadsheet, not just a reference to your thumbnail directory)... for 4600 thumbnails, the spreadsheet is 49MB in size, but still alive :D... doesn't speed up the insertion of thumbnails though...

Can't you set the height for the column where the thumbnails will display, and the width for all the rows (except the column headers at the top) before you even start?

Yep, certainly ^_^.

For instance, I had my trial Excel spreadsheet set to 4 columns, Product Name, Product ID, Package ID and Thumbnails, and I had the 4th column (D) width at 16.5, and the height of all rows at 135. The thumbnails fit perfectly. I don't know how those measurements would translate to LibreOffice measurements, but they can't be that far off.
LibreOffice macros use a scale of 100ths of a mm, so a 30mm wide cell is represented in macros as 3000 - from memory Excel uses something like points, or twips, or differing scales for height vs width? *can't remember* <(^_^).</div>
Thanks for your observations. I'll let you know if I come up with something usable in Excel. :coolsmile:

namffuak

Thanks for putting this together.

I'm running into a bit of a problem - everything is coming up as 'Not Found'; is there an easy way to dump the fully qualified file name string the macro is trying to use? I can manually follow the process and find the thumbnails, so I'm trying to figure out where the process is going 'tilt' and I'm not real strong on basic in general, much less on Libre Office Calc basic. Ideally I'd like to replace the 'Not Found' whit the (apparently) erroneous path.

Edit - Nevermind - figured it out. :-)

Never fails - ask for help and find the issue immediately thereafter! :-)

Aaaand - I'm still stumped. I get the pathname that is 'Not Found'; if I copy it and paste it into explorer - I get the image loaded into my 2D graphics program. FWIW, I'm on Win7 Ultimate, 64-bit. The base path is "C:\Users\Public\Public Documents\DAZ 3D\InstallManager\Thumbnails\" and the first file is 90.jpg; "C:\Users\Public\Public Documents\InstallManager\Thumbnails\90.jpg" is the path shown in Calc; explorer finds it, but Calc does not. Suggestions?

M F M

C:\Users\Public\Public Documents\... or C:\Users\Public\Documents\... ?

Aaaand that is very odd o.0. Can you use Calc to manually insert a thumbnail?

namffuak

M F M said:

C:\Users\Public\Public Documents\... or C:\Users\Public\Documents\... ?

Aaaand that is very odd o.0. Can you use Calc to manually insert a thumbnail?

Public\Public Documents - And yes, this confuses me a bit, even though Windows is not my strength.

I can copy an image from PSP and paste it in to Calc and have it show up. If I comment out the test for file exists I end up with a small graphic of three boxes (the red/yellow/blue 'here there be a graphic image') and a dotted outline with the full path of the image in it - starting with file:\\\C:\ with the spaces in the path replaced with ' ' - and the whole cell is an image.

Copy attached -

M F M

Does the direct Calc mechanism for inserting a graphic from a file work though? (Insert -> Image -> From File...)

Also, have you modified the line at the start that reads "Private Const thumbDir$ = ...", to contain that extra '...\Public Documents\...' path element?

Oh hang on, I see... this is part of the Win7 magic involving "Junction Points" (read: symbolic/hard links)...

namffuak

M F M said:

Does the direct Calc mechanism for inserting a graphic from a file work though? (Insert -> Image -> From File...)

Also, have you modified the line at the start that reads "Private Const thumbDir$ = ...", to contain that extra '...\Public Documents\...' path element?

Direct insert works correctly. And yes, I did modify the pathname - my basic isn't quite that rusty. :-)

FWIW, here's the path, copied out of the explorer window :C:\Users\Public\Documents\DAZ 3D\InstallManager\Thumbnails

and this is 90.jpg:

Aaannnd - I wanna go to bed!

The path I copied from explorer above is NOT the path that shows when I browse directory by directory to the thumbnails - and when I re-modify the thumbDIR$ back to the original, I get images in the spreadsheet. I think I'm going to go re-arrange part of the wall with my head for a while . . .

Apologies for the false alarm . . .
(Obviously I started by setting thumbDIR$ to the path displayed in explorer . . .)

M F M

Hehe no problems - sleep well (^_^)n.

(and you had me worried for a few minutes there, wondering however did it manage to work on my own system... maybe your Calc was running with a non-admin user account... maybe my directory tree was subtly different... glad to hear it's all sorted out now v(^_^)v).

HyJinx

M F M said:

. the FIRST row of the FIRST sheet of the workbook is checked for titles "ProductID" and "Thumbnail", and these columns when found are used for processing (you can modify this if your columns are labelled differently - see ProductTitle$ and ThumbTitle$ just below);

Hi MFM!

Thanks so much for putting this together! It's helped tremendously already(I didn't even know about the LibreOffice Software before discovering this post!). I understand that this is an old post, but I'm stuck and thought you might be able to help if you happen to see this.

I'm using your instructions to import thumbnails into my brand-new spreadsheet, and I believe I've followed your very clear instructions exactly, going back through several times before posting this to make sure I'm not missing something glaringly obvious as I tend to do :-)

Anyway, I have columns labeled just as you suggest and I've attached a picture below, yet when I attempt to to run the Macro I get an error message saying that the action can't be completed because I don't have the necessary column headings. I've also included an image of this, plus an image showing that I'm working with the 1st sheet of the spreadsheet (I'm sure they're not all necessary for you to see where I'm going wrong, but I figure another pair of eyes checking behind me can never hurt). Does there happen to be a certain procedure for designating those fields as "headings"? I was operating under the assumption that the words just needed to be located in the first row, I didn't add them as a heading specifically and couldn't find a way to do so, but I could have easily overlooked it. I also tried running the macro both with and without the formatting of the words in the first row.

Any help you or anyone else may be able to provide will be GREATLY appreciated!

Again, thanks so much for everything!

HyJinx

Figured it out!

I'm not sure if it was the "right" way of going about it, but it worked for me. Here's what I did, in case anyone else runs into the same problem:

Select Column B, or whichever is "Thumbnail" in your document.
Define the name for this column here:
Insert > Names > Define
In the popup window enter the name "Thumbnail" in the appropriate field, then confirm the cell range is correct in the "Range" field and chang the "Scope" of the column name to just effect the first page of the spreadsheet.
Repeat this process for the column titled "ProductID"

Then I tried running the Macro again and it worked! I'm sure there's a better way, but this seems to work in a pinch. If I'm doing anything that could cause problems with my spreadsheet in the future please let me know!

Thanks!

HyJinx

Now I have another question... Sorry!

There were no thumbnails available for some items, and the Macro performed just as it's supposed to and displayed "Not Found" in the cell.

However, I was able to obtain images for the majority of the ones missing, I sized them the same as the others and saved them in the same file with the appropriate file name. Then I ran the Macro again, only this time nothing happened. Should I do something else before running it again or reset it in some way to get it to pull the additional files?

Thanks for any help!

M F M

bduncan86 said:

Now I have another question... Sorry!

There were no thumbnails available for some items, and the Macro performed just as it's supposed to and displayed "Not Found" in the cell.

However, I was able to obtain images for the majority of the ones missing, I sized them the same as the others and saved them in the same file with the appropriate file name. Then I ran the Macro again, only this time nothing happened. Should I do something else before running it again or reset it in some way to get it to pull the additional files?

Thanks for any help!

Hi bduncan86! (^_^)n...

... sorry I didn't answer earlier - I didn't notice this thread had been reanimated <(^_^). Yes, you need to clear the "Not Found" cells before rerunning the script (otherwise it will cheerfully skip over that cell and continue looking for ones that are empty). Just select the range of "Not Found" cells (JUST the cells in the Thumbnail column), and press Delete to zap them back to a blank state, then run the script again, and it should process them.</p>

PS I'm a little surprised you needed to do that trick with the Insert -> Names -> Define... it _should_ have worked without it (certainly I did not do that here)... still, if it works, it works v(^_^)v. Glad to hear it's looking useful for you (I think Jaki has given up on this ;-) ).