Worklogs using Excel

[Mach]

If you use picasa's (Google) web album for your worklog photos and you have excel, you may find this of use. Not sure how it'lll work with another hosting service. If anyone knows of any easier way, I'd be interested in learning more.

Problem: Picasa only allows you to selet one photo at a time. Cutting and pasting between the boards and then editing the link is a pain.

Solution(?): Use excel to create the worklog

I opened a new spreadsheet. Then resized the Excel on one side of the screen. On the other side, I opened my browser set to my online album. I then copied the first link and pasted it into the first cell in the 3rd column in the spreadsheet. Toggle to the next photo in the browser and repeat the copy and paste into the next row down.

When I was done with the pasting, I did a Edit>Replace and did a wildcard replace (*<img>) on the front half of the string and then the second half to get rid of the extra charaters in the link setting both to replace with nothing.

Then I created a new macro in Excel that looks like this:

Sub InsertPicture()
Dim myfile As String

For Each cell In Selection
Set mypict = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cell.Offset(0, -2).Left, cell.Top, cell.Height, 100)
myfile = Trim(cell.Value)
mypict.Fill.UserPicture myfile
Next

End Sub

I then went back to the spreadsheet and selected all the links that I had just pasted in. Then I ran the above (Tools>Macro>Macros) macro above. It copied from Picasa a thumbnail for each link.

I then could move around each row to build my worklog in the order that I wanted. With the thumbnails in the first column and the links in the third, I typed my comments in the second.

I then created another macro to upload my edited worklog. It looks like this:

Sub Worklog()
Dim myLog As Variant
myLog = ""
Dim MyDataObj As New DataObject
    For Each cell In Selection
        myLog = myLog & Chr(10) & cell
        myLog = myLog & Chr(10) & "[img]" & cell.Offset(0, 1) & "[/img]"
    Next cell
    

MyDataObj.SetText myLog
MyDataObj.PutInClipboard
       
End Sub

Note: From VBA, you'll need to Insert> New UserForm. This adds a reference to the DataObject object to your project.

Selecting all the comments in the second column that I just typed in and running this macro puts the entire worklog on the clipboard to paste into TBCS.

When I want to add a new worklog entry, I clear the spreadsheet and start again. It much easier for me, hopefully for you too.

[perry]

Hi, I feel a little confused on your problem? Can you simplify it?