Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Populating a spreadsheet (XL97/WinNT4)

    I have been presented with a spreadsheet that consists of several columns, the first of which contains document names, hyperlinked to the documents themselves. Some of these are XL docs, mostly they're Word. I've been asked to follow each link, check the document it goes to and assign one of three categories to it in another column and add the 'last modified' date to yet a third column.

    Now this is a rather tedious task and it seems to me it ought to be a relatively easy one to automate, at least with regard to the 'last modified' date (there's no definitive way to say which category it would be, but I could live with that), but I've no experience in working between applications with VBA and I'm not really sure how to start!

    Does anyone have any ideas or snippets of code that might help me here? I'd be most grateful, and I'm attaching a snippet of the spreadsheet for reference.

    Edited to add:
    Well, I've made a start with this little macro in both Word and XL (not cross-application, though!):

    Sub LastModifiedToClipboard()
    Dim docdate As String
    docdate = ActiveWorkbook.BuiltinDocumentProperties(12)
    Workbooks.Add
    With Selection
    .typetext Text:=docdate
    .WholeStory
    .Cut
    End With
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    End Sub

    This is the XL version; the Word version (identical except it says document where this says workbook) works perfectly, but this one crashes with an 'unknown automation error' on the line that sets docdate to the last saved date. I've checked everything I can think of and everything *should* work, as far as I can see, but it just doesn't! Help!
    Beryl M


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    You get an error when you triy to READ a property that is NOT set (ie it has no value). Excel does NOT use this "built-in property" so it is not filled (unless you fill it yourself).

    Here is some info from Chip Pearson on doing this.
    John Walkenbach has a routine here which has alternate functions for getting the last saved date.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Thanks for the links, Steve, but I can't get the code from the first one to work (probably because I need the date either in the clipboard or pasted into a cell, and in the second case the dates I want are not the ones for the activeworkbook!) - and the code in the second one went straight over my head without stopping!

    I guess I'll have to keep trying!
    Beryl M


  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Beryl,

    Your "docdate" should be declared as "Date" not as "String".

    Also, If there is an active Workbook, your code will have to talk "Excel" not "Word".
    Excel won't recognize typetext and wholestory.

    Regards,

    Jim Cone
    San Francisco, CA

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Umm, I'd found that out!

    What would be the Excel equivalents, though?
    Beryl M


  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Hi Steve,

    Wouldn't it be better to move the createObject statement to the top and assign it to an object variable?

    Now you're recreating the scripting object inside the loop every time.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    <P ID="edit" class=small>(Edited by sdckapr on 19-Sep-03 10:57. Modified to include assigning the createobject to a variable (per Jan's suggestion))</P>Does this get you started?

    It will go thru all the links on the activesheet and create a table on a new sheet with the address, the name of the link the filename and the last modified date. It does NOT open any of the files, it just reads the info. Modify as needed

    Steve

    <pre>Option Explicit
    Sub ListHyperlinks()
    Dim hl As Hyperlink
    Dim wks As Worksheet
    Dim wksHL As Worksheet
    Dim lRow As Long
    Dim sFilename As String
    Dim fso As Scripting.FileSystemObject

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set wksHL = ActiveSheet
    Set wks = Worksheets.Add
    lRow = 1

    wks.Cells(lRow, 1) = "Address"
    wks.Cells(lRow, 2) = "Name"
    wks.Cells(lRow, 3) = "File Name"
    wks.Cells(lRow, 4) = "Last Modified"
    Range(Cells(lRow, 1), Cells(lRow, 4)).Font.Bold = True
    Cells(lRow + 1, 1).Select
    ActiveWindow.FreezePanes = True

    For Each hl In wksHL.Hyperlinks
    sFilename = hl.Name
    If InStr(sFilename, ":") = 0 Then _
    sFilename = Application.DefaultFilePath & _
    sFilename
    lRow = lRow + 1
    wks.Cells(lRow, 1) = hl.Range.Address
    wks.Cells(lRow, 2) = hl.Range.Value
    wks.Cells(lRow, 3) = sFilename
    wks.Cells(lRow, 4) = fso.GetFile(sFilename).datelastmodified
    Next
    Cells.EntireColumn.AutoFit
    End Sub</pre>


  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Yes you are correct (of course). I guess my lack of programming expertise is showing.

    I originally had it in a function call and then eliminated the call and just shoved it in.

    I mmodified the code in my original post.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Hi Steve, thanks for that, and it looks like it should get me started, but when I run it I get an error saying that it doesn't recognised the scripting.filesaveobject?
    Beryl M


  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Is it the line?:
    Dim fso As Scripting.FileSystemObject

    I have found just using:
    Dim fso

    works on some computers. For whatever reasons, it works as a variant, but not as a defined type. There probably is something no setup correctly, though I have never investigated it.

    Steve

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Steve,

    Setting a reference in the VBE to "Microsoft Scripting Runtime" (scrrun.dll) will do the trick.
    Declaring your scripting variables as "Object" will also work.

    Regards,
    Jim Cone
    San Francisco, CA

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating a spreadsheet (XL97/WinNT4)

    Thanks, I will check this when I get home. The code works at my business computer, but I have had "problems" with it at my home (though never serious enough problems to do anything about it, I always remarked out any code before the AS in the DIMs it didn't like and that generally "fixed" it.

    You can tell I used to work for manufacturing: "why actually figure out how to fix the real problem when you can get away with a 'jury-rigged' solution!"

    Thanks,
    Steve

Posting Permissions

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