Results 1 to 2 of 2

Thread: Worklogs using Excel

  1. #1
    Retrosmith Mach's Avatar
    Join Date
    Mar 2005
    Location
    Texas
    Posts
    910

    Default Worklogs using Excel

    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:

    Code:
    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:

    Code:
    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.

  2. #2

    Default Re: Worklogs using Excel

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •