Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Oct 2011
    Location
    Central Virginia
    Posts
    38
    Thanks
    8
    Thanked 2 Times in 1 Post

    Importing file names and listing

    I'm not sure what application is best to use but I have a serious need to revamp my website and the first order of business is to list all the HTML files associated with it. I'll be using Dreamweaver to do the actually revamp but getting the files listed and arranged in some order is what this is about. This is my astronomy imaging website and the HTML files are numbering at present 1,202. Now some of these are automatically generated files such as those related to my weather station uploads every 30 minutes and aren't of any concern. What I'm looking at is trimming the older image pages where later, better images have been taken. Each new image these days will have a minimum of 6 html files generated. The main page showing a small version of the object and the data about the object with links to 3 additional sized images, each with their own html page. Then there is the category page such as type of galaxy, nebula, supernova remnant, and so on. Lastly it also gets added to the most recent image page where the last 10 images are posted. Posting the image without the html page leads to the image being posted on a white background which really doesn't work well for astronomy images. With the html page I can control the background color so you don't have such a bright background to what is normally a darker image. A bit easier on the eyes. So anyway, easy to see where the files start to accumulate quickly.

    I'm not experienced with anything much in MS Office which I subscribe to Office 2013 other than Outlook and Word, and Word only really not very advanced as I use it at home only. Is Excel capable of list and arranging these file names or do I need to use another application such as maybe Access? Any suggestions very welcomed.

    Steve

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Steve:
    Welcome to the lounge. The good news is Excel should be able to assit you. If the files remain under say 10,000 Excel has most of the tools you will need. With Excel Open select "Data" to Open the Data Ribbon. There you will find several Groups and Buttons to manlipulate information.

    If all the files are in one Folder what I would suggest is to Open Windows Exlpoyer go to the folder and select all the files Ctrl + A. Next while holding down the shift key right click the mouse and on the pop up select "copy as path" The go to Excel and Paste it to a workbseet. This will give you the files names. Next you can use any blank column or columns to expand the identificaiton of a file such as in one column the type of file such as "Auto-Generated", "Old html", "New html", etc. By addiing such items you can "sort" or "Filer" data. You control what ID's you want for each file.

    Attached I have included a sample of what Excel can provide. The Workbook has the Filter Function of Excel Activated.

    Lastly, if you should need further assistance or have additional questions just post it here.

    Good Luck
    TD
    Attached Files Attached Files

  3. The Following User Says Thank You to duthiet For This Useful Post:

    sreilly24590 (2015-06-04)

  4. #3
    Lounger
    Join Date
    Oct 2011
    Location
    Central Virginia
    Posts
    38
    Thanks
    8
    Thanked 2 Times in 1 Post
    Thanks TD,

    The advantage of two monitors is having one display the needed information while the other has the program you're attempting to use. Based on this great info I have two worksheets started which will in turn probably end up creating a 3rd. I have one sheet with the html file names and a 2nd sheet with all the images listed. Sorted A-Z this will help me assign the images as need which I guess will be the final 3rd sheet. Thanks for the quick start to something I have been putting off for a long time, as the site just gets bigger and bigger!

    One thought, can the files be linked to the files themselves? Let's say I am deciding on which image to use, can a link the image to open when clicked on with it's associated program?

    Steve

  5. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Steve:

    Yes you can link. For closed files you will need the full path name. Since you already have the full path names all you need is to use Excel's HYPERLINK Function.

    Example:

    Cell A1 has C:\documents\Planets.html
    In Cell A2 put the following formula =HYPERLINK(A1)
    Copy the formula down as many rows as needed.
    Cell A2 will turn blue and be underlined
    if you click on the cell it should open the file in it's associated program.
    TD

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    If you don't want to add another cell for the link this code will convert the values in Col A to hyperlinks.
    Code:
    Option Explicit
    
    Sub ConvertToHyperLink()
    
       Dim lRow As Long
       Dim zHLStr As String
       
       [a1].Select
       lRow = 0
       
       Do
         zHLStr = [a1].Offset(lRow, 0).Value
         [a1].Offset(lRow, 0).Formula = _
           "=HyperLink(" & Chr(34) & zHLStr & Chr(34) & "," & _
                           Chr(34) & zHLStr & Chr(34) & ")"
         lRow = lRow + 1
       
       Loop Until [a1].Offset(lRow, 0).Value = ""
       
       Columns("A:A").EntireColumn.AutoFit
       
    End Sub    'ConvertToHyperLink()
    Just paste code into a standard module and save as .xlsm file to a trusted location.

    HTH
    Last edited by RetiredGeek; 2015-06-05 at 11:07.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Here's a more inclusive piece of code that will prompt you for a directory spec, read the directory, and create the links all in one operation, e.g. no cut & paste!

    Code:
    Option Explicit
    
    Sub ReadFileSpecsToHyperLinks()
    
       Dim zDirName  As String
       Dim zFileName As String
       Dim lRowCntr  As Long
       
       
       zDirName = InputBox("Please enter the desired directory Path to return.", _
                           "Full File Path...d:\path[\path\]...")
                           
       zDirName = zDirName & "\"
       zFileName = Dir(zDirName & "*.*") 'For specfic filetypes change *.* pattern here!
       lRowCntr = 0
       
       Do While zFileName <> ""
          [a1].Offset(lRowCntr, 0).Formula = _
            "=HyperLink(" & Chr(34) & zDirName & zFileName & Chr(34) & "," & _
                            Chr(34) & zDirName & zFileName & Chr(34) & ")"
    
         lRowCntr = lRowCntr + 1
         zFileName = Dir()
       Loop
       
       Columns("A:A").EntireColumn.AutoFit
       
    End Sub   'ReadFileSpecsToHyperLinks
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Steve,

    This may be an alternative solution that will provide a quick way to view your files without linking to the internet or to the files on your hard drive.

    Using Duthiet's method of copy and pasting the file paths into excel, you will end up with something like this:

    Steve.png

    clicking on any of the files will open a resizable form that will display the picture instantaneously without have to open Internet Explorer, creating hyperlinks, or opening any external program to view it. Simply paste the paths starting at A2 then click away!

    Steve1.png

    In the sheet module:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        If Not Intersect(Target, Range("A2:A" & LastRow)) Is Nothing Then
            PictureFrame.Hide
            PictureFrame.Show
        End If
    End Sub
    In the form module:
    Code:
    Private Sub UserForm_Activate()
        MakeFormResizable
        Row = ActiveCell.Row
        With Worksheets("Sheet1")
        Me.Picture = LoadPicture(.Cells(Row, 1))
        End With
    End Sub
    In a standard module:
    Code:
    'Written: February 14, 2011
    'Author:  Leith Ross
    '
    'NOTE:  This code should be executed within the UserForm_Activate() event.
    
    Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long
    
    Private Declare Function GetWindowLong _
      Lib "User32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long) _
      As Long
                   
    Private Declare Function SetWindowLong _
      Lib "User32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long, _
         ByVal dwNewLong As Long) _
      As Long
    
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16
    
    Public Sub MakeFormResizable()
    
      Dim lStyle As Long
      Dim hWnd As Long
      Dim RetVal
      
        hWnd = GetForegroundWindow
      
        'Get the basic window style
         lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
    
        'Set the basic window styles
         RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)
    
    End Sub
    Remember to replace my paths with yours
    Attached Files Attached Files

  9. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    X_LD (2015-06-14),zeddy (2015-06-15)

  10. #8
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Maudibe,

    This can be a very valuable tool for me. If I move the image window to the center of the screen, it will stay there if I click additional files. If I close the viewer, it will reopen in the top left. Is there a way to control where the viewer opens? One last question, can this be modified for .pdf files?

    TIA,
    Brian

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Brian,

    In the VB editor (Alt-F11), click on the form in the project window. In the properties window below, change the startup position.

    HTH,
    Maud

    FormProperties.png

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    Thanks for that nifty method for displaying images!

    That StartupPosition won't apply if you are using a second monitor for your Excel.

    The UserForm StartUpPosition (see Maud's post above ), is set to 1 (CenterOwner) by default, which means that it should appear in the middle of Excel's window. This works fine, unless you have a dual-monitor system. In that case, the UserForm is not centered in the Excel window.

    To force an Excel UserForm to be centered in Excel's window (on whatever monitor), add this code snippet when you activate the Form, for example:

    Code:
    Private Sub UserForm_Activate()
    
    With Me
      .StartUpPosition = 0
      .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
      .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
      .Show
    End With
    
        MakeFormResizable
        Row = ActiveCell.Row
        With Worksheets("Sheet1")
        Me.Picture = LoadPicture(.Cells(Row, 1))
        End With
        
    End Sub
    zeddy-eight-screens
    Last edited by zeddy; 2015-06-15 at 05:23.

  13. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Maudibe (2015-06-17),X_LD (2015-06-22)

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy-EIGHT-Screens...boy am I JEALOUS!

    Thanks for the code it will work great on my measly 2 screens.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    You could mention to your wife that you need another 8 screens to be ready for Windows 10 next month, but you might be pushing your luck.

    zeddy

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy.

    I like your NEW Math!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #14
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    RG, I was surfing the net (as you do) and found this. Hope you like it

    https://www.arbico.co.uk/Arbico-Sile...ing-PC-p-.html

  18. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Access,

    A might rich for my blood!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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